mysql - 使用 LOAD DATA INFILE 将 csv 上传到 mysql 表中

Question

我正在使用 LOAD DATA INFILE 将 .csv 文件上传到表格中。

这是我在数据库中创建的表:

CREATE TABLE expenses (entry_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entry_id), 
ss_id INT, user_id INT, cost FLOAT, context VARCHAR(100), date_created DATE);

这是我尝试上传的一些示例数据(有些行的每一列都有数据，有些缺少日期列):

1,1,20,Sandwiches after hike,1,1,45,Dinner at Yama,1,2,40,Dinner at Murphys,1,1,40.81,Dinner at Yama,1,2,1294.76,Flight to Taiwan,1/17/20111,2,118.78,Grand Hyatt @ Seoul,1/22/20111,1,268.12,Seoul cash withdrawal,1/8/2011

Here is the LOAD DATA command which I can't get to work:

LOAD DATA INFILE '/tmp/expense_upload.csv'
INTO TABLE expenses (ss_id, user_id, cost, context, date)
;

此命令完成，将正确数量的行上传到表中，但每个字段都是 NULL。每当我尝试添加 FIELDS ENCLOSED BY ',' 或 LINES TERMINATED BY '\r\n' 时，我都会收到语法错误。

其他需要注意的事项:csv 是在 MS Excel 中创建的。

如果有人有提示或可以指出正确的方向，将不胜感激!

Answer 1

首先我将 FLOAT 更改为 DECIMAL for cost

CREATE TABLE expenses 
(
  entry_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  ss_id INT, 
  user_id INT, 
  cost DECIMAL(19,2), -- use DECIMAL instead of FLOAT
  context VARCHAR(100), 
  date_created DATE
);

现在试试这个

LOAD DATA INFILE '/tmp/sampledata.csv' 
INTO TABLE expenses  
    FIELDS TERMINATED BY ',' 
           OPTIONALLY ENCLOSED BY '"'
    LINES  TERMINATED BY '\n' -- or \r\n
(ss_id, user_id, cost, context, @date_created)
SET date_created = IF(CHAR_LENGTH(TRIM(@date_created)) > 0, 
                      STR_TO_DATE(TRIM(@date_created), '%m/%d/%Y'), 
                      NULL);

id 的作用:

1. 它使用正确的语法来指定字段和列终止符
2. 由于文件中的日期值格式不正确，它首先读取一个值到用户/ session 变量，然后如果它不为空，则将其转换为日期，否则分配 NULL .后者可防止您获得零日期 0000-00-00。