MySQL Cookbook, 2nd Edition

Reading Files from Different Operating Systems

Problem

Different operating systems use different line-ending sequences.

Solution

That’s why LOAD DATA has a LINES TERMINATED BY clause. Use it to your advantage.

Discussion

The line-ending sequence used in a datafile typically is determined by the system from which the file originated. 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.

If your system doesn’t use the Unix default (linefeed), you need to specify the line terminator explicitly. Files created under Mac OS X or Windows often have lines ending in carriage returns or carriage return/linefeed pairs, respectively. 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