Learning MySQL

Loading Data from Comma-Delimited Files

Databases are sometimes an afterthought. In fact, a staggeringly large amount of time spent by IT professionals is devoted to reformatting data from one application to suit another. It’s very common, for example, to store data using a spreadsheet program such as Microsoft Excel or OpenOffice Calc, only to realize later—when you’re swamped with data—that a relational database would have been a better choice. Most spreadsheet programs allow you to export data as rows of comma-separated values (CSV), often also referred to as comma-delimited format (CDF). You can then import the data with a little effort into MySQL.

If you need to import large numbers of spreadsheet files, you could use the xls2csv script (http://search.cpan.org/~ken/xls2csv) to automate the conversion from the Excel spreadsheet files to text files of comma-separated values.

If you’re not using a spreadsheet program, you can still often use tools such as sed and awk to convert text data into a CSV format suitable for import by MySQL. This section shows you the basics of how to import CSV data into MySQL.

Figure 8-1. List of Australian academics stored in a spreadsheet file

Let’s work through an example. We have a list of Australian academics with their university affiliation that we want to store in a database. At present, it’s stored in a spreadsheet workbook file named academics.xls ...