Exporting a MySQL Database to Excel Using ODBC

One of the most useful applications involves automatically transferring the data from a MySQL table to an MS Excel workbook. There are a number of ways to do this; some have disadvantages. This article will show you how to do it with the MySQL ODBC driver, one of the more efficient ways of moving the data.

In a previous ASP Free article,  I discussed how to convert a MySQL database into an Excel workbook. The techniques discussed in that tutorial have the following disadvantages:  

  • If a developer exports a MySQL database into an Excel workbook using that technique, the data imported to the Excel workbook CANNOT be updated when the MySQL database is updated. There is no communication interface between MS Excel and the MySQL database. 

  • Exporting any MySQL database table to Excel is a lengthy process, since any developer will still need to log in to the MySQL database, click on Export, select options, download the file and then re-format the downloaded information to be compatible with an Excel workbook. If shortcuts can be made, especially when exporting several databases to their respective MS Excel workbooks, then it saves a lot of time and increases efficiency.

The objective of this tutorial is to teach you how to retrieve all the data for an  entire MySQL table to an MS Excel workbook using the MySQL ODBC driver.

What are ODBC driver and its requirements?

We all know that MySQL is an open source database and is currently the most popular database used with websites. Because of this popularity, enormous applications are being developed. Microsoft Excel, being the most popular spreadsheet used for technical and business applications, needs a way to communicate with MySQL databases. Since MS Excel is proprietary and not open source, an API was developed by Microsoft in partnership with Simba Technologies. This API is called ODBC (Open Database Connectivity).

The objective of ODBC is to let developers connect to open source databases like MySQL and fetch data to MS Excel. This application programming interface increases the efficiency of programmers working in both proprietary and open source database environments.

MySQL ODBC is not included in the default installation of Windows XP (as of this writing, whether or not it is part of the default installation of Windows 7 was unknown). This tutorial has been tested using the Windows XP Home environment and MS Excel 2002. However, this would most likely to work also with newer Windows and office software (like Windows 7 and MS Excel 2007). The procedure discussed in this tutorial can be applied to them also, with minor variations.

{mospagebreak title=How to install MySQL ODBC driver}

Before you can connect to a MySQL database server using MS Excel, you need to install ODBC on your Windows computer. To install, follow the steps below.

Step 1: Download ODBC

Go to the section under “Windows Downloads” and look for Windows MSI Installer (x86) — this is for a 32-bit environment, which covers most Windows XP users. If you have a 64 bit Windows XP, you can select the Windows MSI Installer (AMD64/ Intel EM64T).

Step 2: Click “Pick a mirror.”

Step 3: Click “No thanks, just take me to the downloads.”

Step 4: Select the closest country to your location.

Step 5: Click “HTTP.”

Step 6: The download dialog box will then pop up. Click “Save File.”

Step 7: Close all programs running in your computer. Go to the download location. If you are using the Firefox browser in Windows XP Home, it will be My Documents -> Downloads.

Step 8: At the time this tutorial was written (December 2009), the filename of the installer was: mysql-connector-odbc-5.1.6-win32

You are now ready to install the ODBC driver. To start the installation, double click on the installer and click “Run.”

Step 9: Click “Next” and when an option is shown, select “Typical.” Proceed with the rest of the installation, and finally, click “Finish.”

Step 10: Confirm that you have correctly installed the driver. Go to this path:

Control Panel -> Administrative Tools -> Data Sources

You can then see the ODBC Data Source Administrator. To check if the MySQL ODBC driver has been installed, click “Add.” You should see “MySQL ODBC 5.1 Driver.”

The installation is now complete and you are ready to connect to a remote MySQL server directly through your MS Excel application.

{mospagebreak title=How to add a MySQL database server to ODBC}

Before you can access the database, you need to configure it. Follow these configuration steps:

Step 1: Go to this path: Control Panel -> Administrative Tools -> Data Sources

Step 2: Click “Add.”

Step 3: Select MySQL ODBC 5.1 Driver, and click “Finish.”

Step 4: The MySQL Connector/ODBC Data source configuration will then show up.

Fill in the blank fields except “Database.” For example, I will access the S & P 500 MySQL database table, so I will use the following settings:

Data Source Name: SP500

Description: S&P 500 Index data

Server: fdb9.agilityhoster.com

User: 291671_phpdev

Password: *******

Leave the Port to default, which is 3306. This is the default port used for MySQL connections. You should see a dialog box like the one below:

Step 5: Click “Test.” After clicking on “Test,” if ODBC successfully connects to your MySQL server, then a pop-up dialog box will appear:

Test Result

Connection Successful

Click “OK.”

Step 6: Select a specific MySQL database name under the “Database” drop down menu. It may freeze for some time, especially if your server is located very far from you. You need to wait for at most three seconds for your databases to show up under the drop down.

Step 7: Click “OK” and then click “OK” again to close all Windows ODBC dialog boxes.

You have now successfully configured the ODBC to connect to a specific remote MySQL database.

{mospagebreak title=Automatically exporting data from MySQL to MS Excel}

Now that everything has been successfully configured, follow the steps below to automatically export data from a remote MySQL database to your MS Excel workbook.

Step 1: Open a new/blank MS Excel workbook. (Start -> All programs -> Microsoft Excel).

Step 2: Go to File -> Save as, type a relevant file name. In this example, I will name it SP 500 data.xls ,  and then save it to the desktop.

Step 3: Go to Data -> Import External Data -> Import Data and click “New Source.”

Step 4: Under “What kind of data source do you want to connect to?” select “ODBC DSN” and then click “Next.”

Step 5: Under “ODBC data sources,” select the specific data source name you created in the previous section. In this example, I will select “SP500” and then click “Next.” See the screenshot below:

Step 6: The MySQL tables of your selected MySQL database will then show. Select the table you need to export to Excel and click “Finish.”

Step 7: Under “Select Data Source,” click “Open.”

Step 8: The “Import Data” dialog box will then appear. Select “New Worksheet” and click “OK.” This will import all of the data from your MySQL table to a new worksheet.

How to manage the results

If the MySQL database table will be updated, you can easily update the MS Excel workbook connected via ODBC also by going to Data -> Refresh Data. By refreshing the data, changes in the MySQL table will be reflected automatically in your Excel workbook.

You can then interact more with the information by using Excel formulas to analyze the downloaded data. It is recommended that you use only one Excel workbook for one MySQL database table. If you need to import another table under the same MySQL database, you need to create a separate workbook for it, and then add a separate data source by repeating steps 3 through 8.

2 thoughts on “Exporting a MySQL Database to Excel Using ODBC

  1. With SQLodbc you can use odbc connections to execute SQL importing the result into excel

    http://sqlodbc.t35.com

    example:

    1- select a range of cells where you want the database data exported
    2- place on that range the SQLodbc function example =sql.execute( ; )
    3- execute function as array on the given range (ctrl + enter)

[gp-comments width="770" linklove="off" ]