MySQL database banner

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 MS 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 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 re-used in other workbooks – select Existing Connections from the Data Tab and your connection will be listed

If you used Option A, you can still use the MS 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

By foxbeefly

PHP / MySQL Developer. HTML, CSS and some JavaScript.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.