ODBC MySQL Data connection in Excel

After swatting up on connecting QlikView to a MySQL database, I used the knowledge gained to create a similar set-up, this time with Microsoft Excel. This is how to dynamically connect to and query a MySQL database table directly from Microsoft Excel on a local development machine. I did this using Microsoft Office Professional 2013.

1. Download and install the MySQL ODBC (mysql-connector-odbc-5.3.4-winx64.msi), and create a DSN. I used this excellent tutorial http://www.plaintutorials.com/install-and-create-mysql-odbc-connector-on-windows-7/ to create a DSN called dsnlocalhost
2. Open Excel, open and save a new workbook or open an existing workbook
3. Click on the Data tab
4. Data Connection Wizard option
5. Select the From Data Connection Wizard option
6. Select the ODBC DSN option
7. Select dsnlocalhost from the ODBC data sources list
8. Click Next
9. Select the database from the dropdown list
10. Select the Connect to a specific table checkbox and select the table – you can also connect to a view
11. Click Next
12. Provide a user-friendly name (and other details) for the connection file
13. Click Finish
14. Your connection file will be saved – the default location is: C:\Users\<user>\Documents\My Data Sources
15. You will now be prompted to Import Data, click OK
16. Your spreadsheet will be populated with data
17. Microsoft Query option
18. Select the From Other Sources » From Microsoft Query option
19. Your DSN will appear in the Choose Data Source pop-up; select the DSN you created above
20. Ensure the Use the Query Wizard to create/edit queries checkbox is selected
21. Follow the steps in the Query Wizard, optional steps are to add filters on rows
22. Ultimately this creates a Query – you will be prompted to save the query – you can view at this stage
23. Default location of connection: C:\Users\<user>\Documents\My Data Sources
24. To view the Query, and potentially change it,
    1. On the Data tab, select Connections
    2. Click the Properties button
    3. Select the Definition tab
    4. The query appears in the Command text window at the bottom
    5. Edit the Query manually
    6. Or edit the query using the Edit Query button which launches the MS Query Wizard which is a user-friendly interface that looks like MS Access.

Once you have created the connection, it can be reused in other workbooks – select Existing Connections from the Data Tab and your connection will be listed

If you used Option A, you could still use the Microsoft Query Wizard to make modifications:

1. Data tab Connections
2. Select your connection file
3. Click Properties
4. Click definition
5. At the bottom of the window, you will see your SELECT query; you can manually edit it, or you can click Edit Query and use the Query Wizard