mysql loads newline from flat file

[Jack]

Hi there MYSQL is loading a non printable character at the end of each
row, clearly its a newline or ENTER character.. I have tried
dos2unix.exe, a perl program that converts dos files to unix, etc.
But I dont think I cant remove newline from the file otherwise how
will mysql know what the record separator is ? here is my load
command:

LOAD DATA INFILE 'e:/ETL/john/mergedfile_CELL2.txt' INTO TABLE
mergedfile_CELL1b FIELDS TERMINATED BY 0x1C IGNORE 1 LINES;

How does one resolve this ?

Thank you,

Jack

[Jonathan]

Jack wrote:

Hi there MYSQL is loading a non printable character at the end of each
row, clearly its a newline or ENTER character.. I have tried
dos2unix.exe, a perl program that converts dos files to unix, etc.
But I dont think I cant remove newline from the file otherwise how
will mysql know what the record separator is ? here is my load
command:

LOAD DATA INFILE 'e:/ETL/john/mergedfile_CELL2.txt' INTO TABLE
mergedfile_CELL1b FIELDS TERMINATED BY 0x1C IGNORE 1 LINES;

How does one resolve this ?

Have you looked at the documentation of the LOAD DATA command? I believe
there is a query option to specify the line termination style, at least
there is for the record seperator. Usually for Unix/Linux it's only
'\n', but I believe it could be '\n\r' or '\r\n' for Windows (I'm not
sure if it is the first or the second option for Windows).

Jonathan