Doing More with MySQL - Learning MySQL - page 282

Figure 8-1. List of Australian academics stored in a spreadsheet file
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 spread-
sheet 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.
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 and has the format shown in Fig-
ure 8-1. You can see that the surname is stored in the first column, one or more given
names and initials in the second column, and their affiliation in the third column. This
example is formulated from a file that is publicly available at http://www.cs.jcu.edu.au/
acsadb/nameonly_db.html , and the workbook format example is available from the
book's web site.
Saving the academics.xls file as values with a comma or other character as a delimiter
is easy in most spreadsheet programs. In most versions of Microsoft Excel, you click
on the File menu, then select Save As, and then choose “CSV (Comma delimited)” for