Interfacing with MySQL via Excel

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: SELECT on 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:

  1. Click Start, point to Settings, and then click Control Panel.
  2. Double-click Administrative Tools, and then double-click Data Sources (ODBC).
  3. Under the User DSN tab, click Add.
  4. In the Create New Data Source window, select MySQL ODBC 3.51 Driver and click the Finish button.
  5. In the Connector/ODBC window that appears, enter the following details (the information are as mentioned earlier):
    Sample ODBC Settings
  6. 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).

New Database Query Screenshot

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.

Selecting Columns for the Document

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.

Placing the Data on Your Spreadsheet

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.

16 responses to “Interfacing with MySQL via Excel”.

  1. Chad Says:

    Wondering if you can make changes in Excel and have those changes go back to the mysql database. Is that possible?

  2. Site Admin Azmeen Says:

    Hi Chad,

    Yes. That’s pretty much achievable with VBA.

  3. David Mitchell Says:

    I used your article to set up an ad-hoc reporting interface to our product development dashboard. The dashboard is deployed on MySQL and replaced a FileMaker database.

  4. Antonello Says:

    I have not understood if, once saved the spreadsheet, every time I open it the query will be automatically re-executed, provided the ODBC exists and the DB is accessible.
    Thanks, bye.
    Antonello

  5. TS7 Says:

    @Antonello, by default the query will not be re-executed. But all you need to do once you have the query working in Excel is to:
    1. Click a cell in the range you want to refresh
    2. Click Data Range Properties on the External Data toolbar 3. Click the “refresh data on file open” box.
    Save, re-open, and you’re good to go.

  6. seun Says:

    can excel submit into mysql….how?

  7. Inserting Data Into MySQL From Excel Using VBA - HTNet Says:

    […] seun asked in an old post of mine, Interfacing with MySQL via Excel; whether it’s possible to submit data from Excel into MySQL. […]

  8. Mark Ruiter Says:

    When I’m at step the step to select the database and to make an query I get an error: ‘Database does not contain visible tables’. What am I doing wrong, I’m sure there are tables.
    I have no problems importing complete tabeles into Excel from the same database.

  9. confusedus Says:

    Mark had the same issue as I. Guess I will have to really learn MYSQL.

  10. thhui Says:

    You have to enter the password first
    as the query does not have that information in the dsn file.

    After you submit the correct password,
    everything will be normal.

  11. Victor Says:

    I have the same problem – “Database does not contain visible tables”. It isn’t a logon problem, because the MySQL query browser gets in with the same username and password and can see the tables just fine.

  12. virgoptrex Says:

    It works like charm!!! I got error “Database does not contain visible tables” earlier. I got it working later. First and foremost you need to download ODBC connector 3.5 + version from MySQL http://dev.mysql.com/downloads/connector/odbc/3.51.html. If you are programming from C# like me or any .NET platform you also need to download http://dev.mysql.com/downloads/connector/net/5.1.html I just had to make a successful connection in MS Excel 2007 at the step (This is the most imp step) “Preparations for Client PC”. Select . Make sure to “test” it. If “successful” then only it works. Since my server is running on the same machine as my client – user = “root”, server “localhost” password = ” xxxxx ” (must know at the time of creation when you are installing MySQL server http://dev.mysql.com/downloads/mysql/5.1.html) all without quotes. In my case I had created (yes make sure you have database with tables in it – else go to tutorial on creating database with MySQL http://dev.mysql.com/doc/refman/5.1/en/database-use.html). Hope this helps all!!!

  13. virgoptrex Says:

    It works like charm!!! I got error “Database does not contain visible tables” earlier. I got it working later. First and foremost you need to download ODBC connector 3.5 + version from MySQL http://dev.mysql.com/downloads/connector/odbc/3.51.html. If you are programming from C# like me or any .NET platform you also need to download http://dev.mysql.com/downloads/connector/net/5.1.html I just had to make a successful connection in MS Excel 2007 at the step (This is the most imp step) “Preparations for Client PC”. Select . Make sure to “test” it. If “successful” then only it works. Since my server is running on the same machine as my client – user = “root”, server “localhost” password = ” xxxxx ” (must know at the time of creation when you are installing MySQL server http://dev.mysql.com/downloads/mysql/5.1.html) all without quotes. In my case I had created (yes make sure you have database with tables in it – else go to tutorial on creating database with MySQL http://dev.mysql.com/doc/refman/5.1/en/database-use.html). Hope this helps all!!! cheer up! 🙂

  14. TD Says:

    For an elegant solution to make easy Excel reporting on MySql you may want to check Jet Reports :

    http://jetreports.com/capabilities/demo-videos.php

  15. Military Connectors Says:

    Informative article, yet I still am looking for a way of connecting excel to mysql to retrieve info via a form.

  16. khim Says:

    nice sir,,, i really like to work on that.. unfortunately no one can teach me.. i was just reading and i got my idea on Excel into mysql from here…. would u mind if i ask u to do some another example of this,, i cant open the one u’had example in there…. thank you and i appreciated this..=)