Ms-Excel 2011 MySQL Config

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.

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

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

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

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

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

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

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

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

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

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

12. If you’ve configured everything correctly, you’ll see the following confirmation dialog. Click the OK button.

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