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
