The most common “database” format, especially for small- to medium-sized businesses, is the spreadsheet. The reason for this is fairly obvious — no special skills are required either for design or for data entry. Not only that, a spreadsheet may well be the best format for presenting and maintaining some kinds of information. If the file is not complicated, it's easy to get a quick overview of the data and sorting on a specific field is usually just a matter of clicking a column heading.

However, as the volume or complexity of information increases, this format becomes more and more cumbersome. Information becomes more difficult to retrieve and you run into the kinds of problems usually associated with flat-table databases — data duplication, for example.

This chapter deals with migrating a spreadsheet to a MySQL database. The solution presented here is operating system (OS) neutral; it works on Mac, Windows, or any Unix-like OS.

Excel is probably the most commonly used spreadsheet format but the procedure described here applies to any spreadsheet. The only requirement is that the spreadsheet data be exported as a text file so that it can be imported into MySQL.

To help facilitate things Query Browser, one of the open source MySQL GUI Tools, will be used. Creating database objects is made especially easy using the Table Editor, a feature of the Query Browser also common to other GUI Tools. By pointing and clicking you can quickly build a table without knowing anything about data definition language (DDL). Not only will the table editor help you work more quickly, it's also a good way to learn MySQL's implementation of SQL. Any alterations made to a table using the graphical interface are shown as SQL statements, making it easy to learn the appropriate SQL commands. We'll take advantage of this feature to document as we go.

The example spreadsheet that we'll be importing contains information about the accreditations of members of a professional association. It's not complicated so the process should be fairly easy to follow but at the same time it does highlight the major issues you might encounter and provides general guidelines for importing spreadsheets into MySQL.

The steps we'll take are as follows: