問題描述
我有一個來自客戶端的非規(guī)范化事件日志 CSV，我試圖將其加載到 MySQL 表中，以便我可以重構(gòu)為合理的格式.我創(chuàng)建了一個名為CSVImport"的表，該表為 CSV 文件的每一列都有一個字段.CSV 包含 99 列，因此這本身就是一項艱巨的任務(wù):
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) 值，除了包含計數(shù)(用 INT 表示)、是/否(用 BIT 表示)、價格(用 DECIMAL 表示)和文字簡介(由 TEXT 表示).
我嘗試將數(shù)據(jù)加載到文件中:
LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;查詢 OK，2023 行受影響，65535 條警告(0.08 秒)記錄:2023 刪除:0 跳過:0 警告:198256選擇 * 從 CSVImport;|空 |空 |空 |空 |空 |...整個表被NULL填滿.
我認(rèn)為問題在于文本簡介包含不止一行，并且 MySQL 正在解析文件，好像每個新行都對應(yīng)于一個數(shù)據(jù)庫行.我可以毫無問題地將文件加載到 OpenOffice 中.
clientdata.csv 文件包含 2593 行和 570 條記錄.第一行包含列名.我認(rèn)為它是逗號分隔的，文本顯然是用雙引號分隔的.
更新:
如有疑問，請閱讀手冊:
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:
這篇關(guān)于如何將 CSV 文件導(dǎo)入 MySQL 表?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！