Bulk Importing Data - Learning MySQL and MariaDB - page 391

LOAD DATA INFILE ' / tmp / birdwatcher - prospects . csv '
INTO TABLE birdwatchers.birdwatcher_prospects_import
FIELDS TERMINATED BY ' | ' ENCLOSED BY ' " ' ESCAPED BY ' \\ '
LINES STARTING BY ' [ ' TERMINATED BY ' ] \ r \ n '
IGNORE 1 LINES
( prospect_name , prospect_email , prospect_country );
Although this SQL statement is correct, if you loaded the birdwatcher-prospects.csv file,
it generated an error and no data was inserted into the table. We'll address that error in the
next section. Let's focus now on the subclauses ofthe FIELDS and LINES clause in-
cluded in the LOAD DATA INFILE statement here.
First, let's look at the FIELDS clause:
▪ The TERMINATED BY subclause says that fields end with a vertical bar. The last
field doesn't have one, but because we'll let the statement know it's the end of the
line, MySQL will then assume the last field has ended.
▪ The ENCLOSED BY subclause says that each field is positioned between double
quotes.
▪ The ESCAPED BY clause specified the character that's used to escape special
characters. The default is a backslash. So there's no need to include this subclause
for this data text file, but I wanted you to be aware that it exists.
Let's look now atthe LINES clause:
▪ The STARTING BY subclause specifies an opening bracket.
▪ The TERMINATED BY subclause specifies a closing bracket followed by a car-
riage return and a newline. Normally, a newline is sufficient. But this data text file
was created on a MS Windows computer with an application that ends lines this
way.
Replacing Data Versus Ignoring Errors
Let's address the errorgenerated by executing the LOAD DATA INFILE statement in
the previous section. The following error message appeared when that SQL statement was
run:
ERROR 1062: Duplicate entry '[e-mail]' for key
'prospect_email'
This error was caused because there are two identical email addresses for Sveta Smirnova
in the data text file and we stipulated that the prospect_email be unique. Because of
the error, the entire import was rolled back and no data was inserted.