MacLochlainns Weblog

I finally got around to finishing my testing of Excel 2011 querying MySQL. That meant installing and configuring Excel 2011 and MySQL 5.5.9 on Mac OS X (Snow Leopard). While installing Microsoft Office is pretty trivial, installing and configuring MySQL wasn’t. You can read about installing and configuring MySQL here. A quick test after this, led me to discover that you still need a third party ODBC, as covered in this earlier blog. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver.

I downloaded and installed one of third party ODBC tool sets. I opted for OpenLink Software’s ODBC Driver. Instructions for the install with screen shots are in this blog page. The only downside of this was the discovery that Microsoft’s solution requires Rosetta, like Excel 2008. Rosetta enables Power PC application to run on Intel-based Mac OS X.

Launching the Database icon from Excel 2011, I configured the Data Source Name, which you can find here with screen shots. After you configure the Data Source Name, restarting Excel 2011 is the best choice because otherwise you may see several non-fatal errors.

The following screen shots show you how to establish a connection between Excel 2011 and MySQL, and how to query data from the MySQL database:

1. After you click the Database icon, you see the following dialog. Select a Data Source Name and click the OK button to begin a query.

2. The OpenLink MySQL Lite Login screen requires the user name and password. Click the Connect button to launch the Microsoft Query, which appears to be a native Power PC application that requires Rosetta to run it

3. Microsoft Query appears to be a native Power PC application that requires Rosetta to run it. If you want to enter a query, click the SQL View button.

4. Having clicked the SQL View button you now have a work area where you can enter a standard SQL SELECT statement, like the one below. Then, you click the Return Data button.

5. This dialog lets you select where you want to put the return result set from the query. The default is the absolute cell reference of the top and left most cell, $A$1. Click the OK button to query and load the data into the worksheet.

6. Now you can see the data in the worksheet. The only problem is the extraneous characters returned into the column headers of the table. While tedious, they’re easy to fix. The following illustrates the downloaded result set from the previous query:

7. If you perform a query with a join operation, the column names are never displayed whether you provide aliases to the query or not. It means you have to convert the table to a range, remove the false headers, and recreate the table. This appears to be a limitation of Microsoft Query and unlike the behavior in Excel 2010 on Windows. Perhaps it’s all wrapped up in the emulation provided by Rosetta but I couldn’t find any information about what’s happening. That leaves me with pure speculation, which I never like. If you find the reason, post a comment with a link because everyone would benefit.

As always, I hope this helps those who want to work only in the Mac OS X environment. The risk is Rosetta because it will go away, the only question is when and whether the vendors will fix their dependency first or not. The problem with this solution is that Microsoft Query doesn’t return any tables when it appears that it should.