With the advancement of Open Source applications into closed source platforms, the integration of the two categories of development models become more and more intertwined. The weight of these different development models are beginning to be more and more evenly distributed, that it’s hard to avoid merging these two solutions in modern day enterprises.
One of the most popular open source software is the MySQL database. It has continuously been considered by both small and large corporations to be deployed in internal as well as distributed applications.
On the other side of the spectrum, we have Microsoft Office. Microsoft’s premier office applications suite that continues to be the most dominant electronic format for documents, spreadsheets and presentations.
In this article, I’m going to demonstrate how we can use Microsoft Excel to build a simple report from data stored on a MySQL database.
Requirements
Requirements for this article are as follows:
- A MySQL installation configured to allow TCP/IP access. This installation can be on any platform supported by MySQL.
- A Windows XP “client” that has Microsoft Excel and MyODBC installed.
Preparations for MySQL
In this article, Excel will be used to access data stored on a MySQL database hosted by a remote machine. This is why the MySQL installation must be configured to allow TCP/IP access.
For the purpose of this article, it’s presumed that the MySQL database is installed on a Linux host called rdsblinux.
The table that will be used in this example is called employees and it will reside in a database called test (created by default in a standard MySQL installation). The SQL dump for this table can be downloaded here. Basically, it contains a list of name, extension number and departments of people working in a purely fictional company.
For accessing this data, we will be using a MySQL user with the following details:
- Username: reporter
- Password: reporter
- Host: any (%)
- Priviledges:
SELECTon test database
It is recommended that reporter to not have other priviledges than outlined above because this user will only be reading data and not do any writing to it.
Preparations for Client PC
We need to add an ODBC data source for our MySQL database. Here are the steps on how we can create this:
- Click Start, point to Settings, and then click Control Panel.
- Double-click Administrative Tools, and then double-click Data Sources (ODBC).
- Under the User DSN tab, click Add.
- In the Create New Data Source window, select MySQL ODBC 3.51 Driver and click the Finish button.
- In the Connector/ODBC window that appears, enter the following details (the information are as mentioned earlier):
- Click on the Test button to check that the settings are correct.
Accessing the Data from Excel
Start Microsoft Excel and create a new document. Go to the Data menu, select Import External Data and click on New Database Query (as shown on the image below).
In the Choose Data Source window, select excel example and click OK.
In the Query Wizard window that appears, expand the employees tree and select the name, department and extension columns to be included in the query (refer to the following screenshot), and click Next.
Just click Next on the resulting Filter Data and Sort Order screens. At the Finish screen, just click the Finish button. When asked where to place the data, select cells A1 to C1 (as illustrated below) and click OK.
Taraa! Your spreadsheet should now contain the names, department and extension number for staff in the company.
Real World Implementations
Although this article is admittably simplistic in nature, the real world implementations of using the MySQL ODBC Connector with Excel is virtually endless. Some examples that I can think of (and/or have done before) include:
- Generating sales reports and graphs
- Automatically creating mailing lists from a membership web site
- Designing an interactive security report generator using conditional formatting to highlight critical areas
If you were inspired to create your own MySQL driven spreadsheets from reading this article. I would love to hear what you did and how it has benefitted you. You can do so by commenting under this article.