Exporting a MySQL Database to Excel Using ODBC
(Page 1 of 4 )
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.
Next: How to install MySQL ODBC driver >>
More Database Articles
More By Codex-M
