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
RE: trouble with LOAD command
I'd pick up the text file and search/replace all end of line character(s) with a end of line00 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
Re: trouble with LOAD command
Chris Walcott wrote:
How do I LOAD a text file into a table that has a Primary Key defined?
Most stuff snipped. Sorry :)
I drop the Primary Key (and Timestamp) when I use 'load data infile'.
Below is my VB code which I use in Access to import stuff into MySQL.
Maybe someone will find it useful.
The point is that you have to drop the PK before the import, and add it
back later. If you can't do this, because you already have data in the
table and don't want to mess up the PK, then import the data into a tmp
table and then do a 'insert into' command to append it where you want it.
Function MySQLImport(source As String, Destination As String, _
FieldTerminator As String, Optional OptionalWrapper As String, _
Optional IgnoreLines As Integer, Optional LineTerminator As String, _
Optional NoTruncate As Boolean, Optional KeepPrimaryKey As Boolean, _
Optional KeepMyStamp As Boolean)
Dim myconn As ADODB.connection, mycommand As ADODB.command, sqlstr As String
sqlstr = DRIVER={MySQL ODBC 3.51 Driver}; _
SERVER=vortex; _
DATABASE=mydatabase; _
UID=me; _
PASSWORD=mypassword
Set myconn = New ADODB.connection
With myconn
.ConnectionString = sqlstr
.Open
End With
Set mycommand = New ADODB.command
mycommand.ActiveConnection = myconn
If NoTruncate = False Then
sqlstr = truncate table Destination
mycommand.CommandText = sqlstr
mycommand.Execute
End If
' Drop primary key timestamp for import...
If KeepPrimaryKey And KeepMyStamp Then
' Nothing...
Else
sqlstr = alter table Destination
If Not KeepPrimaryKey Then
sqlstr = sqlstr drop primary key, _
drop DanPK,
End If
If Not KeepMyStamp Then
sqlstr = sqlstr drop MyStamp
End If
mycommand.CommandText = sqlstr
mycommand.Execute
End If
' Import...
sqlstr = load data infile _
'/usr/local/bills_on_disk/ElectronicBills2MySQL/ source ' _
into table Destination _
fields terminated by ' FieldTerminator ' _
IIf(Len(OptionalWrapper) = 0, , optionally enclosed by '
Chr(34) ') _
IIf(Len(LineTerminator) = 0, , lines terminated by '
LineTerminator ') _
IIf(Len(IgnoreLines) = 0, , ignore IgnoreLines lines)
mycommand.CommandText = sqlstr
mycommand.Execute
' Add Primary Key Timestamp so Access can get hold of it...
If KeepPrimaryKey And KeepMyStamp Then
' Nothing
Else
sqlstr = alter table Destination
If Not KeepPrimaryKey Then
sqlstr = sqlstr add DanPK mediumint(8) unsigned NOT NULL
auto_increment first, _
add primary key (DanPK),
End If
If Not KeepMyStamp Then
sqlstr = sqlstr add MyStamp timestamp(14) not null after DanPK
End If
mycommand.CommandText = sqlstr
mycommand.Execute
End If
myconn.Close
Set myconn = Nothing
Set mycommand = Nothing
End Function
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
-
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
Re: LOAD command
The SERVER needs to be able to read the file, and I doubt very much that mysql has read permissions here. Jim Bahr wrote: Hello I am trying to use the LOAD command and not having much success. My host company is using version mysql 3.23.51. I am sshing into server, starting mysql and getting a mysql prompt. I have a database called login, and have made an empty table called logintest. I want to load it with data from a text file (downacct.txt). The text file is in a dir. called websites (the higest permission level I have on this shared server) I use the following commands USE login; LOAD DATA INFILE '/home/cbslink/websites/downacct.txt' INTO TABLE login.logintest; And get the following; ERROR 1105: File '/home/cbslink/websites/downacct.txt' not found (Errcode: 13) Any ideas? Is there another way I can move this large amount of data into a table? Thanks ahead of time Jim - 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
re: LOAD command
Jim, Tuesday, October 01, 2002, 4:01:18 PM, you wrote: JB I am trying to use the LOAD command and not having much success. My host JB company is using version mysql 3.23.51. I am sshing into server, JB starting mysql and getting a mysql prompt. I have a database called JB login, and have made an empty table called logintest. I want to load it JB with data from a text file (downacct.txt). The text file is in a dir. JB called websites (the higest permission level I have on this shared JB server) JB I use the following commands JB USE login; JB LOAD DATA INFILE '/home/cbslink/websites/downacct.txt' INTO TABLE JB login.logintest; JB And get the following; JB ERROR 1105: File '/home/cbslink/websites/downacct.txt' not found JB (Errcode: 13) JB Any ideas? Is there another way I can move this large amount of data JB into a table? $ perror 13 Error code 13: Permission denied MySQL doesn't have permissions on that file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
LOAD command failed if the Local infile is not \bin
Hi, I just discovered that I have to move the data text file I want to store in my table into bin directory of mysql directory (c:\mysql\bin\) before the LOAD DATA local infile text.txt INTO TABLE tabname can work. The problem here is that how do I then transfer this kind of file from my local computer if at all i am able to connect to mysql server at the remote machine. Regards. kamal - 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
Re: LOAD command failed if the Local infile is not \bin
On Thu, Dec 13, 2001 at 12:34:59PM -0500, [EMAIL PROTECTED] wrote: Hi, I just discovered that I have to move the data text file I want to store in my table into bin directory of mysql directory (c:\mysql\bin\) before the LOAD DATA local infile text.txt INTO TABLE tabname can work. No just specify the full pathname to the file. If it's in D:\tmp\text.txt, use: LOAD DATA LOCAL INFILE D:\\tmp\\text.txt INTO TABLE tabname; Or: LOAD DATA LOCAL INFILE D:/tmp/text.txt INTO TABLE tabname; The problem here is that how do I then transfer this kind of file from my local computer if at all i am able to connect to mysql server at the remote machine. Regards. kamal - 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
Connecting to mysql server in a remote site and using LOAD command
Dear All, I have Mysql server installed at remote site which i need to connect to. I have tried this from my local computer but didn't work; c:\mysql\binmysql -h myhost -u myusername -pmypassword I will also like to use LOAD command to load a text file from my local computer to the database on this mysql server at the remote site. I have been able to achieve both actions from my local machine. I am using win 98, Mysql 3.23 All supports to get these done will be appreciated. Thanks to you all. kamal. - 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
Connecting to mysql server in a remote site and using LOAD command
Dear All, I have Mysql server installed at remote site which i need to connect to. I have tried this from my local computer but didn't work; c:\mysql\binmysql -h myhost -u myusername -pmypassword I will also like to use LOAD command to load a text file from my local computer to the database on this mysql server at the remote site. I have been able to achieve both actions from my local machine. I am using win 98, Mysql 3.23 All supports to get these done will be appreciated. Thanks to you all. kamal. - 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
