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.

Locations

Creating locations in the AT is fairly straightforward, especially for simple numeric ranges. AT's batch location creation tool is very handy here. Creating more complex locations, especially those with alphanumeric components, however, cannot be done in the AT. This was unfortunate for us because although the vast majority of our material is stored offsite, the number of distinct onsite locations we have is still close to 3000 given the unique combination of rooms, ranges, sections, and shelves. Rather than try to generate these manually in the AT, I chose to create them in Excel and then paste directly into the AT tables. Here's how I did it.

First (after having surveyed all of our locations to come up with room numbers, ranges, sections, and shelves), I opened the LocationsTable in Navicat and copied the last record into Excel. [note: we had entered in some locations already.] I then used this record to structure and format subsequent location entries and assign sequential locationIds. Excel's fill-in feature is extremely helpful here, especially for alphanumeric data. Simply typing in 1A1, for example, and then dragging the fill-in cross-hairs to the appropriate point, allows you to quickly generate values 1A1, 1A2,...1AX. Once finished adding in all your data, your spreadsheet should look like this: sample (.xls).

The final step is to paste the records into the LocationTable via Navicat. [note: as I explain in my post on creators, you need to make sure you are the only one using the AT when pasting in data or looking up ids. Again, this is to prevent overlap or duplication of work.] Once finished, check your work in the Locations module (Tools > Locations), making sure you can create additional (i.e. test) locations.

AT Tips & Tricks: Transfer Components

One of the key weaknesses, in our opinion, of the early AT releases was the inability to import and attach EAD for an accession or addition to an existing resource. The Yale University Archives uses an inventory template, which offices fill out and email to us with each new accession. The template, an Excel spreadsheet, incorporates EAD tags and allows us to simply copy and paste encoded description directly into a finding aid, requiring only minimal clean-up. Without a means for importing this partial EAD into early versions of the AT meant we either had to re-enter the information into the AT or delete the resource and re-import it with the addition. The problem with the later is that deleting the resource also deletes all the location and instance information tied to the resource, which, in our case, is sizable. With the addition of Transfer Components functionality in v.1.5, however, the AT allowed us to import partial EAD without losing information assigned in the AT. Here's how it works.

First, create a dummy EAD finding aid with the addition/accession EAD as a component. I generally add the new accession EAD to the existing finding aid for a resource and delete all other components. Second, import said finding aid into the AT. Third, open the the resource in the AT into which you want to transfer components. Fourth, click on the Transfer button and select the dummy resource to import components from. Fifth, update the resource as necessary (e.g. extent, dates, etc.). Lastly, delete the dummy resource from the AT. That's it. It's that simple.

We originally thought about creating a plug-in to allow us and others to import and append EAD components into a resource, but ultimately decided/realized that the Transfer function was sufficient and free. Sometimes you just have to be a little creative in your approach to maximize the AT's functionality.

Batch Editing & Data Clean-Up

One of the key weaknesses of the AT is the inability to batch edit data. The need for batch edit functionality was ranked of high importance in the AT user group survey and will hopefully be added in a future release. What then is a repository to do in the meantime? I suggest two possible options: 1) batch edit data prior to import; 2) manipulate the MySQL tables directly or use a database administration tool such as Navicat for MySQL to connect to the AT's MySQL database and perform queries/updates in the tables.

As I have described before, over time our collection management data has been created according to a number of different ad hoc and defacto standards. We in MSSA have tried as much as possible to batch edit and standardize our data prior to import into the AT. This was straight forward for our accessions and location information which was already stored in a database and thus easy to identify and manipulate. The one problem that did exist with this data was a tendancy by MSSA staff to combine data that belong to a series of different data fields, as instructed by EAD or the AT, into a single catch-all free text note field (a place to find everything). Options for handling this data included exporting or legacy data to and modifying another file or importing into the AT and then editing. We chose the former, performing batch operations to format the files according to the AT import maps. Although this was largely successful, we still encountered edits that needed to be made once in the AT. The options at that point were either to edit in the AT one at a time or perform another round of edits, delete the data in the AT, and then reimport again. We chose instead to perform batch edits in AT using Navicat, saving a considerable amount of time and effort.

The biggest challenge though we faced in standardizing our data prior to import came with our finding aids. Because they're not in a database and therefore not easily comparable, it's hard to see what changes need to made until they're actually in the AT. No matter the number of iterative batch edits we ran on our finding aids we still came across edits that needed to be made. Importing them into the AT however would still likely require a further round of edits. Running edits outside of the AT, deleting all the data, and then reimporting them would be a huge burden, particularly on over 2500 finding aids. We chose instead to run batch operations in the AT using Navicat and then run find/replace separately on the finding aids.

Tips and Tricks: Resources

In migrating your legacy data into the AT your repository might have collections that lack both a finding aid and MARC record but yet still need to get into the AT as resource records. Rather than take the time to manually create these resource records in the AT one by one, it would be helpful to come up with a means for importing this information in a batch process. MSSA had over 250 such records that we had to bring into the AT. Here is how we did it.

The legacy data that we had to address, consisting mostly of what can loosely be described as deaccessions (i.e. collections that were transferred, destroyed, never came in, and/or who knows what), was in a Paradox database. Given the orderly data structure, we decided to to generate simple EAD finding aids for each collection using a mail merge and then batch import into the AT.

The first step was to export the data from our Paradox database into Excel. We then filtered the information to select only the specific collection records needed and deleted any unnecessary data elements. We then modified the data bit, in this case creating a column for filename in addition to the other data elements present (e.g. collection number, title, note, and disposition). This served as the data source for the merge.



Next we modified our existing EAD template to include only the basics needed for import into the AT (namely level, resource identifier, title, dates, extent, and language), as well as the information present in our Paradox database to distinguish it as a deaccession (e.g. note and disposition). We then opened the EAD template in Word and set up a mail merge, inserting the elements from our Excel data source into the appropriate places in the Word document. Here is a partial view of the Word EAD template:



Then we completed the merge of the data elements in Word and saved the individual finding aids with appropriate filenames. Here is a partial view of one of the resulting finding aids:



The final step was to import the finding aids into the AT. To distinguish these resources in the AT we checked them as Internal Only in the Basic Description tab and modified appropriate note fields as needed.

All in all the process proved very easy and was much faster than trying to enter this data manually. The only real drawback was having to save 250 separate finding aids.