Using Database Access Kit and Mathematica Link for Excel

If you have both Database Access Kit and Mathematica Link for Excel installed on your computer, you can use the Database Access Kit transparently from Excel to query and manipulate databases. The following example connects to a simple database and runs a simple query, but you can, of course, add any Mathematica program or analysis.

Making Database Access Kit Available to Excel

To make Database Access Kit available to Excel, open the Mathematica Add-on Libraries dialog by clicking the bookshelf icon on the Mathematica Link for Excel toolbar.

When the dialog box opens, click Update to display all available application packages. Select DatabaseAccess, and click either Load to load the package for this session or Always Use to load this package at the beginning of every subsequent Excel session.

Using the Mathematica Function Wizard

The easiest way to access Database Access Kit functions is using the Mathematica Function Wizard.

Opening the Function Wizard

To start the wizard, click the Mathematica function wizard  icon on the Mathematica Link for Excel toolbar.

Connecting to the Data Source

Type the function name you want to use or a part of it, and click Search. Select the correct function, and click Next.

Enter the function parameters, in this case the name of the data source dbcreate. Press Enter to execute that function.

Querying the Data Base

The execution  command for queries is DataSourceEvaluate. To query the database, open the function wizard again, select DataSourceEvaluate, and click Next.

DataSourceObject was returned to an Excel cell. Enter the cell reference in the datasourceobject text field and SQLSelect[SQLTable["employees"]] in the "command" field. Click Enter to return the employees table of dbcreate.

SQLSelect[SQLTable["employees"]]

Mathematica Link for Excel returns the query result in a Mathematica list in a single cell. To reformat it into a table, select a 3 by 8 group of cells, click in the formula bar as if you were going to edit the existing formula, and press CONTROL-SHIFT-ENTER. This enters the formula as an array formula.

Using the Kernel Dialog

While using the Mathematica Function Wizard is very convenient, it can get a bit tedious. One alternative is using the kernel dialog to execute database commands.

Opening the Kernel Dialog

To open the Mathematica kernel dialog, click the Mathematica kernel dialog icon on the Mathematica Link for Excel toolbar.

Connecting to the Data Source

Simply type in the OpenDataSource command. This assumes that the Database Access Kit is already loaded. If you haven't done so yet, refer to the section "Making Database Access Kit Available to Excel."

Type in your query, and press Send.

Select, copy the output, close the dialog box,  and use the Mathematica Paste button or menu command to paste the result into your spreadsheet.

Using Mathematica Programs

Mathematica Link for Excel can execute any Mathematica program stored in a .m file. For more information on how to do this, please refer to the Mathematica Link for Excel manual. As an example, the script below queries a database of survival data for breast cancer victims, calculates a linear fit to the data, identifies and discards outliers, refits to the data with outliers removed, and plots the data and the fits.

Needs["DatabaseAccess`"];
Needs["Statistics`DescriptiveStatistics`"];
Needs["Graphics`Legend`"]
bcstudy=OpenDataSource["bc98survival"];
data=DataSourceEvaluate[bcstudy,SQLSelect[SQLTable["mortbytemp"],{SQLColumn["Mean_Annual_Temperature"],SQLColumn["Mortality_Rate"]}]];
myfit[x_]:=Fit[data,{1,x},x];
residuals=Table[{i,data[[i,2]]-myfit[data[[i,1]]]},{i,1,Length[data]}];
std=StandardDeviation[Transpose[residuals][[2]]];
newdata=Delete[data,Position[Transpose[residuals][[2]],x_/;x>std]];
newmyfit[x_]:=Fit[newdata,{1,x},x];
fits=Plot[{myfit[x],newmyfit[x]},{x,33,54},PlotStyle->{RGBColor[0,1,0],RGBColor[1,0,0]},AxesOrigin->{30,40},DisplayFunction->Identity];
lp=ListPlot[data,DisplayFunction->Identity];
all=Show[{fits,lp}];
ShowLegend[all,{{{Graphics[{RGBColor[0,1,0],Line[{{0,0},{1,0}}]}],StyleForm["With\nOutlier\n",FontSize->9]},{Graphics[{RGBColor[1,0,0],Line[{{0,0},{1,0}}]}],StyleForm["Without\nOutlier\n",FontSize->9]}},LegendPosition->{.5,-.4},LegendSize->{0.85,0.4}},DisplayFunction->$DisplayFunction,ImageSize->{500,300}]

The Mathematica Link for Excel manual describes a number of ways you can run Mathematica programs. One way is by calling the complete or relative file path of the .m file in the kernel dialog with Get.

After the program has executed, click Graphic to paste the result into the spreadsheet.