Importing data quickly from text to Access

[dswitzer]

What is the quickest way to move data from a csv/txt file into an existing table in MSAccess?

I browse for the file using openfiledialog....
Assume I know everything in the txt file is gold (no errors at all) -- do I have to put it into a datagrid or something and then move into an access table? or can I move it straight over?

I was going down the path of building a procedure in VBA in my database -- I would pass this VBA procedure the path from my openfiledialog and then (using DoCmd.TransferText...) transfer data straight from the file into the existing table. Since I can't figure out how to run this procedure from VB.NET, I thought there must be a better way....

I have no interest in viewing/editing data -- I just want a fast transfer - what is the best way (keeping in mind I am still new here)?

Thanks.

Dave

 

[ThatRickGuy]

There are a few options.

You could read the file from a text stream, open a datatable for the destination table in access, then append data from the text stream to the table.

Or you could use the MS text file ODBC driver to open the text file as a datasource and import the data row by row to the data table from access.

Or you could build a custom SQL String based off of the text read in to insert the data.

Of you could use the Office COM libraries to tell access to import the text file.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[earthandfire]

Probably the easiest way, if you know what version of Access you will be using:

Set a Reference to the Access Object Library - (add References, select COM tab, select Microsoft Access, click Select, click OK)

in your program -

Dim axs as Access.Application
axs.DoCmd.TransferText(<there are several parameters - fill in as necessary)
axs = nothing


Hope this helps.

 

[dswitzer]

Thanks.

I tried the COM route first (get the easiest one working and move on from there):

Dim axs As New Access.Application
        axs.OpenCurrentDatabase("C:\TestDataImport.mdb", False, "")
        axs.DoCmd.RunSQL("Delete * from tblContacts")
        axs = Nothing

It worked -- but whenever it opened the database -- I got the "Security Warning for Macros" dialog box.... since I can't seem to control this - probably not the best option (If I am wrong - please correct me). I needed to add the OpenCurrentDatabase to tell it where to go...but I'm not sure if there is a better way.

So I moved on to try the filestream method. This code works fine:

'This works to read data from a file
Dim fs As New System.IO.FileStream("P:\dir\filename.csv", IO.FileMode.Open, IO.FileAccess.Read)
Dim srReader As New StreamReader(fs)
While srReader.Peek() > -1
       MsgBox(srReader.ReadLine())
End While

But I am struggling with how to set up a datatable for the MSAccess table -- and ultimately, how to populate that table with the filestream data...I'm sure I'm missing something fundamental here. I've been searching everywhere for examples and haven't found anything yet. Can someone give me a boost here?

Thanks

Dave

 

[ThatRickGuy]

You may be able to change the Macro Warning in access. Check the options for Macro Security, you should have the option to disallow, prompt, and allow.

As for getting the data from a text steam, you'll have to parse it. if it's character delimited, you can use the string.split(",") function to return an array of string values, if it's fixed width, you can use string.substring(start, length) to get the values.

Or you can use the Microsoft Text ODBC driver which will allow you to read the text file as if it were a database.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.