Programmatically converting csv file into MSAcess table

[JH]

I have a comma delimited file and I want to export it to an MS access table
already designed with appropriate field names. How do I do this
programmatically using VB.NET or C#?

Thanks for any help in advance.

 

[Guest]

It seems to me that Access has an Import function that works on CSV files.
If you really need to execute it from .NET you can probably automate the
Import with a Runtime Callable wrapper.

But automating from VB6 - or even an Access Macro would be easier.

Getting back to .NET: Another approach would be to parse each line in the
CSV file into individual values and store them in a DataTable (that's a class
in the System.Data namespace). Look at the string.substring method to do the
parsing.

Once you've populated the DataTable, you can use a DataAdapter to issue
appropriate SQL commands to store the rows in Access. As you read up on the
DataAdapter, you'll learn about how it uses Command objects to issue SQL
statements for loading the Access table you mentioned.

You'll need a variety of .NET objects to get this working...a DataSet to
contain your DataTable, A Connection, one or more Command objects, as well as
the DataAdapter. But fear nought: Visual Studio will help get you started.

Go into the Server Explorer in Visual Studio - Click on "Add Connection" and
the "Provider". Select the Jet OleDB provider for access and instantiate a
connection. Next step is to go to the ToolBox and drag a DataAdapter onto a
form (either a windows for or a web form). This puts you into a wizard that
you can use to not only create the DataAdapter but also the SQL commands you
will need.

HTH

 

[Grzegorz Danowski]

I have a comma delimited file and I want to export it to an MS access table
already designed with appropriate field names. How do I do this
programmatically using VB.NET or C#?

Another way is to use special jet query, for example:

Insert Into
tblResult
Select
*
From
[Text;FMT=Delimited;HDR=YES;DATABASE=E:\].[MyTextData.csv]

But, I'm not sure if comma delimited will be possible to use (I have tested
it on file with semicolon delimited).

I hope it helps.
Grzegorz

 

[Paul Clement]

¤ I have a comma delimited file and I want to export it to an MS access table
¤ already designed with appropriate field names. How do I do this
¤ programmatically using VB.NET or C#?
¤

See if the following works for you:

Function ImportTextToAccess() As Boolean

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

AccessConn.Open()

'Existing table
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tbl1] (Field1, Field2,
Field3, Field4, Field5) SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=d:\My
Documents\TextFiles;].[TextFile.txt]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function


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