Gman wrote:
How easy is it to import a CSV file into MySQL and when it is done doesIf you're using Access / VB, you might be able to modify this function to suit your needs. Note that all my primary keys are called DanPK, and each table has a timestamp called MyStamp...
MySQL or the program that does that automatically create a field to
match the fields in my CSV file? If not is this something that could be
easily implemented?
George Flatman
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=myserver;" _
& "DATABASE=mydatabase;" _
& "UID=mylogin;" _
& "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" _
& "'" & 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
