MySQL LOAD DATA INFILE with auto increment

Question

I have tried the suggestion in the question below but I still have syntax errors.

How to LOAD DATA INFILE in mysql with first col being Auto Increment?

create table db.test
(ai_id int(11) auto_increment primary key,
field varchar(5))

LOAD DATA LOCAL INFILE 'C:\\Users\\nick\\Desktop\\test\\book1.csv' 
INTO TABLE db.test
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(field)
SET ai_id = NULL
IGNORE 1 lines;

I am having trouble reconciling this seemingly very simple syntax error, any assistance greatly appreciated!

EDIT: error code: 1064: You have error in SQL syntax; check syntax around 'ignore 1 lines' line 8.

datasource is a csv with one column "field" with five rows "one"-"five"(all five rows are characters not int)

Edit your question with some sample data.It seems to me the varchar size might be too small.Also what are the errors? — Mihai, Oct 20 '15 at 16:03

score 0 · Answer 1 · answered Oct 20 '15 at 16:23

0

This syntax is correct, I tested its working(in MySQL 5.6). please verify your input file.

answered Oct 20 '15 at 16:23

Adarsh Gangadharan

653
4
10

score 0 · Answer 2 · answered Oct 20 '15 at 18:33

The following works. It appears the order of commands is what threw it off.

LOAD DATA LOCAL INFILE 'C:\\Users\\nshatz\\Desktop\\test\\book1.csv' 
INTO TABLE db.test
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 lines
(field);

MySQL LOAD DATA INFILE with auto increment

2 Answers2