I'm trying to get this CSV file that I exported from excel loaded into my database and I can't seem to get the formatting correct no matter what I try.
Here is the SQL:
LOAD DATA INFILE 'path/file.csv'
INTO TABLE tbl_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
(column1, column2, column3);
This works fine but then I run into trouble when the end of a line (column 3) ends in a quote. For example:
Actual value: These are "quotes"
Value in CSV: "These are ""quotes"""
What happens is that I will get an extra quote on that value in the database and also any additional lines until it reaches another quote in the CSV. Any ideas on how to solve this?
See Question&Answers more detail:os