mysql loads newline from flat file

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

Re: mysql loads newline from flat file

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