MySQL NULL in last CSV column possible

Question

I want to import CSV files into MySQL 5.7 and would like to load NULL values.

I have read MySQL load NULL values from CSV data

If I use the same sample table and data it works fine. If I however try to import the following data:

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

Then the output is: Error Code: 1366. Incorrect integer value: '' for column 'five' at row 2

Table creation script:

create table moo(
one int not null,
two int not null,
three int null,
four int null,
five int null);

I am using the nullif expression for the nullable columns:

LOAD DATA INFILE '/test-moo.txt' INTO TABLE moo 
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
    (one, two, @vthree, @vfour, @vfive)
    SET three = nullif(@vthree,''),
    four = nullif(@vfour,''),
    five =nullif(@vfive,'');

Is this a bug or am I missing something? It only seems to go wrong with the last column.

Are you sure that the error doesn't come from the first line "1,2,3" should be "1,2,3,," — Daniel E., Dec 21 '18 at 15:58
Yes I am sure. Removing this line will change the error message to indicate that row 1 is faulty. What I just also found is that if I make line 2 the last line of the file, then it works. It seems that there is something going wrong with the end of line interpretation. — Ben Ootjers, Dec 21 '18 at 16:08
I tried it with MySQL-MariaBD (15.1 Distrib 10.1.37-MariaDB), and it worked. So maybe it's bug in your MySQL-Version. Or, more likely, maybe you have some invisible characters in your file. — Paflow, Dec 21 '18 at 16:35

score 1 · Accepted Answer · answered Jan 08 '19 at 15:18

I have found an answer to my question/problem. It seems that the line separator was not correctly. I am working on Windows environment, and so it should have been \r\n instead of \n.

So after correcting it became this, and processed correctly:

LOAD DATA INFILE '/test-moo.txt' INTO TABLE moo 
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
    (one, two, @vthree, @vfour, @vfive)
    SET three = nullif(@vthree,''),
    four = nullif(@vfour,''),
    five =nullif(@vfive,'');

MySQL NULL in last CSV column possible

1 Answers1