Doing More with MySQL - Learning MySQL - page 283

the “Save as type” field. If you're using OpenOffice or StarOffice, follow the same steps,
but choose “Text CSV (.csv)” for the “File type” field. When you save the file, you'll
find it has the same name as the workbook (in this case, academics ) but with the ex-
tension .csv .
If you open the file using a text editor (we discussed how to use a text editor in “Using
a Text Editor” in Chapter 2), you'll see the result: the file has one line per spreadsheet
row, with the value for each column separated by a comma. If you're on a non-Windows
platform, you may find each line terminated with a ^M , but don't worry about this; it's
an artifact of the origins of Windows. Data in this format is often referred to as DOS
format , and most software applications can handle it without problem. Here are a few
lines selected from academics.csv :
Abramson,David,Griffith University
Addie,Ron,University of Southern Queensland
Al-Qaimari,Ghassan,Royal Melbourne Institute of Technology
Allen,Greg,James Cook University
Allen,Robert,Swinburne University of Technology
Anderson,Gerry,University of Ballarat
Armarego,Jocelyn,Curtin University of Technology
Ashenden,Peter,University of Adelaide
Atiquzzaman,M,La Trobe University
Backhouse,Jenny,"University College, ADFA, UNSW"
If there are commas within values, the whole value is enclosed in quotes, as in the last
line shown here.
Let's import this data into MySQL. First, create the new academics database:
mysql> CREATE DATABASE academics;
and choose this as the active database:
mysql> USE academics;
Now, create the details table to store the data. This needs to handle three fields: the
surname, the given names, and the institution:
mysql> CREATE TABLE details (surname CHAR(40), given_names CHAR(40),
institution CHAR(40));
We've allocated 40 characters for each field.
Now that we've set up the database table, we can import the data from the file using
the LOAD DATA INFILE command:
mysql> LOAD DATA INFILE 'academics.csv' INTO TABLE details FIELDS TERMINATED BY ',';
If the academics.csv file isn't in the current directory, you'll need to specify the full path
—for example, /home/adam/academics.csv or C:\academics.csv . The MySQL server
must have permission to read this file; for example, if the server is running as the user
mysql on a Linux or Mac OS X system, the datafile must have its permissions set such
that this user can read it.