如何将CSV文件导入到MySQL表中? [英] How do I import CSV file into a MySQL table?
问题描述
我有一个来自客户端的非标准化事件日志CSV，我正在尝试将其加载到MySQL表中，以便可以将其重构为理智的格式.我创建了一个名为"CSVImport"的表，该表的CSV文件的每一列都有一个字段. CSV包含99列，因此，这本身就是一项艰巨的任务:
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);
表上没有约束，并且所有字段都包含VARCHAR(256)值，除了包含计数(以INT表示)，是/否(以BIT表示)，价格(以DECIMAL表示)和文字说明(由TEXT表示).
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 |
...
整个表都填充了NULL.
我认为问题在于文本blurbs包含多行，而MySQL正在解析文件，好像每一行都将对应于一个databazse行.我可以毫无问题地将文件加载到OpenOffice中.
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.
clientdata.csv文件包含2593行和570条记录.第一行包含列名.我认为它是用逗号分隔的，并且文本显然是用双引号分隔的.
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.
更新:
如有疑问，请阅读手册: http://dev.mysql.com/doc/refman/5.0/en/load-data.html
When in doubt, read the manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html
我在LOAD DATA语句中添加了一些信息，表明OpenOffice足够智能，可以推断出它，现在它可以加载正确数量的记录了:
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;
但是仍然有很多完全NULL条记录，而且似乎没有加载的数据都在正确的位置.
But still there are lots of completely NULL records, and none of the data that got loaded seems to be in the right place.
推荐答案
问题的核心似乎在于将CSV文件中的列与表中的列进行匹配.
The core of your problem seems to be matching the columns in the CSV file to those in the table.
许多图形化的mySQL客户端都具有用于此类事情的非常好的导入对话框.
Many graphical mySQL clients have very nice import dialogs for this kind of thing.
我最喜欢这份工作的是基于Windows的 HeidiSQL .它为您提供图形界面来构建LOAD DATA命令.您可以稍后以编程方式重新使用它.
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.
要打开导入文本文件"对话框，请转到Tools > Import CSV file:
To open the Import textfile" dialog, go to Tools > Import CSV file:
这篇关于如何将CSV文件导入到MySQL表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
