10.7 Reading Files from Different Operating Systems
10.7.1 Problem
Different operating systems use different
line-ending sequences.
10.7.2 Solution
That's why
LOAD DATA has a
LINES TERMINATED
BY clause.
10.7.3 Discussion
The line-ending sequence used in a datafile typically is determined
by the system on which the file originates, not the system on which
you import it. Keep this in mind when loading a file that is obtained
from a different system.
Unix files normally have
lines terminated by linefeeds, which you can indicate in a
LOAD DATA statement like this:
LINES TERMINATED BY '\n'
However, because \n happens to be the default line
terminator for LOAD DATA, you
don't need to specify a LINES
TERMINATED BY clause in this
case unless you want to indicate explicitly what the line ending
sequence is.
Files created under
Mac OS or Windows usually have
lines ending in carriage returns or carriage return/linefeed pairs.
To handle these different kinds of line endings, use the appropriate
LINES TERMINATED
BY clause:
LINES TERMINATED BY '\r'
LINES TERMINATED BY '\r\n'
For example, to load a Windows file that contains tab-delimited
fields and lines ending with CRLF pairs, use this
LOAD DATA statement:
mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
-> LINES TERMINATED BY '\r\n';
The corresponding
mysqlimport command is:
% mysqlimport --local --lines-terminated-by="\r\n" cookbook mytbl.txt
|
