Specifying the Datafile Format
Problem
You have a datafile
that’s not in LOAD
DATA
’s default format.
Solution
Use FIELDS
and LINES
clauses to
tell LOAD
DATA
how to interpret
the file.
Discussion
By default, LOAD
DATA
assumes
that datafiles contain lines that are terminated by linefeeds
(newlines) and that data values within a line are separated by tabs.
The following statement does not specify anything about the format of
the datafile, so MySQL assumes the default format:
mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl;
To specify a file format explicitly, use a FIELDS
clause to describe the characteristics of fields within a line, and a
LINES
clause to specify the line-ending sequence.
The following LOAD
DATA
statement specifies that the datafile contains values separated by
colons and lines terminated by carriage returns:
mysql>LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
->FIELDS TERMINATED BY ':'
->LINES TERMINATED BY '\r';
Each clause follows the table name. If both are present, the
FIELDS
clause must precede the
LINES
clause. The line and field termination
indicators can contain multiple characters. For example,
\r\n
indicates that lines are terminated by
carriage return/linefeed pairs.
If you use
mysqlimport, command-line options
provide the format specifiers. mysqlimport
commands that correspond to the preceding two LOAD
DATA
statements look like this:
%mysqlimport --local cookbook mytbl.txt
%mysqlimport --local --fields-terminated-by=":" --lines-terminated-by="\r" ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.