Hi,
My opinion is alter your table so that auto increment column should be the last
column. Try importing values for n-1 columns, where the nth column will get
increment with auto_increment & get escaped from NULL values.
For instance:
mysql>create table x (item varchar(10),price int,id int not null auto_increment
primary key);
and sample.txt as
Pen 10
Pencil 3
Eraser 5
With this, mysql > LOAD DATA local INFILE "<fullpath>/sample.txt" INTO TABLE x
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
Result will be,
mysql> select * from x;
+--------+-------+----+
| item | price | id |
+--------+-------+----+
| rubber | 10 | 1 |
| pencil | 15 | 2 |
| pen | 45 | 3 |
| | NULL | 4 |
+--------+-------+----+
4 rows in set (0.00 sec)
Thanks
ViSolve DB Team
----- Original Message -----
From: "John Kopanas" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, November 24, 2006 9:42 PM
Subject: Having Problem With Load Files and AutoNumber id fields
> Argh... I am autogenerating a load file after parsing and massaging a
> text file I get from an AS/400 system. My first field is an id that
> is on auto_number so I put a tab at the begging of the line for each
> one of my records... it throws a warning for each line... if instead
> of putting an autonumber I put a random number it works perfectly.
> How can I get it to work?
>
> Sample line in my load file that works:
> 111111111111111111111 3066001210010000001467 SMART ALARMS INC 201
> MILLWAY AVE UNIT #8 \N \N VAUGHAN ON L4K 5K8 0 1999-9-1
> 00:00:00 1999-9-1 00:00:00
>
> Sample line that does not work:
> 3066001210010000001467 SMART ALARMS INC 201 MILLWAY AVE UNIT
> #8 \N \N VAUGHAN ON L4K 5K8 0 1999-9-1 00:00:00 1999-9-1 00:00:00
>
> My code that generates the sample line that does not work:
> tempfile.puts
> "\t#{record[0,22].strip}\t#{record[22,32].strip}\t#{record[54,32].strip}\t#{record[86,32].strip}\t#{record[118,32].strip}\t#{record[150,30].strip}\t#{record[180,2].strip}\t#{record[182,7].strip}\t0\t2000-01-01
> 00:00:00\t2000-01-01 00:00:00"
>
> How can I get mysql to put the auto_numbers in for me? Why is it even
> accepting my numbers?
>
> Thanks :-)
>
> Does anyone also know how I can see the warnings created by loadfile?
> All I just see is the summary with skipped and warnings.
>
> Thanks :-)
> --
> John Kopanas
> [EMAIL PROTECTED]
>
> http://www.kopanas.com
> http://www.cusec.net
> http://www.soen.info
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
