Import text file to access db table (OleDB, ADO, ADO.NET)

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

The question of how to import a text file into the database table has
been asked many times. The code that suits me was given by Paul
Clement is the following:
Sub ImportTextToAccessADO()

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlString As String

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My
Documents\TextFiles].[Sample2.txt]"

cnn.Execute sqlString
cnn.Close

End Sub

It works great but only if the table doesn't exist. My questions are
how to do this if the following table already exists. Will bulk Insert
like this work or do I have to use adapter to fill a dataset and then
insert into the table row by row. Also how can I check if the table
exists then I delete it and create a new one but with the same schema
as it had before. Appying the above code to the table that doesn't
exist creates a new table with columns labelled F1, F2, etc. How do I
create custom columns?

I would greatly appreciate any input on this (preferably using .NET).
Thank you.
 
On 5 Dec 2003 08:37:09 -0800, (e-mail address removed) (Max) wrote:

¤ The question of how to import a text file into the database table has
¤ been asked many times. The code that suits me was given by Paul
¤ Clement is the following:
¤ Sub ImportTextToAccessADO()
¤
¤ Dim cnn As New ADODB.Connection
¤ Dim rs As New ADODB.Recordset
¤ Dim sqlString As String
¤
¤ cnn.Open _
¤ "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=e:\My Documents\DB1.mdb;" & _
¤ "Jet OLEDB:Engine Type=4;"
¤
¤ sqlString = "SELECT * INTO [tblSample2] FROM
¤ [Text;HDR=NO;DATABASE=e:\My
¤ Documents\TextFiles].[Sample2.txt]"
¤
¤ cnn.Execute sqlString
¤ cnn.Close
¤
¤ End Sub
¤
¤ It works great but only if the table doesn't exist. My questions are
¤ how to do this if the following table already exists. Will bulk Insert
¤ like this work or do I have to use adapter to fill a dataset and then
¤ insert into the table row by row. Also how can I check if the table
¤ exists then I delete it and create a new one but with the same schema
¤ as it had before. Appying the above code to the table that doesn't
¤ exist creates a new table with columns labelled F1, F2, etc. How do I
¤ create custom columns?
¤
¤ I would greatly appreciate any input on this (preferably using .NET).
¤ Thank you.

Here is an example of a SQL INSERT query that works with an existing table:

sqlString = "INSERT INTO [tblOrder] (Field1, Field2, Field3) SELECT F1, F2, F3 FROM
[Text;DATABASE=e:\My Documents\TextFiles;].[Order.txt]"

The above example uses the default field/column names (F1, F2, F3) for the Text file.

If you want specific column names you will need to either put the column names in the first row of
the Text file and use the following connection string:

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblOrder] (Field1,
Field2, Field3) SELECT COL1, COL2, COL3 FROM [Text;DATABASE=e:\My
Documents\TextFiles;HDR=YES;].[Order.txt]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

or, the alternative is to create a schema.ini file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp

Here is an example:

[CSVFile.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=CSVDelimited
Col1=Prefix Text
Col2=Base Text
Col3=Suffix Text
Col4=Revision Text
Col5=Version Text
Col6=CreationDate Text
Col7=LastUpdate Text
Col8=Creator Text


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top