Page 1 of 1

Load data infile ignore first field

Posted: Thu Feb 02, 2012 5:34 pm
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:
,This,is,text,\n
,This,text,is,\n
,text,is,This,\n
,is,This,text,\n
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:
LOAD DATA INFILE 'C:/Path/To/File/file.txt' INTO TABLE mytable
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';
Thanks in advance :)

Re: Load data infile ignore first field

Posted: Fri Feb 03, 2012 1:11 am
by jacek
Do you have to do it in SQL only or can you use php ?

Re: Load data infile ignore first field

Posted: Fri Feb 03, 2012 6:41 am
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 :)

Re: Load data infile ignore first field

Posted: Sat Feb 04, 2012 12:40 am
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.

Re: Load data infile ignore first field

Posted: Sat Feb 04, 2012 1:44 am
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