Learning Mysql

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 extension .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 seethe 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 area 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, given_names CHAR,
institution CHAR(40));
We’ve allocated 40 characters for each field.
Now that we’ve setup 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.

Download 4.24 Mb.

Share with your friends: