mysql load data from csv which contains line feed

Question

I am dumping some csv data into mysql with this query

LOAD DATA LOCAL INFILE 'path/LYRIC.csv' INTO TABLE LYRIC CHARACTER SET euckr FIELDS TERMINATED BY '|';

When I did this, I can see follow logs from console.

...
[2017-09-13 11:24:10] ' for column 'SONG_ID' at row 3
[2017-09-13 11:24:10] [01000][1261] Row 3 doesn't contain data for all columns
[2017-09-13 11:24:10] [01000][1261] Row 3 doesn't contain data for all columns
...

I think csv got some line feed as a column data so it breaks all parsing process.

A single record in csv looks like ...

000001|2014-11-17 18:10:00|2014-11-17 18:10:00|If I were your September     
I''d whisper a sunset to fly through    
And if I were your September    


|0|dba|asass|2014-11-17 18:10:00||||2014-11-17 18:10:00

So LOAD DATA pushes line 1 as a record and then try line 2 and so on, even if this is a single data.

How can I fix it? Should I request different type of the file to the client?

P.S. I am so new with this csv work.

Answer 1

Multiline fieds in csv should be surrounded with double quotes, like this:

000001|2014-11-17 18:10:00|2014-11-17 18:10:00|"If I were your September     
I''d whisper a sunset to fly through    
And if I were your September    


"|0|dba|asass|2014-11-17 18:10:00||||2014-11-17 18:10:00

And any double quote inside that field should be escaped with another double quote.

Of course, the parser has to support (and maybe be instructed to use) multiline fields.