如何将CSV文件导入MySQL表中？

Question

我有一个来自客户的未经规范化的事件日历CSV文件，我试图将其加载到MySQL表中，以便可以重构为合理的格式。我创建了一个名为'CSVImport'的表，该表每列都对应CSV文件中的一列。CSV文件包含99列，因此这本身就是一项艰巨的任务：

CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);

表中没有任何限制，所有字段都包含VARCHAR(256)值，除了包含计数（表示为INT）、是/否（表示为BIT）、价格（表示为DECIMAL）和文本段落（表示为TEXT）的列。

我尝试将数据加载到文件中：

LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023  Deleted: 0  Skipped: 0  Warnings: 198256
SELECT * FROM CSVImport;
| NULL             | NULL        | NULL           | NULL | NULL               | 
...

整个表格都被填满了 NULL。

我认为问题在于文本块包含多行，而 MySQL 解析文件时会将每个新行视为一个数据库行。我可以毫无问题地将文件加载到 OpenOffice 中。

clientdata.csv 文件包含 2593 行和 570 条记录。第一行包含列名。我认为它是逗号分隔的，文本显然用双引号括起来。

更新：

当不确定时，阅读手册：http://dev.mysql.com/doc/refman/5.0/en/load-data.html 我向 LOAD DATA 语句添加了一些信息，OpenOffice 足够智能以推断出正确的记录数：

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

但仍然有很多完全NULL的记录，而且没有加载的数据似乎在正确的位置。

- Iain Samuel McLean Elder

---

如果你使用的是OSX操作系统，那么Sequel Pro拥有一个非常棒的导入工具，并且它是免费的；-) - Merrick

我觉得很惊奇的是原帖作者比其他人回答他自己的问题做得更好......我不知道为什么许多人都很愿意在已有SQL命令可以通过编程实现时提供软件推荐，因为通过UI来操作是纯手动的。对我而言，编程意味着我可以设置脚本来按时间戳自动导入文件，而基于UI则完全需要手动操作。 - Chris Cirefice

@ChrisCirefice：我认为被接受的答案解释得很好。他需要一些帮助手动创建“加载数据”命令，这可以通过图形程序来实现。一旦图形程序创建了“加载数据”命令，他就可以在编程中重复使用它。 - AlexC

@Merrick 这在 OSX 上运行得很好。 - weaveoftheride

@ChrisCirefice的脚本非常适合重复事件；但GUI更适合一次性的事情，因为你不必为了做一件事情而弄清所有的奥秘。 - Stephen R

Answer 1

使用mysqlimport将表格加载到数据库中：

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --local -u root \
            -p Database \
             TableName.csv

我在http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/找到了它。

要将分隔符设置为制表符，请使用--fields-terminated-by='\t'。

- michalzuber

---

mysqlimport 在后台使用 LOAD DATA INFILE...，所以它们基本上是相同的。 - Mladen Jablanović

和LOAD DATA INFILE一样，使用mysqlimport之前需要先创建表。 - Marcus Downing

@ninjabber 根据文档所述，_mysqlimport客户端提供了一个命令行界面来执行LOAD DATA INFILE_，因此理论上不可能比LOAD DATA INFILE提供任何性能优势。 - Mladen Jablanović

还需要--fields-optionally-enclosed-by=\"和--fields-escaped-by=\\。 - chaintng

应该是正确的答案。讨论了类似于命令行界面而不是图形用户界面的问题。 - cjones

Answer 2

你面临的核心问题似乎是将CSV文件中的列与表中的列匹配。

许多图形化的MySQL客户端都有非常好用的导入对话框来解决这种问题。

我最喜欢用的是基于Windows系统的HeidiSQL。它提供了一个图形界面来构建LOAD DATA命令，你以后还可以编程地重复使用它。

截图：“导入文本文件”对话框

要打开“导入文本文件”对话框，请转到工具 > 导入CSV文件：

- Pekka

---

适用于Mac OSX的数据库管理工具是Sequel Pro。 - Agi Hammerthief

我刚试过了，它要求我先创建表格，而不是使用列名。 - Dominique

在继续之前，您需要选择一张桌子……而整个意义就在于不必制作这张桌子…… - Dominique

请注意，在Linux上，HeidiSQL在Wine环境下运行非常好。 - AlejandroVD

@Paul，这个答案的关键是GUI工具可以使匹配导入列和表列更容易。 - Pekka

Answer 3

我在phpmyadmin的sql窗口中导入了200多行数据，最简单的方法是以下命令：

我有一个简单的国家表，包含两列：CountryId和CountryName。

这里是.csv数据

以下是命令：

LOAD DATA INFILE 'c:/country.csv' 
INTO TABLE country 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

记住一件事，不要出现在第二栏中，否则您的导入将停止。

- DareDevil

---

在处理TSV文件时，将逗号替换为制表符'\t'，双引号替换为空格''，如果没有标题，则去掉最后一行。（希望搜索爬虫能索引此内容）。 - Bleeding Fingers

如果这是一个本地文件，你可能需要使用LOAD DATA LOCAL INFILE。如果出现错误1148“不允许使用该命令”，你可以在命令行上运行mysql并加上--local-infile来启用它。 - Big McLargeHuge

我收到了错误信息：ERROR 1045 (28000)：用户'user'@'%'被拒绝访问（使用密码：YES）。 - ARUNBALAN NV

我遇到了这个错误：MySQL服务器正在使用--secure-file-priv选项运行，因此无法执行此语句。 - Agniswar Bakshi

完美的答案就是使用 LOAD DATA LOCAL INFILE 运行它，一切都会很好。 - x-magix

Answer 4

我使用此方法在0.046秒内导入了100K条记录（约5MB）。

以下是操作步骤：

LOAD DATA LOCAL INFILE  
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

如果有多个字段，包含最后一行非常重要，通常情况下会跳过最后一个字段（MySQL 5.6.17）

LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

假设您的字段的第一行为标题，那么您可能还想包括此行

IGNORE 1 ROWS

如果您的文件有标题行，则会呈现如下所示。

LOAD DATA LOCAL INFILE  
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);

- Fahad

---

我导入了16000行和48列。谢谢，伙计。 - Renjith V R

这是最佳解决方案。对于任何好奇这有多快的人：我在一个每月20美元的AWS Lightsail实例上，使用内置的MySQL服务器（而不是高性能RDS），在不到14秒的时间内导入了320万行数据。太棒了！ - master_gracey

在这一行中，(field_1,field_2,field_3); 它是指 .csv 文件或表格中的列列表吗？即，这些字段是什么？ - tera_789

@tera_789 你 CSV 文件中的那些很可能与你的表匹配。 - Fahad

Answer 5

phpMyAdmin可以处理CSV导入。以下是步骤：

1. 准备CSV文件，使其具有与MySQL表字段相同的顺序。

2. 从CSV中删除标题行（如果有），只留下数据。

3. 进入phpMyAdmin接口。

4. 在左侧菜单中选择表。

5. 点击顶部的导入按钮。

6. 浏览到CSV文件。

7. 选择"使用LOAD DATA的CSV"选项。

8. 在“字段结束符”中输入“，”。

9. 以与数据库表中的顺序相同的顺序输入列名称。

10. 点击“执行”按钮即可完成操作。

这是我为将来使用而准备的注释，并在此分享，以便其他人受益。

- Farhan

---

这很好也很简单。我更喜欢通过SQL创建表格和列（所以跳过第9步），并通过导入CSV插入数据。不要忘记为任何自增字段/列在CSV中设置“NULL”。 - k_rollo

请注意，当CSV文件中包含Cyrillic字符时，phpMyAdmin无论您告诉它使用utf-8编码，都会表现得非常糟糕。 - Fran Marzoa

不要忘记在导入大型CSV文件时更改导入大小。顺便说一句，对于大型CSV文件来说这不是一个好的选择。 - Avi

这需要先将 .csv 文件下载到 Web 客户端，因为您无法浏览本地 csv 文件。 - mckenzm

是的，您可以这样做。您需要勾选标记为“本地关键字”的复选框，并且可以使用本地CSV文件。 - chrisfs

我在使用SQL命令时遇到了“LOAD DATA LOCAL INFILE forbidden”错误，一直苦苦挣扎。这种方法解决了我的问题。简单明了，直截了当。 - Ranjul Arumadi

Answer 6

如果您正在使用MySQL Workbench（目前版本为6.3），您可以通过以下步骤完成：

1. 右键单击"Tables";
2. 选择"Table Data Import Wizard";
3. 选择您的csv文件并按照说明操作（也可以使用JSON）； 好处是您可以基于要导入的csv文件创建一个新表，或者将数据加载到现有表中。

- Vitaliy Pak

---

+1。我使用了这个方法，因为mysql在使用“LOAD DATA INFILE”和“mysqlimport”时出现了错误（“此版本的mysql不支持此功能”）。 - clamentjohn

这种方法可以运行，但速度有点慢。我本以为使用这个功能会构建一个大型的INSERT查询并尝试一次性完成所有操作，但实际上看起来是每行都运行一次INSERT。 - DaveTheMinion

这个方法救了我。我在使用LOAD DATA时遇到了很多错误，但是时间紧迫。如果任何读者在使用LOAD DATA时遇到问题，我强烈推荐使用这个方法。 - Alexander Santos

使用Workbench 8.0.22，这个功能远非稳定。经常崩溃、导入0行或者只是冻结。我已经尝试了Our World in Data的COVID-19 CSV文件：https://github.com/owid/covid-19-data/tree/master/public/data - urig

Answer 7

您可以通过在 LOAD DATA 语句中列出列来解决此问题。 根据手册：

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

所以在你的情况下，你需要按照它们在CSV文件中出现的顺序列出99个列。

- lafncow

Answer 8

试试这个，对我有用

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

这里的“IGNORE 1 ROWS”忽略了第一行，该行包含字段名称。请注意，对于文件名，您必须键入文件的绝对路径。

- David

---

这是最好的答案。当一个单一的 SQL 命令可以完成时，为什么要使用另一个工具？ - sdgfsdh

你知道在将文件加载到运行在服务器上的MySQL时如何使其工作吗？它会提示我文件访问被拒绝（密码）。在哪里输入CSV文件位置的密码？ - Baktaawar

Answer 9

我看到了一些奇怪的东西。你在转义和包含字符时使用了相同的字符。因此，当引擎遇到 '"' 时，它不知道该怎么做，我认为这就是为什么似乎什么都没有放在正确位置的原因。

我认为如果你删除转义行，应该会运行得很好。像这样：

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

除非你手动、可视地分析CSV并找出哪个字符用于转义，有时是'\'。但如果你没有它，请不要使用它。

- Juan

Answer 10

MySQL命令行在导入时容易出现太多问题。以下是如何操作：

• 使用Excel编辑标题名称，确保没有空格
• 保存为.csv文件
• 使用免费的Navicat Lite SQL浏览器进行导入，并自动创建一个新表（给它一个名称）
• 打开新表，插入一个自增主键列ID
• 按需要更改列的类型
• 完成！

- user1464271