MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel 2011 Query MySQL

with 10 comments

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.

Querying MySQL from Excel 2010 #1

  1. 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

Querying MySQL from Excel 2010 #2

  1. 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.

Querying MySQL from Excel 2010 #3

  1. 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.

Querying MySQL from Excel 2010 #4

  1. 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.

Querying MySQL from Excel 2010 #5

  1. 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:

Querying MySQL from Excel 2010 #6

  1. 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.

Querying MySQL from Excel 2010 #7

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.

Written by maclochlainn

February 27th, 2011 at 2:51 am

10 Responses to 'Excel 2011 Query MySQL'

Subscribe to comments with RSS or TrackBack to 'Excel 2011 Query MySQL'.

  1. I encountered a problem and it appears that this may be the problem. Any advice?

    Why does Excel produce “Unable to allocate server handle” errors?

    Are you running Excel on an Intel-based Mac along with our ODBC Driver for JDBC Data Sources or our Express Edition drivers? Our JDBC-related drivers cannot be used by PPC-native applications (such as MS Excel/Query 2004) on Intel Macs, due to limitations in Apple’s JVM.

    The PPC-native application will load the PPC-native segment of the iODBC Driver Manager, and in turn, the PPC-native segment of the ODBC driver — but there is no PPC-native segment of the JVM. It only installs with Intel-native binaries on Intel-based Macs.

    Elise

    3 Mar 11 at 11:49 am

  2. Dear Michael,

    Thanks for the great tutorials for using Office 2011 with MySQL.

    I’d like to add that I found that you can go without the paid OpenLink driver by manually “fixing” the official MySQL drivers, following these older instructions for Office 2008, here:
    http://forums.mysql.com/read.php?37,194261,194261

    This not only worked, but produced nice results regarding the column header titles.

    Claudio P.

    14 Apr 11 at 3:24 pm

  3. Thanks for the tutorials. It was very very helpful.

    shri

    19 Jul 11 at 9:22 am

  4. thanks… still same old stuff from Microsoft! rather than doing all this, paying for 3rd party odbc drivers, i’ve opted to install win7 on my macbook via bootcamp.

    still, it’s a better solution (for me) when i need to connect to MySQL using excel as i also need to run web queries.

    i was excited when Mac upgraded office 2011, however was sorely disappointed to learn of all this…

    steve

    19 Sep 11 at 7:53 pm

  5. I concur with the sentiments. Did you see this post on working with MySQL and Windows 7?

    maclochlainn

    19 Sep 11 at 8:08 pm

  6. This tutorial was so helpful – thanks! Any chance you figured out a way to get rid of the extraneous characters in column names? I can manually delete them, but every time I refresh the data it comes back and messes with functions I’m performing.

    Lindsey

    17 Jan 12 at 2:47 pm

  7. Unfortunately, no. If you figure out a way let me know.

    maclochlainn

    17 Jan 12 at 2:50 pm

  8. Whilst this works OK for static queries – has anyone found a way to do dynamic queries? You know – select * from items where crit = worksheet.cell(5,9)

    Strikes me as pretty limiting to expect users to edit SQL etc or VBA not to be able to access a database.

    Fresh

    22 Feb 12 at 8:56 pm

  9. The bigger issue with this is that Microsoft Query doesn’t work with Lion, so even if you get the ODBC drivers, you cannot use them.

    BIG FAIL, Microsoft!

    Zayne

    7 Jun 12 at 9:32 am

  10. Absolutely true!!!

    maclochlainn

    7 Jun 12 at 2:01 pm

Leave a Reply