I am having problems getting the speed that I want in importing approx 30,000 lines of data from Tab separated files in to an Access database. The File import is managed from a VB.net 2010 program that I have written.
The program is currently looping through each line and issuing a SQL insert statement for each one. However this is taking about 2 hours to complete. If I manually import in the same file directly into access using Get External Data, it takes about 30 seconds.
I was thinking that I might be able to create a datagridview linked to MS access and somehow run a native .net command to import the file into the datagridview and then quickly write the data to access but i'm not sure if this is possible.
By the way doing a manual import is not an option, it needs to go through the vb.net application for various reasons.
Can any one provide any suggestions on how I might speed things up?
Thanks
Simon
You can run an SQL statement against your MS Access connection to import text/csv. To import into a new table:
cmd.CommandText =
"SELECT F1 AS id, F2 AS firstname " &
"INTO MyNewTable " &
"FROM [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=C:\__tmp].table1.csv;"
To import into an existing table:
cmd.CommandText =
"INSERT INTO MyExistingTable " &
"SELECT F1 AS id, F2 AS firstname " &
"FROM [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=C:\__tmp].table1.csv;"
You can also use a schema.ini file for non-standard delimiters. ( Exporting data into a pre-formatted file , Create comma separated file (csv) from access - scheduled daily from windows)
The question is why use .Net at all? If your using a newer version of Access it will run full on SQL. This allows you to use SQL Bulk Insert. Bulk insert blows the pants off every other method of data insertion in SQL.
If you need to manipulate the data you can bulk insert the raw data in a heap table (just a raw dump of the data from the file) then use SQL to update/move the relevant data into the correct table(s).
