Recipe 10.6. 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