CODEWITHSUNDEEP
mysql
user2755209
user2755209

Reputation: 85

Loading Data - Skip first line & Columns Don't match

I am trying to load a data set into a table and need to skip the first line of that csv file and also need to leave the first column blank for an id auto increment to fill it.

Here is what I am trying right now 

load data local infile 'C:\\bla\blah\file.txt' into table sources_1
fields terminated by '\","'
lines terminated by '\r\n'
(@col1,@col2,@col3,@col4) set name=@col1,address@col2,city@col3,state@col4
ignore 1 lines

Upvotes: 0

Views: 624

Answers (1)

Barmar
Barmar

Reputation: 782488

Try this:

load data local infile 'C:\\bla\blah\file.txt' into table sources_1
fields terminated by ','
       optionally enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(name, address, city, state)

It puts the data from the CSV directly into the appropriate columns, no user-variables are needed. Omitting the ID column will cause it to be filled in with the auto-increment value.

The IGNORE 1 LINES clause should be before the column names.

Upvotes: 2

Related Questions