mpj mpj - 6 months ago 46
MySQL Question

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

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

Answer

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.