1
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

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

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.