I'd pick up the text file and search/replace all end of line character(s) with a end of line"00 "comma combination so it reads > 00,Abe,Lincoln,8347 on each line and try your first method. If that fails, I would drop the ID field off the table, then do the import with original file, then atlter the table again to add the ID field Alan
> -----Original Message----- > From: Chris Walcott [mailto:cwalcott@;macromedia.com] > Sent: Thursday, 14 November 2002 9:42 > To: Mysql-L (E-mail) > Subject: trouble with LOAD command > > > How do I LOAD a text file into a table that has a Primary Key defined? > > I have the following table defined: > mysql> describe phoneList; > +--------------+-------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +--------------+-------------+------+-----+---------+----------------+ > | ID | int(11) | | PRI | NULL | auto_increment | > | First_Name | varchar(20) | YES | | NULL | | > | Last_Name | varchar(20) | YES | | NULL | | > | Phone_Number | varchar(20) | YES | | NULL | | > +--------------+-------------+------+-----+---------+----------------+ > > I'm attempting to load a comma delimited list using load. The > text file looks like this: > > Abe,Lincoln,8347 > Herb,Albert,9387 > George,Washington,9283 > ... > > When I do this: > mysql> load data local infile "/home/cwalcott/PhoneList_b.txt" > into table phoneList > -> fields terminated by ',' (First_Name, Last_Name, Phone_Number); > > I get this on select * > +----+------------+------------+--------------+ > | ID | First_Name | Last_Name | Phone_Number | > +----+------------+------------+--------------+ > |e | Lincoln | 8347 > |rb | Albert | 9387 > |orge | Washington | 9283 > +----+------------+------------+--------------+ > > if I do this: > load data local infile "/home/cwalcott/PhoneList_b.txt" into > table phoneList; > > I get: > +----+------------+-----------+--------------+ > | ID | First_Name | Last_Name | Phone_Number | > +----+------------+-----------+--------------+ > | 1 | NULL | NULL | NULL | > | 2 | NULL | NULL | NULL | > | 3 | NULL | NULL | NULL | > +----+------------+-----------+--------------+ > > I've also tried using a text file with the first column set to > index numbers but the results are very similar. > > If I do this: > > mysql> load data local infile > "/home/cwalcott/PhoneList_small.txt" into table phoneList > -> fields terminated by ',' (First_Name, Last_Name, Phone_Number); > > I get: > +----+------------+-----------+--------------+ > | ID | First_Name | Last_Name | Phone_Number | > +----+------------+-----------+--------------+ > | 1 | 1 | Abe | Lincoln | > | 2 | 2 | Herb | Albert | > | 3 | 3 | George | Washington | > +----+------------+-----------+--------------+ > > If I do this: > mysql> load data local infile > "/home/cwalcott/PhoneList_small.txt" into table phoneList > -> fields terminated by ',' (ID, First_Name, Last_Name, Phone_Number); > > I get this: > +----+------------+------------+--------------+ > | ID | First_Name | Last_Name | Phone_Number | > +----+------------+------------+--------------+ > |e | Lincoln | 8347 > |rb | Albert | 9387 > |orge | Washington | 9283 > +-----+------------+-----------+--------------+ > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
