CSV LOAD DATA Question: line term in quoted field

[douglas_reith]

Hi there,
I have a windows generated CSV file which I have little or no control over and it has some fields which are optionally enclosed by quotes.

I use this command as per the instructions:

LOAD DATA LOCAL INFILE '/tmp/import/my_file.csv'
INTO TABLE import_my_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3);

This works well in the sense that if it finds a field that is quoted it ignores FIELD TERMINATORS (commas) within that field until it reaches the closing quote. However the difficulty I'm having is that it is not ignoring the LINE TERMINATORS within a quoted field.

For example (illustrative only):
col1, col2, col3\r\n
01,"this comma, is ignored",dougreith\r\n
02,"the comma, is fine but this windows carriage return\r\n
will cause problems",dougreith\r\n


I'm hoping somebody else may have come across this issue or just simply know of a neat work around.

thanks in advance.