使用LOAD DATA INFILE报表问题导入CSV [英] Importing CSV using LOAD DATA INFILE quote problem

问题描述

我尝试将我从Excel导出的CSV文件加载到我的数据库中，无论我尝试什么，我似乎无法获得正确的格式。

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.

这里是SQL：

LOAD DATA INFILE 'path/file.csv'
INTO TABLE tbl_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
(column1, column2, column3); 

这很好，但是当一行的结束（第3列）结束在一个引号时遇到麻烦，例如：

This works fine but then I run into trouble when the end of a line (column 3) ends in a quote. For example:

实际值：这些是报价

在CSV中：这些是报价

会发生什么任何关于如何解决这个问题的想法？

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?

推荐答案

嗯，我试图复制这个问题，但不能。我的数据在哪里不同？你能提供样本数据来复制吗？这是我做的：

Hmm. I tried to duplicate this problem but can't. Where does my data differ from yours? Can you provide sample data to duplicate this? Here's 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)

还要注意，如果你在Windows平台上工作，你可能需要使用

LINES TERMINATED BY'\r\\\
'。

Also note that if you're working on a Windows platform you might need to use
LINES TERMINATED BY '\r\n' instead.

这篇关于使用LOAD DATA INFILE报表问题导入CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！