Import Text File into Access Database

[shorty545]

Here is what I am trying to do. Hopefully someone with more knowledge than me can help.

I want to write a code that will open a text file on a local pc, and import that text file directly into an Access database.

There will be several files to import and therefore several tables in Access.

When I run what I think should work i get the following error.

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Here is my Code:

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

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tbl1] SELECT * FROM [Text;DATABASE=c:\CSV.txt]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

The program bombs out on the AccessCommand.ExecuteNonQuery() function everytime.

Any Advice???

 

[kulrom]

To be honest i have no idea what you are doing here
I've never seen someting like this before ... however, i would first read the content from the file using System.IO.StreamReader and then proceed it into DB but, who knows maybe you are right (maybe there is a way to read it directly ... but, i'm suspicious yet)

Regards

 

[jmcilhinney]

That SQL is all wrong. If you want to copy data directly from one table to another you use "SELECT INTO", but that creates a new table. Also, I don't know for sure but I very much doubt that you can draw data directly from anywhere but the Access database you are currently connected to. I don't think Access could get data directly from the CSV like that. If you have information that says otherwise then I'd be interested to know where from. What you would need to do, I believe, is create two connections: one for the CSV and one for Access. You could then use a data adapter to retrieve the data from the CSV and then send it to the Access database. You would use one data adapter with a SelectCommand, which would use the connection to the CSV, and an InsertCommand, which would use the connection to Access.

 

[shorty545]

Thanks for the reply's. To be honest, the code I am trying is code copied from a previous post to a similar problem. I have no idea if it should or could work.

I know it is possible in VB6 using a statement similar to: App.Path.DoCmd.TransferText

would System.IO.Streamreader be similar?

 

[shorty545]

I'm Sorry the code in VB6 I'm using is:
Access.Application.DoCmd.TransferText

 

[jmcilhinney]

OK, that's Access automation, not ADO.NET. You can do that in a .NET app as well but it doesn't involve anything from the System.Data namespace. You would need to add a refererence to the Access type library to your app and then use the objects within it to control an instance of the Access application directly. This is not the most efficient way to get things done with Access but it is very powerful, because it gives you access to virtually every function available from the Access GUI. In your case, I'd suggest you go with ADO.NET in two stages as I suggested. I think it's the easiest and most efficient method. You could use a StreamReader to get the data from the CSV but then you would need to add it all to a table manually in order to then use ADO.NET to get it into Access. Automation is, in my opinion, slow and fiddly to code. ADO.NET is straightforward and efficient.

 

[shorty545]

The only problem is the file I am importing into the Access database is not a CSV file. It is a text file that is delimited by either tab or quotes!

Sorry for my ignorance on this. I am very new to this level of programming.

 

[mjb3030]

It would work the same way with the text file. A CSV file is just a 'comma separated values' text file.

 

[jmcilhinney]

ADO.NET will definitely understand a tab delimited file, as TSV is the second most common form of deleimited text file after CSV. I really don't know how other delimiter types would be handled.

 

[shorty545]

Okay, i think i got a little off track. I got the import to work using the access automation. But for some reason it is only importing one record for each unique key and not importing anything else. I'm still trying to figure out why.

You said it would be better to use ADO.NET. I've never used StreamReader before so I will start to research its function. Can you clarify what you mean by "you would need to add it all to a table manually". Is this the table in Access, or a temporary table in VB?

 

[jmcilhinney]

A StreamReader just reads plain text from a file. You would have to create a DataTable and its schema yourself, before using ADO.NET to send that data to the Access database. If you use ADO.NET to retrieve the data from the file in the first place it can automatically create the schema and populate the DataTable for you. It would give you more control to create the table schema yourself, but I'd still be inclined to use an ADO.NET DataReader to retrieve the data. Plus, automatiuon requires Access on the machine, whereas ADO.NET will work with just an MDB file.

 

[shorty545]

Thanks for your advice. I will give it a shot. If I have any other questions I will let you know.