Database Reference
In-Depth Information
The results here are limited to two rows, but you can removethe
LIMIT
clause to see all
of the rows. There should be 11 in all. These two rows relate to the two records in the ex-
cerpt from the
Clements-Checklist-6.9-final.csv
file shown earlier in this chapter. Notice
that data isn't getting into the correct columns. To determine where things are going awry,
let's look closely at the record for the second row:
6707
,
new species
,
"Robb et al. (2013) describe a new species of owl,
Omani Owl (Strix omanensis),
from the Arabian Peninsula
,
with range ""central Al Hajar mountains
,
northern Oman"". Position Omani Owl immediately following
Hume's Owl (Strix butleri).",
species
,
Strix omanensis
,
Omani Owl,
"central Al Hajar mountains,
northern Oman",
Strigiformes,
Strigidae (Owls),
,,,,addition (2014),,,,,
The text that was inserted in the columns is shown in boldface here. It seems that MySQL
was confused by the commas contained within some of the fields. This isbecause the
LOAD DATA INFILE
we executed included a
FIELDS
clause that stipulated that they
are terminated by a comma. The result is that text from fields containing commas is being
cut into pieces and inserted into the subsequent columns. We can fix this problem by
adding more parameters to the
FIELDS
clause.
Let's delete the data in the
clements_list_import
table. This is one of the advant-
ages of using a temporary table as we have done: we can delete everything and start anew.
Then we'll reload the data. Enter the following two SQL statements on your server:
DELETE FROM rookery.clements_list_import;
LOAD DATA INFILE '
/tmp/Clements-Checklist-6.9-final.csv
'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;
The first SQL statement deletes all of the data in
clements_list_import
so that we
may start with an empty table. The second SQL statement is the same as the previous
LOAD DATA INFILE
, except that we've added the
ENCLOSED BY
subclause to the
