Re: Having Problem With Load Files and AutoNumber id fields

2006-11-27 Thread Visolve DB Team 
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:
mysqlcreate table x (item varchar(10),price int,id int not null auto_increment 
primary key);

and sample.txt as
Pen10
Pencil3
Eraser5

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:
 1 306600121001001467 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:
 306600121001001467 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]

Re: Having Problem With Load Files and AutoNumber id fields

2006-11-27 Thread Remo Tex 

John Kopanas wrote:

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:
1306600121001001467SMART ALARMS INC201
MILLWAY AVE UNIT #8\N\NVAUGHANONL4K 5K80
1999-9-1

00:00:001999-9-1 00:00:00

Sample line that does not work:
306600121001001467SMART ALARMS INC201 MILLWAY AVE UNIT
#8\N\NVAUGHANONL4K 5K801999-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 :-)
Try with 0 at autoinc position. If 0 or NULL doesn't do the trick... 
well perhaps you should check your --sql-mode=


http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. 
Normally, you generate the next sequence number for the column by 
inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses 
this behavior for 0 so that only NULL generates the next sequence number.


This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT 
column. (Storing 0 is not a recommended practice, by the way.) For 
example, if you dump the table with mysqldump and then reload it, MySQL 
normally generates new sequence numbers when it encounters the 0 values, 
resulting in a table with contents different from the one that was 
dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file 
solves this problem. mysqldump now automatically includes in its output 
a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.


HTH

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Having Problem With Load Files and AutoNumber id fields

2006-11-27 Thread Jerry Schwartz 
Perhaps that empty field is being interpreted as a string?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: John Kopanas [mailto:[EMAIL PROTECTED]
 Sent: Friday, November 24, 2006 11:13 AM
 To: mysql@lists.mysql.com
 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:
 1 306600121001001467  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:
   306600121001001467  SMART ALARMS INC201
 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]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: RE: Having Problem With Load Files and AutoNumber id fields

2006-11-27 Thread John Kopanas 

The empty field did not work but the 0 worked beautifully!  Thanks everyone! :-)

On 11/27/06, Jerry Schwartz [EMAIL PROTECTED] wrote:

Perhaps that empty field is being interpreted as a string?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: John Kopanas [mailto:[EMAIL PROTECTED]
 Sent: Friday, November 24, 2006 11:13 AM
 To: mysql@lists.mysql.com
 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:
 1 306600121001001467  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:
   306600121001001467  SMART ALARMS INC201
 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]









--
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]