MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel 2011 MySQL Config

with 4 comments

This blog post shows you how to configure Excel 2011 on Mac OS X to query a MySQL database natively installed on the same Mac. If you need installation instructions for the MySQL database natively on Mac OS X, use this post. The configuration is only required the first time and then you may reuse the connection later.

Before you can begin these steps inside Excel 2011, you need to download and install OpenLink Software’s ODBC Driver. I’ve posted instructions in this other blog page because all the screen shots make page loading a problem. You open a new Workbook and click on the Data tab. Then, on the Database icon in the Data ribbon. It lets you import data from the database.

The configuration steps are as follows:

  1. This first step prompt you to install Rosetta which allows native Power PC applications to run your Intel-based Mac OS X. It will eventually no longer be supported with the release of Lion (at least that’s the rumor at the time of writing). Click the Install button to get this working.

Configure Excel 2011 to query MySQL #1

  1. The install took a minute on my Mac Pro, so I figured you should see the progress bar in case it takes a half minute or so.

Configure Excel 2011 to query MySQL #2

  1. After the completion, you now configure a Data Source Name. This type of configuration is provided for by components on the Windows OS (see this post for an example), which don’t exist on Mac OS X. It appears that Microsoft didn’t want to provide those components, which are a dependency for Excel 2011, and that’s why you need OpenLink Software, specifically the iODBC Data Source Connector. You should note that it looks virtually the same as the Windows OS component. Click the Add button to proceed.

Configure Excel 2011 to query MySQL #3

  1. The next screen lets you choose a driver from those installed on the Mac OS X system. If you went to the bother to install the free MySQL Connector/ODBC (instructions here), it won’t work because the Microsoft Component requires Rosetta to work. It appears that Microsoft Query isn’t a native Intel-port but rather a Power PC port. Choose one of the OpenLinnk drivers and click the Finish button to continue.

Configure Excel 2011 to query MySQL #4

  1. The next step requires that you configure the Data Source Name (DSN), provide a hostname and listener port for MySQL (the default port is 3306). After you enter these values, click the Connection tab to the right of the Data Source tab in the dialog box.

Configure Excel 2011 to query MySQL #5

  1. This step requires that you set the user name, password, and database. The ability to pick the database in the iODBC Data Source Connector is the result of the MySQL Lite tool. It requires that you’ve configured the database first. After entering the data, click the Options tab in the dialog to set the next set of values.

Configure Excel 2011 to query MySQL #6

  1. This step doesn’t initially let you do much but afterward you can set the character set. Click the Preference tab to proceed with the configuration.

Configure Excel 2011 to query MySQL #7

  1. There’s nothing you need do here, but I’d suggest in a real situation that you click the Always include VIEWS in table list check box before you click the Finish button.

Configure Excel 2011 to query MySQL #8

  1. The Data Source Name setup is complete, the next screen lets you launch a connection to the MySQL database. Click the OK button to complete the configuration.

Configure Excel 2011 to query MySQL #9

  1. At this point, you repeat the Step #1. It launches the iODBC Data Source Chooser dialog. Click the Data Source Name and then the Test button.

Configure Excel 2011 to query MySQL #10

  1. The test launches a dialog to test the connection, as shown below. Enter the Password in the dialog.

Configure Excel 2011 to query MySQL #11

  1. If you’ve configured everything correctly, you’ll see the following confirmation dialog. Click the OK button.
  2. Configure Excel 2011 to query MySQL #12

Hope this helps you if you’re looking to connect Excel 2011 to query MySQL.

Written by maclochlainn

February 27th, 2011 at 12:36 am

Posted in Uncategorized

4 Responses to 'Excel 2011 MySQL Config'

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

  1. […] configured the Data Source Name […]

  2. Hi,

    I am new at all this. I have installed MySql 5.6.19 in my Mac OS X10.9. I have also installed Open link software ODBC DRIVER. Now, when I am in the connections tab in Open Link Mysql lite setup wizard. I click on the databse, I get an error: Access denied for user ‘user’ @’localhost’. I dont know how to establish a database. Could you please help me with it?


    Tanya Singh

    6 Jun 14 at 4:47 am

  3. Please be aware — Updating Office 2011 to 14.1.2 (released June 2011) or later brings an Intel-native Microsoft Query which is compatible with OS X Lion, Mountain Lion, Mavericks, and Yosemite (which do not include the Rosetta components that were available for Snow Leopard, Leopard, and Tiger). Office updates are easily available through AutoUpdate, by starting a Microsoft Office program; then, on the Help menu, clicking “Check for Updates.”

    All components from OpenLink Software, including the iODBC components on which Query and Excel depend, are fully Universal binaries — 32-bit and 64-bit, PPC and Intel — and we ship versions compatible with every version of Mac OS X named herein.

    We also have a knowledgebase article about Tanya Singh’s reported `Access denied` error. It’s a MySQL issue; not in the ODBC components at all.

    Ted Thibodeau Jr

    14 Jan 15 at 8:16 pm

  4. Ted, Thanks for the clarification.


    15 Jan 15 at 11:48 pm

Leave a Reply