Geek Answers Handbook

Import CSV using LOAD DATA INFILE loading problem

I am trying to get this CSV file that I exported from excel loaded into my database and I can’t get the correct formatting 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 '\n' (column1, column2, column3);

This works fine, but then I ran into difficulties when the end of the line (column 3) ends with a quote. For instance:

Actual Value: These are "quotes"

CSV value: "These are ""quotes"""

What will happen is that I will receive an additional quote for this value in the database, as well as any additional lines, until I reach another quote in CSV. Any ideas on how to solve this problem?

+4
source share
1 answer

Hm. I tried to duplicate this problem but cannot. Where are my details different from yours? Can you provide duplicate data samples? Here is what I did:

 > cat /tmp/data.csv "aaaa","bbb ""ccc"" ddd",xxx xxx,yyy,"zzz ""ooo""" foo,bar,baz mysql> CREATE TABLE t2 (a varchar(20), b varchar(20), c varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> LOAD DATA INFILE '/tmp/data.csv' INTO TABLE t2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (a, b, c); Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t2; +------+---------------+-----------+ | a | b | c | +------+---------------+-----------+ | aaaa | bbb "ccc" ddd | xxx | | xxx | yyy | zzz "ooo" | | foo | bar | baz | +------+---------------+-----------+ 3 rows in set (0.00 sec)

Looks fine to me (?)

Also note that if you are running on a Windows platform, you may need to use LINES TERMINATED BY '\r\n' .

+9
source

More articles:


All Articles