Studio/ODBC Simple Application
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