Connecting Excel to AT/MySQL

Given the problems associated with creating and running custom Jasper reports within the AT, we decided to simply use Excel as a front-end application for data analysis. The beauty in this approach is that data can be pulled from the AT tables, or from MySQL queries, and then further analyzed, graphed, etc., in Excel. Pivot tables can even be used to create more robust reporting options. What's more, and with a little know-how, you can even use Excel for batch updates.

Here's how to connect Excel to the AT:

1. Download and install the ODBC driver for MySQL here: http://dev.mysql.com/downloads/connector/odbc/3.51.html.
2. Configure the ODBC Data Source (Control Panel > Administrative Tools > Data Sources (ODBC) > Add >MySQL ODBC 3.51 Driver)
3. Enter your AT database connection settings
4. Open Excel
5. Click on Data >From Other Sources > From Data Conenction Wizard > ODBC DSN > [the database/connection you just set-up in step 3]
6. Select what you want to import

Voila! It's as easy as that.

Right now we're working on constructing more complicated MySQL queries typically used for end-of-year analysis and reporting, which we will then similarly connect to Excel to allow our staff to manipulate the data as needed. Here is where a little MySQL knowledge goes a long ways!

In the end, we thought this was a much more efficient and effective means for providing our staff with customizable reporting functionality from the AT. For one, our staff are more comfortable with and regular uses of Excel. Second, there is much more documentation and training options available for using Excel and its many advanced features.

UPDATE (6/23/2010)

We ran into a bit of a dead end trying to connect custom MySQL queries to Excel. It was easy enough to connect the tables, but connecting to custom queries proved troublesome. Instead, we are now using SharePoint, which does allow you to create custom SQL queries and stored procedures. If interested, more information on how to set this up can be found here: http://office.microsoft.com/en-ca/sharepoint-designer-help/add-a-database-as-a-data-source-HA010100908.aspx#BM6.

PDF & HTML Stylesheets for Resource Reports

As with perhaps most other users, we wanted to devise a means for customizing the AT's EAD output to comply with our local EAD implementation guidelines. Mike Rush at the Beinecke created and maintains a stylesheet that converts our AT EAD output to conform with Yale's EAD best practices. This now works very well for us. We export EAD, apply the transformation, validate it, and then save for upload to our finding aids database. That is all well and good. Our staff, though, wanted to be able to perform a similar operation, really a print preview, when editing a resource. This was possible in our previous EAD editor (XMetaL) thanks to built-in customizations. Although the AT's PDF & HTML resource reports do allow for such a print preview, many of our staff wanted a finding aid that looked more like our own. Thankfully, the AT allows you to swap stylesheets (see the AT's FAQ > Import & Export Questions for instructions) to address such needs. We found a few problems, however, that you may need to take into consideration when swapping stylesheets.

First, make sure to check the path for any or subordinate stylesheets you utilize. If you're saving your stylesheets in the AT's report folder (i.e. C:\Program Files\Archivists Toolkit 2.0\reports\Resources\eadToPdf) make sure to use the path \reports\Resources\eadToPdf\[filename]. Otherwise, if you're pointing to a URL, make sure to use the full URL. This was all that was needed to make our PDF stylesheets run in the AT.

Second, especially for HTML stylesheets, make sure that any parameters specified include default values. This was what was causing errors for us.

With these two simple tweaks we are now able to apply our PDF and HTML stylesheets when staff generate resource reports. Ideally, we would like to apply our AT to Yale BPG stylesheet prior to running the resource PDF & HTML reports, perhaps via a plug-in. I'm sure others would like to modify this process as well. For the time being though, we're satisfied with the current output, which allows our staff to easily preview their work in a format similar to our own finding aids.