Re: trouble with LOAD command
Chris Walcott wrote:
How do I LOAD a text file into a table that has a Primary Key defined?
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: trouble with LOAD command
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
