如何将 CSV 文件导入 MySQL 表？

How do I import CSV file into a MySQL table?

提问by Iain Samuel McLean Elder

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column of the CSV file. The CSV contains 99 columns , so this was a hard enough task in itself:

我有一个来自客户端的非规范化事件日记 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);

No constraints are on the table, and all the fields hold VARCHAR(256) values, except the columns which contain counts (represented by INT), yes/no (represented by BIT), prices (represented by DECIMAL), and text blurbs (represented by TEXT).

表上没有约束，所有字段都包含 VARCHAR(256) 值，除了包含计数（由 INT 表示）、是/否（由 BIT 表示）、价格（由 DECIMAL 表示）和文本简介（由 TEXT 表示）。

I tried to load data into the file:

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

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               | 
...

The whole table is filled with NULL.

整张桌子都摆满了NULL。

I think the problem is that the text blurbs contain more than one line, and MySQL is parsing the file as if each new line would correspond to one databazse row. I can load the file into OpenOffice without a problem.

我认为问题在于文本简介包含不止一行，并且 MySQL 正在解析文件，好像每个新行都对应于一个数据库行。我可以毫无问题地将文件加载到 OpenOffice 中。

The clientdata.csv file contains 2593 lines, and 570 records. The first line contains column names. I think it is comma delimited, and text is apparently delimited with doublequote.

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

UPDATE:

更新：

When in doubt, read the manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

如有疑问，请阅读手册：http: //dev.mysql.com/doc/refman/5.0/en/load-data.html

I added some information to the LOAD DATAstatement that OpenOffice was smart enough to infer, and now it loads the correct number of records:

我在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;

But still there are lots of completely NULLrecords, and none of the data that got loaded seems to be in the right place.

但是仍然有很多完整的NULL记录，并且加载的数据似乎都没有放在正确的位置。

采纳答案by Pekka

The core of your problem seems to be matching the columns in the CSV file to those in the table.

您问题的核心似乎是将 CSV 文件中的列与表中的列进行匹配。

Many graphical mySQL clients have very nice import dialogs for this kind of thing.

许多图形化的 mySQL 客户端为这种事情提供了非常好的导入对话框。

My favourite for the job is Windows based HeidiSQL. It gives you a graphical interface to build the LOAD DATAcommand; you can re-use it programmatically later.

我最喜欢的工作是基于 Windows 的HeidiSQL。它为您提供了一个图形界面来构建LOAD DATA命令；您可以稍后以编程方式重新使用它。

Screenshot: "Import textfile" dialog

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

To open the Import textfile" dialog, go to Tools > Import CSV file:

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

回答by michalzuber

Use mysqlimportto load a table into the database:

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

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

I found it at http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/

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

To make the delimiter a tab, use --fields-terminated-by='\t'

要使分隔符成为制表符，请使用 --fields-terminated-by='\t'

回答by DareDevil

Simplest way which I have imported 200+ rows is below command in phpmyadmin sql window

我导入 200 多行的最简单方法是在 phpmyadmin sql 窗口中的命令下方

I have a simple table of country with two columns CountryId,CountryName

我有一个简单的国家表，有两列 CountryId,CountryName

here is .csv data

这是 .csv 数据

here is command:

这是命令：

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

Keep one thing in mind, never appear , in second column, otherwise your import will stop

请记住一件事，永远不要出现在第二列中，否则您的导入将停止

回答by Fahad

I know that the question is old, But I would like to sharethis

我知道这个问题很老，但我想分享这个

I Used this method to import more than 100K records (~5MB) in 0.046sec

我使用这种方法在0.046 秒内导入了超过 100K 的记录（~5MB）

Here's how you do it:

这是你如何做到的：

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);

It is very important to include the last line , if you have more than one field i.e normally it skips the last field (MySQL 5.6.17)

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

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

Then, assuming you have the first row as the titlefor your fields, you might want to include this line also

然后，假设您将第一行作为字段的标题，您可能还想包括这一行

IGNORE 1 ROWS

This is what it looks like if your file has a header row.

如果您的文件有标题行，这就是它的样子。

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);

回答by Farhan

phpMyAdmincan handle CSV import. Here are the steps:

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

1. Prepare the CSV file to have the fields in the same order as the MySQL table fields.

2. Remove the header row from the CSV (if any), so that only the data is in the file.

3. Go to the phpMyAdmin interface.

4. Select the table in the left menu.

5. Click the import button at the top.

6. Browse to the CSV file.

7. Select the option "CSV using LOAD DATA".

8. Enter "," in the "fields terminated by".

9. Enter the column names in the same order as they are in the database table.

10. Click the go button and you are done.

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

2. 从 CSV（如果有）中删除标题行，以便文件中只有数据。

3. 转到 phpMyAdmin 界面。

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

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

6. 浏览到 CSV 文件。

7. 选择选项“CSV using LOAD DATA”。

8. 在“字段终止于”中输入“，”。

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

10. 点击 go 按钮，你就完成了。

This is a note that I prepared for my future use, and sharing here if someone else can benefit.

这是我为将来使用而准备的笔记，如果其他人可以受益，请在此处分享。

回答by lafncow

You can fix this by listing the columns in you LOAD DATA statement. From the manual:

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

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

...so in your case you need to list the 99 columns in the order in which they appear in the csv file.

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

回答by David

Try this, it worked for me

试试这个，它对我有用

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

IGNORE 1 ROWS here ignores the first row which contains the fieldnames. Note that for the filename you must type the absolute path of the file.

此处的 IGNORE 1 ROWS 忽略包含字段名的第一行。请注意，对于文件名，您必须键入文件的绝对路径。

回答by user1464271

The mysql command line is prone to too many problems on import. Here is how you do it:

mysql 命令行在导入时容易出现太多问题。这是你如何做到的：

• use excel to edit the header names to have no spaces
• save as .csv
• use free Navicat Lite Sql Browser to import and auto create a new table (give it a name)
• open the new table insert a primary auto number column for ID
• change the type of the columns as desired.
• done!

• 使用excel编辑标题名称以没有空格
• 另存为 .csv
• 使用免费的 Navicat Lite Sql Browser 导入并自动创建一个新表（给它一个名字）
• 打开新表插入一个主要的自动编号列作为 ID
• 根据需要更改列的类型。
• 完毕！

回答by Vitaliy Pak

If you are using MySQL Workbench (currently 6.3 version) you can do this by:

如果您使用的是 MySQL Workbench（目前为 6.3 版本），您可以通过以下方式执行此操作：

1. Right click on "Tables";
2. Chose Table Data Import Wizard;
3. Chose your csv file and follow the instructions (JSON also could be used); The good thing is that you can create a new table based on the csv file you want to import or load data to an existing table

1. 右键单击“表格”；
2. 选择表数据导入向导；
3. 选择您的 csv 文件并按照说明进行操作（也可以使用 JSON）；好处是您可以根据要导入的 csv 文件创建新表或将数据加载到现有表

回答by Juan

I see something strange. You are using for ESCAPING the same character you use for ENCLOSING. So the engine does not know what to do when it founds a '"' and I think that is why nothing seems to be in the right place. I think that if you remove the line of ESCAPING, should run great. Like:

我看到一些奇怪的东西。您用于 ESCAPING 的字符与用于 ENCLOSING 的字符相同。所以当引擎找到一个 '"' 时，它不知道该怎么做，我认为这就是为什么似乎没有什么东西在正确的地方。我认为如果你删除 ESCAPING 的行，应该运行得很好。比如：

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

Unless you analyze (manually, visually, ... ) your CSV and find which character uses for escape. Sometimes is '\'. But if you do not have it, do not use it.

除非您分析（手动，视觉，...）您的 CSV 并找到用于转义的字符。有时是“\”。但是，如果您没有它，请不要使用它。