Database Reference
In-Depth Information
More Field and Line Definitions
Not all data textfiles will be constructed like the Cornell CSV data text file we used in the
examples so far in this chapter. Some files format the fields and lines differently. Let's load
a different data text file to learn about other ways to define lines andfields with the
LOAD
DATA INFILE
statement.
For the examples in this section, let's refer back to an earlier example (
Extracting Text
) in
which our marketing agency gave us a table in a dump file containing prospects for our
site. This time, let's assume the marketing agency gave us a data text file. The text file is
named
birdwatcher-prospects.csv
and contains a list of names and email addresses of
people who might want to be members of the Rookery site. You can download a copy of
this file from the
MySQL Resources site
. Here are the first few lines of that text file:
["prospect name"|"prospect email"|"prospect country"]
["Mr. Bogdan Kecman"|"bodgan\@kecman-birds.com"|"Serbia"]
["Ms. Sveta Smirnova"|"bettasveta\@gmail.com"|"Russia"]
["Mr. Collin Charles"|"callincollin\@gmail.com"|"Malaysia"]
["Ms. Sveta A. Smirnova"|"bettasveta\@gmail.com"|"Russia"]
The first line lists the name of the fields. Lines start with anopening bracket and end with a
closing bracket. Fields are enclosed within double quotes and separated by a vertical bar.
Theampersand is preceded with a backslash as an escape character, to indicate that the
character that follows it is a literal character. To import the data, we'll have to allow for all
of these details so that MySQL knows when a record starts and ends, when a field starts
and ends, and how characters are escaped.
Starting, Terminating, and Escaping
Before loading the
birdwatcher-prospects.csv
file, let's create a table in which to import its
contents. In addition to columns for each of the three fields in the data text file, we'll add
an incremental column as the primary key. Because email addresses are generally taken by
individuals, we'll make the column for theprospect's email address a
UNIQUE
key
column. Execute the following SQL statement to create this table:
CREATE TABLE
birdwatchers
.
birdwatcher_prospects_import
(
prospect_id
INT
AUTO_INCREMENT
KEY
,
prospect_name
VARCHAR
(
255
),
prospect_email
VARCHAR
(
255
)
UNIQUE
,
prospect_country
VARCHAR
(
255
));
That creates the import table. Let's load the data from the
birdwatcher-prospects.csv
file
into it. Execute the following SQL statement:
