MySQL load NULL values from CSV data
Solution 1:
This will do what you want. It reads the fourth field into a local variable, and then sets the actual field value to NULL, if the local variable ends up containing an empty string:
LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(one, two, three, @vfour, five)
SET four = NULLIF(@vfour,'')
;
If they're all possibly empty, then you'd read them all into variables and have multiple SET statements, like this:
LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(@vone, @vtwo, @vthree, @vfour, @vfive)
SET
one = NULLIF(@vone,''),
two = NULLIF(@vtwo,''),
three = NULLIF(@vthree,''),
four = NULLIF(@vfour,'')
;
Solution 2:
MySQL manual says:
When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances.
So you need to replace the blanks with \N like this:
1,2,3,4,5
1,2,3,\N,5
1,2,3
Solution 3:
The behaviour is different depending upon the database configuration. In the strict mode this would throw an error else a warning. Following query may be used for identifying the database configuration.
mysql> show variables like 'sql_mode';
Solution 4:
Preprocess your input CSV to replace blank entries with \N.
Attempt at a regex: s/,,/,\n,/g and s/,$/,\N/g
Good luck.