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 '
'

However, because 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 '
'
LINES TERMINATED BY '
'

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 '
';

The corresponding mysqlimport command is:

%mysqlimport --local --lines-terminated-by="
" cookbook mytbl.txt
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.31.22