The CSV file csvfile.csv:

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

So I load the CSV with following command:

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,'');

I have also tried setting default of comment as '' and rerunning it the command and got the error ``ERROR 1261 (01000): Row 4 doesn't contain data for all columns`.

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);

How can I just import the database and let the value be "NULL" if there is no data in that cell?

CodePudding user response：

When changing the LOAD FILE to:

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,'');

I did get the following results:

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]>