Converting a MySQL Database to an Excel Workbook

At this point you may be wondering why you'd want to export your data from the database. The reason is simple: working with Excel will allow you to very easily analyze your data, create graphs, or even execute more user-friendly commands than you can while working with server-side MySQL.

MySQL database and Microsoft Excel are very similar to each other in the sense that both of them have tables, cells and columns. However, a serious incompatibility exists between those two technologies. MySQL is open source free software) and MS Excel is owned by Microsoft (not free of course). This means it is not straightforward to convert a MySQL database into an Excel workbook.

This article will look into the best way of converting a MySQL database into an Excel workbook in three applicable scenarios (local host using XAMPP, using phpmyadmin in the server, or working with a direct MySQL file).

Although this may seem easy for advanced users, at some point in time, things can get very confusing when exporting the database. Reading this article might help.

Database structure

To get started, MySQL's structure is broad. So at some point in time, you may get confused as to what level we effectively need to convert a database to an Excel workbook.

In MySQL, the database structure has three levels. The top (first) level is the database server name. The second level is the database name. The third level holds the database tables. We'll take each level one at a time.

Top Level: Database server name

This is commonly called the database hostname, like localhost or mydatabase.db.343432.databaseresources.com

This is a computer that contains the database. If this computer is offline, you cannot access your database. Most of the time, you can find this level in your hosting company's computer, or even in your own computer if you use XAMPP localhost.

This hostname has an equivalent IP address, which can be determined by going into Start -> Run -> and then typing CMD; after that, in the DOS prompt type this (Windows XP system):

C:>ping mydatabase.db.343432.databaseresources.com

Now press the enter key, and you can find the IP address in the ping results. Of course, you cannot convert any of the information in this stage to an Excel workbook.

Second level: database name

A database is a collection of tables, analogous to an Excel Workbook, which is a collection of worksheets. In MySQL, the database can be identified by its name; with hosting companies, this is popularly known as the "database name." It is possible to have more than one database in a single database server; in fact, it is a common practice.

A MySQL database is commonly accessed by open source software called "phpmyadmin." Below is a screen shot of databases in phpmyadmin:

In the screen shot above, there are five databases which can found in the left portion of phpmyadmin. At the database level, one can export to a Microsoft Excel workbook by following this very simple procedure:

Step 1: After logging in to phpmyadmin, you are at the second level (database level); in the navigation above, click "Export."

Step 2: Under the Export section, click "Select all." This will select all databases; otherwise, select only the database you need to export.

Step 3: In the export file type, select "Microsoft Excel 2000."

Step 4. Check "Put field names in the first row."

Step 5. Check "Save as file."

Step 6. Click "Go."

Depending the number and size of the exported database, there are times when an error may occur, typically when you are about to open an Excel workbook that contains the exported contents of the MySQL databases.

See example error below:

The error is obvious; the exported data (of the database and associated tables) are all cramped into a single worksheet. If you have exported several databases, it would be very hard to interpret the information found in the exported workbook.

Even if you exported a single database, say a single WordPress MySQL database, it will still return the above error. So what is the solution? It is recommended that you go to the third level.

Third level: database tables

Since exporting to a Microsoft Excel workbook at the database level can result in cramped data in a single MS Excel worksheet, we will go deeper -- to the database table level -- in exporting the data.

When you click on any of those database names, say "WordPress," the tables are shown, like the one below:

This is a WordPress database with table name like "wp_comments," "wp_links," etc. A MySQL table is an exact replica of an Excel worksheet, except that they use different extensions (.sql and .xls). While exporting databases from MySQL yields  cramped tables and data, MySQL tables are specific, which makes them ideal to use for exporting data.

For example, wp_post table is a table that consists of the posted content of the WordPress blog; another table, wp_comments, is the table in which the comments of readers can be found. This specificity gives an advantage and the ideal situation for exporting data.

In the e-commerce world, a MySQL database is used with osCommerce, in which you can find customers' data, reviews, data for which tables are specific to each other, making it very easy and convenient to do an Excel analysis.

Therefore the best way to export MySQL into an Excel workbook is to actually export those tables to an Excel spreadsheet. A single table corresponds to a single Excel worksheet; this is the ideal situation, where a table can be stored and analyzed.

For example, we would like to export the wp_post table. To do this, go through the following steps:

• Click on the table name: wp_post (shown in the screen shot above).
  
• phpmyadmin will then load the table data.
  
• Click "Export" (the same location in the navigation as we discussed earlier).
  
• The Export menu will be shown. Configure it as shown the screen shot below:

Under "Export" you need to check "Microsoft Excel 2000." You also need to check "Put field names in the first row," and finally check "Save as file." When everything has been set, click "Go" and save it to your desktop.

Now open it, and you will see the MS Excel version of your MySQL table. The only down side with doing this is that, if you have a lot of MySQL tables to analyze, it will take a while to export them all one at a time. Anyway, a human can only effectively analyze one thing at a time, so this down side is not a serious issue.

You might have a question about working directly with a MySQL file, Say you have an .sql extension saved in your computer; how can you convert it to Microsoft Excel? The best way to work this, if you have phpmyadmin installed in your local computer, you can import the .sql extension as a database, and then start implementing the techniques discussed in this article.