mysql - 如何将CSV文件导入MySQL表(How to import CSV file to MySQL table)

Question

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.

(我有一个来自客户端的非标准化事件日志CSV，我试图将其加载到MySQL表中，以便可以将其重构为合理的格式。)

I created a table called 'CSVImport' that has one field for every column of the CSV file.

(我创建了一个名为“ CSVImport”的表，该表的CSV文件的每一列都有一个字段。)

The CSV contains 99 columns , so this was a hard enough task in itself:

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

(我认为问题在于文本内容包含多行，而MySQL正在解析文件，好像每一行都对应一个databazse行。)

I can load the file into OpenOffice without a problem.

(我可以毫无问题地将文件加载到OpenOffice中。)

The clientdata.csv file contains 2593 lines, and 570 records.

(clientdata.csv文件包含2593行和570条记录。)

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

(如有疑问，请阅读手册： 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语句中添加了一些信息，表明OpenOffice足够智能，可以推断出它，现在它可以加载正确数量的记录：)

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '
'
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.

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

  ask by Iain Samuel McLean Elder translate from so

Answer 1

Use mysqlimport to 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')