Importing and Exporting Data - MySQL - page 375

The following LOAD DATA statement indicates that the input file contains data 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, FIELDS must precede LINES . The
line and field termination indicators can contain multiple characters. For example, \r
\n indicates that lines are terminated by carriage return/linefeed pairs.
The LINES clause also has a STARTING BY subclause. It specifies the sequence to be strip‐
ped from each input record. (Everything up to the given sequence is stripped. If you
specify STARTING BY 'X' and a record begins with abcX , all four leading characters are
stripped.) Like TERMINATED BY , the sequence can have multiple characters. If TERMINAT
ED BY and STARTING BY both are present in the LINES clause, they can appear in any
order.
For mysqlimport , command options provide the format specifiers. 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" \
cookbook mytbl.txt
Option order doesn't matter for mysqlimport .
The FIELDS and LINES clauses understand hex notation to specify arbitrary format
characters, which is useful for loading datafiles that use binary format codes. Suppose
that a datafile has lines with Ctrl-A between fields and Ctrl-B at the end of lines. The
ASCII values for Ctrl-A and Ctrl-B are 1 and 2, so you represent them as 0x01 and 0x02 :
FIELDS TERMINATED BY 0x01 LINES TERMINATED BY 0x02
mysqlimport also understands hex constants for format specifiers. You may find this
capability helpful if you don't like remembering how to type escape sequences on the
command line or when it's necessary to use quotes around them. Tab is 0x09 , linefeed
is 0x0a , and carriage return is 0x0d . This command indicates that the datafile contains
tab-delimited lines terminated by CRLF pairs:
% mysqlimport --local --fields-terminated-by=0x09 \
--lines-terminated-by=0x0d0a cookbook mytbl.txt
When you import datafiles, don't assume that LOAD DATA (or mysqlimport ) knows more
than it does. Some LOAD DATA frustrations occur because people expect MySQL to know
more than it possibly can. Keep in mind that LOAD DATA has no idea at all about the
format of your datafile. It makes certain assumptions about the input structure, repre‐
sented as the default settings for the line and field terminators, and for the quote and
escape character settings. If your input differs from those assumptions, you must tell
MySQL so.