Friday, June 11, 2010

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.

4 comments:

  1. I'm curious about your comment that "with a little know-how, you can even use Excel for batch updates." It sounds like you are manipulating the database directly from Excel?

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Although technically possible, I'm not sure if you would want to update or alter data in the AT's MySQL database directly from Excel. I, at least, am a little weary of that. What I do is use Navicat for MySQL to query or view the AT's tables and then copy certain data into Excel for manipulation. I then either create MySQL UPDATE statements from said data to execute via Navicat's Console window or simply paste in to the table columns from Excel. Again, BE VERY CAREFUL.

    ReplyDelete
  4. This was great information! I successfully connected and grabbed data both for MSExcel (copying all accessions table data) and for MSAccess (copying ALL the tables).

    This kind of functionality will make reporting and metrics much easier!

    ReplyDelete