MySQL Error when importing CSV with empty fields

All we need is an easy explanation of the problem, so here it is.

I have CSV file with exported table, that has rows with fields that do not contain values. Columns are separated using “,” and if fields have no values they look like that: ,,,,,, I tried to load data using following code:

LOAD DATA INFILE "C:/table.csv"
INTO TABLE target_table 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Columns in target table (to be loaded with data from this CSV file) are nullable, but server returns error:

"Incorrect integer value"

When I put NULL between commas in CSV file, server do not return any error. Is it possible to change some setting to have table loaded without changing the CSV file?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Something like this, checking every value for ”, and inserting NULL instead, may help. Change a,b,c,d for the actual name and number of rows on the table:

$ cat /tmp/test.csv 
test1, test2, test3, test4
,,,
1,2,3,4
,,,

mysql> LOAD DATA INFILE "/tmp/test.csv" INTO TABLE test.test  
          FIELDS TERMINATED BY ','  
          OPTIONALLY ENCLOSED BY '"' 
          LINES TERMINATED BY '\n' 
          IGNORE 1 ROWS 
       (@a, @b, @c, @d) 
       SET a = IF(@a = '', NULL, @a), 
           b = IF(@b = '', NULL, @b), 
           c = IF(@c = '', NULL, @c), 
           d = IF(@d = '', NULL, @d);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test.test;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
|    1 |    2 |    3 |    4 |
| NULL | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

Check the syntax for LOAD DATA for more details.

Method 2

I had ‘|’ field separators.
I opened csv in good editor like vim and substituted || with |\N|.
Yes Uppercase N. That’s what a tutorial said to use.
In vim:
:% s/||/|\\N|/gc

gc is ‘global” ‘confirm’.
In mine there were two blank fields together ie: |||
Vim would only do the first for some reason. So I had to do the command a second time which got the second.

Then mysql loaded the file.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply