If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Importing .CSV file into Access via ASP.NET is too slow

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
4) REPEAT

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!

Soooo...

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...

Not quite sure what you mean, though-- remember the data I'm importing is in a .CSV file. Are you saying to use a SQL IMPORT statement to the db with a SELECT from the .CSV file?? Not sure what that would look like. Can you give me an example? I've done this between tables in an Access db, and between SQL Server databases on one server, but I've never done this with a .CSV file and an Access db.

If you are uploading large files to your server before reading from them, like I am doing, you will quickly discover that ASP.NET has a built-in 4MB limit on file uploads.

You may even find info on changing this setting; but it takes some digging to discover that changing that value alone won't help much; you'll get timeouts in the webbrowser that manifest as empty blank browser windows, and none of your breakpoints will be hit. What happens is that IIS pulls the entire file in before doing anything with it, and before running the next line of your code. Meantime, the browser thinks nothing is happening, and you get the timeout.

The solution involves a change to 3 settings. You can apparently do this in code from your webpage, but I had trouble getting that to work reliably and decided to go with a web.config-based solution.

maxRequestLength is in bytes; I have set mine for 100MB. 8MB would be 8192; the default value of 4MB is expressed as 4096. It's a good idea to set this limit 1MB higher than the maximum filesize you want to allow.

I hope this helps someone. I had a lot of trouble trouble trying to find this info out by searching MSDN on the web, and wound up stumbling across it by accident at: http://msdn.microsoft.com/library/de...UploadASP2.asp after I had pieced it together from several other sources.

Looks like you've solved what I need, but I'm a little confused. Do I need to have the table already created if I use the previous strSQL string from pclement? and two, what do the F1 and F2 in "Select F1, F2" represent? Are they the "columns" in the csv file? And thirdly, is your first row in the csv the column headings?

The first SQL string Paul posted is for when the table you're importing into does not already have the table in it; the statement will create the table in your database, then fill it with the data from the .CSV file.

F1 and F2 are, as you guessed, the "columns", or fields, in the .CSV file. In my case, I needed the first 2 fields from the .CSV file. Choose each of the fields you need from the .CSV file; obviously, the first field on the left in the file is F1, the second is F2, the 3rd is F3, etc. If you don't need a field, don't specify it.
For example:
Say you want fields 1, 3 and 4. You would use "F1, F3, F4".

Simple when you know, but otherwise, it's tough to figure out...I found very little information on this in Microsoft's documentation.

Column headings- If there are column headings in the .CSV file, simply change the part of the SQL statement that says "HDR=NO" to read: "HDR=YES". I presume "HDR" stands for "Header Row".