Studio/ODBC Simple Application

From J Wiki
Jump to: navigation, search

Lab: ODBC: A simple application Author: John D. Baker bakerjd@kos.net


Introduction

Excel is a great tool for entering and editing data. For small data analysis tasks I usually enter the data into Excel and then use J/ODBC to work with data. This lab demostrates a simple expense account distribution calculation.

First load the ODBC interface.

   load 'dd'

A good spreadsheet layout for ODBC

This lab uses the spreadsheet

system\examples\data\expense.xls

The next step checks to see if this spreadsheet is registered as an ODBC datasource. If the spreadsheet is not registered use the ODBC control panel applet to register it with a DSN (data set name) of "expenses"

   NB. check expense dsn - result should be 1
   (<'expenses') e.  {."1 ddsrc ''
0
   (<'expenses') e.  {."1 ddsrc ''
1

Before connecting the expense sheet with ODBC take a look at the sheet with Excel or another spreadsheet. The layout of expense.xls is the best arrangement for extracting data with ODBC.

A good layout has:

  • A header column consisting of

simple BLANKFREE names in the first row.

  • Homogeneous data columns.

After looking at the sheet close it and proceed to the next step. You must close the spreadsheet or ODBC will not open it.

Defining application verbs and nouns

Now let us define a few simple verbs and nouns. The first required item is the name of the expense table. The data is

stored in Excel "sheet1"

   NB. spreadsheet name - stands for ODBC table
   SHEET=: '`sheet1$`'

Usually ODBC data needs a little scrubbing once you get it into J. In this case there will be some trailing blank rows. This verb removes such blank rows.

   NB. trims blank rows using first date column
   trimblkdates=: ] #~ ' '"_ ~: [: {.&> [: {."1 ]

I am using the basic (ddfet) verb to fetch ODBC data. (ddfet) return date data as a formatted string. In this case we need to convert the date to integer to extract the month value.

   NB. year month data table from ODBC character timestamps
   ymdfrts=: [: ". ' '"_ (<(<a:),<4 7)} _ 10"_ {. [: > 0: {"1 ]

The next two verbs define the expense account distribution calculation.

   NB. total dollars by expense category
   expensedist=: ([: ~. 2: {"1 ]) ,: [: <"0 (2: {"1 ]) +//. [: ; 1: {"1 ]

   NB. monthly expense distribution
   monthexpensedist=: 3 : 0
m =. 1 {"1 ym =. 0 1 {"1 ymdfrts y
y =. expensedist&.> (b =. ~:m) <;.1 (/:m){y
(<"1 ym #~ ~: m) ,. (+/@:( ;@:(1&{)) &.> y) ,. (\:&.> {:&.> y) {"1&.> y

Finally let define an overall report verb

   NB. computes monthly expense distribution
   expensereport=: 4 : 0
sh =. ('select * from ',x) ddsel y
dat =. trimblkdates ddfet sh,_1
monthexpensedist dat

Running the application

Computing an expense distribution is now simply a matter of executing the next two statements.

   NB. connect to expense data
   ch =: ddcon 'dsn=expenses'

   NB. run report
   SHEET expensereport ch
┌───────┬──────┬─────────────────────────────────┐
│1998 1 │109.21│┌─────┬─────┬────────┐           │
│       │      ││hotel│meal │supplies│           │
│       │      │├─────┼─────┼────────┤           │
│       │      ││67.2 │31.44│10.57   │           │
│       │      │└─────┴─────┴────────┘           │
├───────┼──────┼─────────────────────────────────┤
│1998 2 │178.58│┌─────┬─────┬────┬────────┐      │
│       │      ││hotel│meal │taxi│supplies│      │
│       │      │├─────┼─────┼────┼────────┤      │
│       │      ││112  │59.29│5.2 │2.09    │      │
│       │      │└─────┴─────┴────┴────────┘      │
├───────┼──────┼─────────────────────────────────┤
│1998 3 │64.51 │┌────────┬─────┬────┐            │
│       │      ││supplies│meal │gas │            │
│       │      │├────────┼─────┼────┤            │
│       │      ││22.99   │21.42│20.1│            │
│       │      │└────────┴─────┴────┘            │
├───────┼──────┼─────────────────────────────────┤
│1998 4 │177.79│┌─────┬─────┬────────┬───────┐   │
│       │      ││meal │hotel│supplies│postage│   │
│       │      │├─────┼─────┼────────┼───────┤   │
│       │      ││77.84│67.2 │26.42   │6.33   │   │
│       │      │└─────┴─────┴────────┴───────┘   │
├───────┼──────┼─────────────────────────────────┤
│1998 5 │92.4  │┌────┬───┐                       │
│       │      ││meal│gas│                       │
│       │      │├────┼───┤                       │
│       │      ││72.4│20 │                       │
│       │      │└────┴───┘                       │
├───────┼──────┼─────────────────────────────────┤
│1998 7 │85.57 │┌────┬─────┬─────────┐           │
│       │      ││meal│gas  │telephone│           │
│       │      │├────┼─────┼─────────┤           │
│       │      ││59.4│18.99│7.18     │           │
│       │      │└────┴─────┴─────────┘           │
├───────┼──────┼─────────────────────────────────┤
│1998 8 │58.75 │┌─────┬───┬───────┐              │
│       │      ││meal │gas│parking│              │
│       │      │├─────┼───┼───────┤              │
│       │      ││48.75│8.7│1.3    │              │
│       │      │└─────┴───┴───────┘              │
├───────┼──────┼─────────────────────────────────┤
│1998 9 │69.03 │┌─────┬─────┬────┐               │
│       │      ││bus  │meal │taxi│               │
│       │      │├─────┼─────┼────┤               │
│       │      ││40.13│23.55│5.35│               │
│       │      │└─────┴─────┴────┘               │
├───────┼──────┼─────────────────────────────────┤
│1998 10│100.09│┌─────┬────────┬────────┬───────┐│
│       │      ││meal │internet│supplies│parking││
│       │      │├─────┼────────┼────────┼───────┤│
│       │      ││71.95│16      │10.34   │1.8    ││
│       │      │└─────┴────────┴────────┴───────┘│
├───────┼──────┼─────────────────────────────────┤
│1998 11│59.22 │┌─────┬────────┬───────┐         │
│       │      ││meal │internet│parking│         │
│       │      │├─────┼────────┼───────┤         │
│       │      ││40.27│16      │2.95   │         │
│       │      │└─────┴────────┴───────┘         │
├───────┼──────┼─────────────────────────────────┤
│1998 12│237.34│┌────────┬────┐                  │
│       │      ││software│meal│                  │
│       │      │├────────┼────┤                  │
│       │      ││220.34  │17  │                  │
│       │      │└────────┴────┘                  │
└───────┴──────┴─────────────────────────────────┘

By altering the values in the expense table it is easy to adapt this calculation to suit your own expenses.

To finish close the connection. You now know how to layout and extract data from spreadsheetS with ODBC.

   NB. disconnect
   dddis ch
0