with mysql load data infile I get "incorrect integer value"

Question

LOAD DATA INFILE 'thefile.csv' 
INTO TABLE word 
FIELDS TERMINATED BY ';'
IGNORE 1 LINES
SET id = NULL;

I am a bit lost of what to do here, I thought this query would work but it doesn't. I get the error about id, which is why I thought set id = NULL would make it good but no. The point of my id row (which is AUTO_INCREMENT) is that I don't need to specify the ID. so.. ehm. Any help would be greatly appreciated

Incorrect integer value: '' for column 'id' at row 1

My CSV file content:

id;meaning;japanese;kanji;kana;romaji;visible;featured;image;image_author;imgauthor_link;extra
;pants;パンツ;;パンツ;pantsu;;;;;;

Your question looks a lot like this one. http://stackoverflow.com/questions/6017032/how-to-load-data-infile-in-mysql-with-first-col-being-auto-increment — S. Miller, Jun 01 '16 at 01:55

score 2 · Accepted Answer · answered Jun 01 '16 at 02:59

2

You must specify all column names except the id column:

LOAD DATA INFILE 'thefile.csv' 
INTO TABLE word 
FIELDS TERMINATED BY ';'
IGNORE 1 LINES
(col1,col2,col3);

I assumed your table has (id,col1,col2,col3) columns.

answered Jun 01 '16 at 02:59

Mostafa Vatanpour

1,328
13
18

@Emil can you show which documentation and which part of it say that? Did you test my answer and it did not work? I previously tested it and it works. – Mostafa Vatanpour Nov 02 '19 at 15:16

Norbert · Answer 2 · 2016-06-01T20:41:49.590

You can use a two step load:

1) Load the data into a table;

2) Use INSERT INTO ... SELECT ... FROM to get your data in your table

Example:

CREATE TABLE baseData (
    All your column definitions, but not your id column
);

INSERT INTO (all your columns except the id column) SELECT * FROM baseData;

Alternative option:

Create your table with the id as last column, then the loading with LOAD DATA INFILE works

Borislav Gizdov · Answer 3 · 2023-06-06T11:54:15.280

0

In my case, this error occurred in other columns, not the primary key.

The solution was to replace any null values with \N instead of leaving them as ,, .

cat example.csv
1,foobar,1,0,1
2,barfoo,,,
3,foofoo,,,

You can use sed to replace empty values with \N null values.

cat example.csv | sed   -e ':a' -e 's/,,/,\\N,/g;  s/,$/,\\N/g; ta'
1,foobar,1,0,1
2,barfoo,\N,\N,\N
3,foofoo,\N,\N,\N

Note: use \N not \n.

edited Jun 06 '23 at 11:54

answered Jun 06 '23 at 11:36

Borislav Gizdov

1,323
12
22

with mysql load data infile I get "incorrect integer value"

3 Answers3