代码之家 › 专栏 › 技术社区 › Clone

如何将包含一些空单元格的CSV文件加载到MySQL中?

mysql-loadfile csv mysql

Clone · 技术社区 · 4 年前

我正在尝试将带有一些空单元格的CSV导入MySQL数据库。你能帮我吗?

我的最后一个线程已关闭,因为它与 similar question “。我尝试了这个解决方案,但没有成功,下面列出了对它的尝试。

我创建了一个表:

CREATE TABLE citytable (
    id int(11) NOT NULL auto_increment,
    city varchar(100),
    number int(11) NOT NULL DEFAULT 1,
    comment varchar(100),
    primary key(id)
    );

我已经用数据填充了csv,并希望将其导入到表中

CSV文件 csvfile.csv :

id,city,number,comment
1,NY,1,Something
2,W,2,
3,C,1,Something
4,LA,1,

因此,我使用以下命令加载CSV:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvfile.csv' 
INTO TABLE citytable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id,city,number,@vcomment)
SET comment = NULLIF(@vcomment,'');

我也尝试过设置默认值 comment 像 '' 然后重新运行该命令,得到错误“error 1261(01000):第4行不包含所有列的数据”。

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvfile.csv' 
INTO TABLE citytable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id,city,number,comment);

如果单元格中没有数据,我如何导入数据库并让值为“NULL”?

1 回复 | 直到 4 年前

Luuk WHIP 4 年前

当换衣服的时候 LOAD FILE 致:

LOAD DATA INFILE 'D:/TEMP/csvfile.csv' 
INTO TABLE citytable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id,city,number,@vcomment)
SET comment = NULLIF(@vcomment,'');

我确实得到了以下结果:

MySQL [test]> LOAD DATA INFILE 'D:/TEMP/csvfile.csv'
    -> INTO TABLE citytable
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n'
    -> IGNORE 1 LINES
    -> (id,city,number,@vcomment)
    -> SET comment = NULLIF(@vcomment,'');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [test]> select * from citytable;
+----+------+--------+-----------+
| id | city | number | comment   |
+----+------+--------+-----------+
|  1 | NY   |      1 | Something |
|  2 | W    |      2 | NULL      |
|  3 | C    |      1 | Something |
|  4 | LA   |      1 | NULL      |
+----+------+--------+-----------+
4 rows in set (0.00 sec)

MySQL [test]>

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前