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
