[mysql] MySQL load NULL values from CSV data

I have a file that can contain from 3 to 4 columns of numerical values which are separated by comma. Empty fields are defined with the exception when they are at the end of the row:

1,2,3,4,5
1,2,3,,5
1,2,3

The following table was created in MySQL:

+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| one   | int(1) | YES  |     | NULL    |       | 
| two   | int(1) | YES  |     | NULL    |       | 
| three | int(1) | YES  |     | NULL    |       | 
| four  | int(1) | YES  |     | NULL    |       | 
| five  | int(1) | YES  |     | NULL    |       | 
+-------+--------+------+-----+---------+-------+

I am trying to load the data using MySQL LOAD command:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS 
TERMINATED BY "," LINES TERMINATED BY "\n";

The resulting table:

+------+------+-------+------+------+
| one  | two  | three | four | five |
+------+------+-------+------+------+
|    1 |    2 |     3 |    4 |    5 | 
|    1 |    2 |     3 |    0 |    5 | 
|    1 |    2 |     3 | NULL | NULL | 
+------+------+-------+------+------+

The problem lies with the fact that when a field is empty in the raw data and is not defined, MySQL for some reason does not use the columns default value (which is NULL) and uses zero. NULL is used correctly when the field is missing alltogether.

Unfortunately, I have to be able to distinguish between NULL and 0 at this stage so any help would be appreciated.

Thanks S.

edit

The output of SHOW WARNINGS:

+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'four' at row 2 | 
| Warning | 1261 | Row 3 doesn't contain data for all columns             | 
| Warning | 1261 | Row 3 doesn't contain data for all columns             | 
+---------+------+--------------------------------------------------------+

show variables

Show variables like "`secure_file_priv`";

Note: keep your csv file in location given by the above command.

create table assessments (course_code varchar(5),batch_code varchar(7),id_assessment int, assessment_type varchar(10), date int , weight int);

Note: here the 'date' column has some blank values in the csv file.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/assessments.csv' 
INTO TABLE assessments
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(course_code,batch_code,id_assessment,assessment_type,@date,weight)
SET date = IF(@date = '', NULL, @date);

Preprocess your input CSV to replace blank entries with \N.

Attempt at a regex: s/,,/,\n,/g and s/,$/,\N/g

Good luck.

(variable1, @variable2, ..) SET variable2 = nullif(@variable2, '' or ' ') >> you can put any condition

Converted the input file to include \N for the blank column data using the below sed command in UNix terminal:

sed -i 's/,,/,\\N,/g' $file_name

and then use LOAD DATA INFILE command to load to mysql

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

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