Published: Andreas Wiebe, 08 July 2010 - 21:59

Connecting Excel 2007 & 2010 to MySQL using ODBC

It is possible to use pivot tables with a MySQL table to get a better view of your data. Note that you can only connect to one MySQL table for each Excel table or pivot table you want, if you require information from several tables you’ll need to create a joined table in MySQL and connect to that.

1. Download and install the MySQL ODBC driver
   1. You can obtain the MySQL ODBC driver here: http://dev.mysql.com/downloads/connector/odbc/5.1.html
   2. Once downloaded, install by double-clicking the file and following the prompts
      • See here for a step-by-step guide to installing the driver
2. Create an ODBC connection to the MySQL database
   • See here for a step-by-step guide to creating the ODBC connection in Windows
3. Add the ODBC Data connection in Excel 2007 & 2010
   1. Open Excel
   2. Click Data in the Ribbon
      
   3. Click From other Sources and select From Data connection Wizard in the drop-down
      
   4. Select Other/Advanced in the Data Connection wizard and click next
      
   5. In the Provider tab select "Microsoft OLE DB Provider for ODBC Drivers" and click Next
      
   6. In the Connection tab, select your datasource name and click ok
      
   7. Select the database and table you want to pivot, then click Next
      
   8. Click Finish to save the connection with the default filename
      
   9. In the import Data dialog, select pivot table report and click ok
      
   10. Your MySQL table will now be available to you in Excel