Importing .CSV file into Access via ASP.NET is too slow
Hoping someone can give me some "been there/done that" advice.
I have an Access 2002 database acting as the backend for a VB.NET 2005/ASP.NET 2.0 website.
I need to allow the users to upload a delimited text file, then add its contents into an existing table in the Access db; we're talking half a million rows and up in this textfile.
HOWEVER- for each record that goes into the db, I *also* need to put a Time/datestamp into one of the columns, and another piece of text information, which will change for each file imported, but will be the same for every entry within a file.
I have a working version in place, but it's WAY too slow. I have to launch it on a different thread because otherwise IIS/I.E. times out around 1 1/2 minutes.
The method in place works like this:
1) Save file to server
2) load entire file into OleDbDataAdapter/DataSet in one shot with a SELECT
3) iterate through DataSet, inserting one row at a time into db, adding my stamp & string data
I also tried an approach where I added empty columns to the DataSet after filling it from the textfile, then iterated through those columns and placed my stamp & string data in, and Updated the dataset into the db table. As far as I could tell, that ran even SLOWER!
The obvious question: anyone know of a way to do this in a "bulk insert" type of way? After extensive searching, none of the approaches I found on the web consider the problem of adding the data in the additional 2 columns.
I thought of adding the stamp and string data into the table after the bulk insert, but, doing this in Access, I'm not sure how to go about determining which rows to insert the data into after inserting the data from the file...
I've wasted enough time getting this far that I don't want to waste more tackling an approach which may not work at all, or, if it does, may not be any faster...
Thanks for any assistance...