如何将CSV文件导入MySQL表 [英] How to import CSV file to MySQL table

问题描述

我有一个非规范化的事件 - 日记CSV从客户端，我试图加载到MySQL表，以便我可以重构为一个正常的格式。我创建了一个名为CSVImport的表，其中为CSV文件的每一列都有一个字段。 CSV包含99列，所以这是一个很难的任务本身：

  CREATE TABLE'CSVImport' 
 ALTER TABLE CSVImport ADD COLUMN标题VARCHAR（256）; 
 ALTER TABLE CSVImport ADD COLUMN公司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表示），yes / no（由BIT表示），价格（由DECIMAL表示）和文本blurbs（由TEXT表示）。

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

  LOAD DATA INFILE'/home/paul/clientdata.csv'INTO TABLE CSVImport; 
查询OK，2023行受影响，65535条警告（0.08秒）
记录：2023已删除：0已跳过：0警告：198256 
 SELECT * FROM CSVImport; 
 | NULL | NULL | NULL | NULL | NULL | 
 ... 
  

整个表填充 NULL 。

我认为问题是文本blurbs包含多行，而MySQL正在解析文件，如果每个新行对应到一个数据行。我可以将文件加载到OpenOffice没有问题。

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

更新：

，请阅读手册：

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:

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

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.

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.

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.

UPDATE:

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

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

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 NULL records, and none of the data that got loaded seems to be in the right place.

解决方案

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

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

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

Screenshot: "Import textfile" dialog

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

这篇关于如何将CSV文件导入MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！