mysqlcsvmysql-loadfile

How can a CSV file with some empty cells be loaded into MySQL?

I am trying to import a CSV with some empty cells into MySQL database. I Could you please help me with that?

My last thread was closed since it was "associated with similar question". I tried the solution and it didnt work, the attempt on that is listed below.

I have a table created:

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

I have populated a csv with data and want to import it into the table enter image description here

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

enter image description here

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?

Solution

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