Load data infile ignore first field

Post here if you need help with SQL.
Post Reply
User avatar
Temor
Posts: 1186
Joined: Thu May 05, 2011 8:04 pm

Load data infile ignore first field

Post by Temor »

As part of a project I'm working on I have to store quite a lot of data locally on a .txt file and then insert it into a MySQL database.
I'll most likely be using LOAD DATA INFILE for this.
So, to the point: my first field is ID. I do not want to insert anything into the ID field. Is it possible to tell SQL to ignore the first field? What I do now is start my string with a field delimiter. Like this:
[syntax=text]
,This,is,text,\n
,This,text,is,\n
,text,is,This,\n
,is,This,text,\n[/syntax]

but I will probably forget to add the commas to the start of each line when I have to sit down and write every line by hand(400+lines) and I'm quite certain that my brain will turn into mush and / or something materialistic will break if I start seeing MySQL errors.

My SQL looks like this:
[syntax=sql]LOAD DATA INFILE 'C:/Path/To/File/file.txt' INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
[/syntax]

Thanks in advance :)
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Load data infile ignore first field

Post by jacek »

Do you have to do it in SQL only or can you use php ?
Image
User avatar
Temor
Posts: 1186
Joined: Thu May 05, 2011 8:04 pm

Re: Load data infile ignore first field

Post by Temor »

jacek wrote:Do you have to do it in SQL only or can you use php ?


I can use as much php as I like :)
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Load data infile ignore first field

Post by jacek »

I would process the file with php then, and add a default initial value if one doesn’t exist. Then you can build up the VALUES part of the SQL and just run it normally.
Image
User avatar
Temor
Posts: 1186
Joined: Thu May 05, 2011 8:04 pm

Re: Load data infile ignore first field

Post by Temor »

jacek wrote:I would process the file with php then, and add a default initial value if one doesn’t exist. Then you can build up the VALUES part of the SQL and just run it normally.

You're right. Why didn't I think of that :P
Post Reply