How do I get the LOAD DATA INFILE command to skip certain fields.
Lets say I have the following table:
CREATE TABLE stuff (
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
name CHAR(64) NOT NULL,
junk CHAR(64) NOT NULL,
a ENUM('N','Y') NOT NULL DEFAULT 'N',
PRIMARY KEY (id)
);
And my file:
,name1,junk1,
,name2,junk2,
,name3,junk3,Y
does the statement:
LOAD DATA INFILE 'myfile' INTO TABLE stuff FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
automatically skip the auto increment and deal with it properly? it seems to handle everything properly but this produces the following warnings:
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 1
Warning (Code 1265): Data truncated for column 'a' at row 1
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 2
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 3
Warning (Code 1265): Data truncated for column 'a' at row 3
Warning (Code 1366): Incorrect integer value: '' for column 'id' at row 4
removing the comma at the beginning doesn't input the data properly.
You can explicitly set id to NULL so it uses the auto-increment value.
LOAD DATA INFILE 'myfile'
INTO TABLE stuff
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
SET id = NULL;
I would also recommend removing the first comma and explicitly listing the columns in your file:
LOAD DATA INFILE 'myfile'
INTO TABLE stuff
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name,junk,a)
SET id = NULL;