Even in this age of powerful reporting tools like Crystal Reports and flexible customer management applications like SugarCRM, Microsoft Excel remains the most commonly used tool for analyzing information such as contact demographics, sales statistics and revenue projections. This is for good reason; Excel has been a mainstay within office environments for years because of its tremendous array of features for assessing and charting information of all sorts.
Yet companies are increasingly storing data in remote databases so that it can be available for perusal and modification by a variety of sources. Sales staff members are constantly on the road, the remote worker trend continues and organizations continue to globalize, rendering the notion of an in-office corporate file server almost obsolete.
But workflow preferences are hard habits to break. What do you do when the boss demands to use Excel for building pie charts of the latest sales data even though he's sitting in Singapore?
You might be surprised to know that you can connect Excel to a database, and this isn't limited to databases running Microsoft's SQL. Excel can connect to practically any mainstream database (MySQL, PostgreSQL, Oracle and others), provided that the database offers an ODBC (Open DataBase Connectivity) driver. In this article, you'll learn how to connect MySQL to Excel.
Installing MyODBC
ODBC offers a standardized means for performing tasks such as data retrieval, deletion and selection, meaning that a custom driver must be written for each database in order to ensure that the solution-specific way of doing things is properly translated to the ODBC standards. MySQL has long had a custom ODBC driver available, known as MyODBC. You'll need to install MyODBC on each machine on which you plan on using Excel to talk to MySQL.
So, now would be good time to, download the MyODBC installer. The MSI version ...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
is only 2.3 MB; presuming you have a broadband Internet connection, the download should be completed fairly quickly.
Once you've finished downloading MyODBC, click on the icon to begin the installation. Select the Typical setup type unless you have special installation requirements. Click the Install button and when the process completes, click Finish to exit the installer.
Configuring MyODBC
On WindowsXP, you can find the ODBC console by navigating to Settings » Control Panel » Administrative Tools » ODBC Data Source Administrator.
[IMAGE]
Figure 1. The ODBC Data Source Administrator
Click the Add… button to add a new data source and scroll down until you find the MySQL ODBC driver entry. Double-click on that entry to begin the configuration process. The configuration window is displayed in Figure 2.
[IMAGE]
Figure 2. Adding a MySQL data source
Complete each field in the Login tab, and press the button to verify whether the driver is able to talk to MySQL. If you're running MySQL on the same Windows machine for test purposes and localhost (or 127.0.0.1) isn't working as the Server address, try using a single dot which will cause the driver to use a named pipes connection rather than TCP/IP. If you're attempting to connect to a remote MySQL server, don't forget to ensure that the firewall is configured to allow your machine to talk to the server on port 3306. Finally, don't forget to use an existing and properly configured MySQL login combination. If you intend to allow users to not only read but also insert and update data, then the appropriate permissions must be assigned.
Once you've successfully tested the connection, click Ok, which will add the connection to the available data sources list. Next, we'll connect Excel to MySQL.
Connecting Excel to MySQL
For purposes of this exercise, we'll connect Excel to a MySQL table titled contacts, which contains the contact information of various partners and relevant individuals around the country. The MySQL table looks like this:
I've filled this table with some sample data that you'll soon see in an Excel screenshot. To manage this data from Excel, follow these steps:
- Open Excel and navigate to Data » Import External Data » Import Data.
- From the window that appears, choose +Connect to New Data Source.
- A window titled "Welcome to the Data Connection Wizard" will appear. From this window choose ODBC DSN.
- From the window titled Connect to ODBC Data Source, choose the MySQL ODBC data source you created earlier.
- Finally, from the window titled Select Database and Table, select the database and table you'd like to connect to. This window is shown in Figure 3. Select the table and choose Next.
- On the final window titled Save Data Connection File and Finish, choose Finish.
- Finally, you'll be asked where to place this data. You can choose the existing worksheet or specify a new worksheet. Make your decision and press OK.
[IMAGE]
Figure 3. Connecting to a specific database and table
If, upon finishing the items presented in the above set of instructions, you once again see Figure 2 and ultimately an error message stating [MySQL][ODBC 3.51 Driver]User
cancelled., you've encountered an unresolved and mysterious Excel-related bug. Instructions for resolving this bug are provided here.
Assuming all goes well, your information should be dumped to Excel, as displayed in Figure 4.
[IMAGE]
Figure 4. Viewing the contacts database in Excel
From here, the user is free to build interesting charts as necessary. For example, building a pie chart is trivial, as Figure 5 demonstrates.
[IMAGE]
Figure 5. Charting revenue per contact
Where to go from here?
While the above example is realistic, it's nonetheless rather simplistic. What if your user required a somewhat more sophisticated set of data, perhaps drawn from a complex join? The answer is simple; create a view, and then connect to the derived table via the spreadsheet.
Conclusion
MySQL's MyODBC driver opens up a whole new world of possibilities in terms of connecting database data to a variety of client applications. I hope this short article has left your mind buzzing with the possibilities.
About the author: W. Jason Gilmore has developed countless Web applications over the past seven years and has dozens of articles to his credit on Internet application development topics. He is the author of three books, including Beginning PHP 5 and MySQL 5: From Novice to Professional, (Apress), now in its second edition, and with co-author Robert Treat, Beginning PHP and PostgreSQL 8: From Novice to Professional (Apress). Jason is the co-founder of IT Enlightenment, a technical training company.
