MySQL Forums
Forum List  »  CSV Storage Engine

CSV LOAD DATA Question: line term in quoted field
Posted by: douglas_reith
Date: May 01, 2006 10:03PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
CSV LOAD DATA Question: line term in quoted field
27219
May 01, 2006 10:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.