MySQL 使用 LOAD DATA INFILE 引用问题导入 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7019506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing CSV using LOAD DATA INFILE quote problem
提问by Vanessa
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.
我试图将我从 excel 导出的这个 CSV 文件加载到我的数据库中，但无论我尝试什么，我似乎都无法获得正确的格式。
Here is the SQL:
这是 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 run into trouble when the end of a line (column 3) ends in a quote. For example:
这工作正常，但是当行尾（第 3 列）以引号结尾时，我遇到了麻烦。例如：
Actual value: These are "quotes"
实际价值： These are "quotes"
Value in CSV: "These are ""quotes"""
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?
发生的情况是，我将在数据库中获得该值的额外报价以及任何其他行，直到它到达 CSV 中的另一个报价。关于如何解决这个问题的任何想法？
回答by Casper
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)
Looks ok to me(?)
我觉得还行（？）
Also note that if you're working on a Windows platform you might need to useLINES TERMINATED BY '\r\n'instead.
另请注意，如果您在 Windows 平台上工作，则可能需要LINES TERMINATED BY '\r\n'改用。
