LOAD DATA LOCAL INFILE with specifying first column as auto-increment value

Question

LOAD DATA LOCAL INFILE 'index.csv'
INSERT INTO TABLE `aws_pricing`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 6 LINES;

Thing is, my table has the first column as id int NOT NULL AUTO_INCREMENT ... PRIMARY KEY (id), so I get :

ERROR 1265 (01000): Data truncated for column 'id' at row 1

So index.csv has one column less. How do I get LOAD DATA LOCAL INFILE to work by specifying to auto-increment before reading each line from index.csv file ?

MySQL Ver 8.0.32

• Akina: Use input preprocessing. Load the value for AI column into UDV and ignore its value.
• J.D.: You should always tag which database system and version you're using. I've updated the tags with the system, please add the version.

Answer 1

You should name the columns you are importing

For example, this is from the MySQL Documentation on LOAD DATA INFILE

CREATE TABLE jokes
 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
 FIELDS TERMINATED BY ''
 LINES TERMINATED BY '\n%%\n' (joke);

So, in your case, name your columns after all the line and field escaping

LOAD DATA LOCAL INFILE 'index.csv'
INSERT INTO TABLE `aws_pricing`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (col1,col2,...)
IGNORE 6 LINES;

Just name all the columns except id