I have a CSV file that I want to load into a MySQL table using the following command:
LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE items CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (field1, field2, field3, field4, field5);
The problem I face is that the csv file is not properly formatted because some fields are not enclosed by double quotes ("") and have also new lines. e.g.: (third line)
"field1","field2","field3","field4","field5" "aaaaa","bbbbb","ccccc","ddddd","eeeeee" aaaa aaaa,bbbbbbbb bbbbb,"ccccc","dddddd","eeeee"
When I import the csv file into MySQL, newlines inside field contents are interpreted as a line termination.
So... how can I sort it out? Regex? Some CSV editor (I tried CSVed with no luck)? Thank you.