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

[Andrew Cushen]

Hi all-

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

Thanks for any assistance...

-Andrew

[pclement]

Why not just import using SQL and create the date/timestamp/string data in your SQL statement?

[Andrew Cushen]

Paul-

Thanks for the response.

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.

Thanks,

-Andrew

[pclement]

Dim cnn As New ADODB.Connection
Dim strSQL As String

cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\Test Files\db1 XP.mdb;" & _
                     "Jet OLEDB:Engine Type=4;"

strSQL = "SELECT F1, F2, Now() AS DateTimeStamp INTO [tblSample] FROM [Text;HDR=NO;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[Sample.csv]"

cnn.Execute strSQL

cnn.Close

[Andrew Cushen]

Paul-

I see what you are getting at, now; I had to translate your ASP code to ASP.NET.

But the statement as written drops my existing table and creates a new one, then imports the data into that. I need to keep the data already in the table and append the new data. Any ideas?

The next approach I'm going to try is to use your idea to SELECT the file data and my additional data into a DataSet, then UPDATE that into the Access db.

Thanks for your suggestion...Anyone else, feel free to jump in...

-Andrew

[pclement]

You just need to tweak the SQL statement if you're importing into an existing table:

strSQL = "INSERT INTO [tblSample] (F1, F2, DateTimeStamp) SELECT F1, F2, Now() AS DateTimeStamp FROM [Text;HDR=NO;DATABASE=C:\Documents and Settings\nfisppc\My Documents\My Database\Text;].[Sample.csv]"

[Andrew Cushen]

Ouch.

I thought I knew a little about SQL, but I never knew you could pull the data out of a textfile like that and blow it into the db in one statement.

Wow. I think you've solved my problem and then some. I've been banging my head against the wall on this one for 3 days now.


THANK YOU PAUL!!!! I see why you are an MS MVP...

-Andrew

[Andrew Cushen]

IT WORKS IT WORKS IT WORKS!!!

Thanks again, Paul.

For those curious to see what it looks like in ASP.NET, with my slight alterations:

Dim ds As New DataSet
' Pull connection string from Web.Config (will be encrypted later)
Dim objConn As New System.Data.OleDb.OleDbConnection( _
System.Configuration.ConfigurationManager.ConnectionStrings _
                                        ("MyConnectionString").ToString)

'WHY DOES THIS SITE CHOP APART "ConnectionStrings" ABOVE???
' THERE SHOULD NOT BE A SPACE THERE!! I PUT NONE THERE!

' Make sure the .CSV file exists:
If File.Exists(strPath & strFileName) Then
            Try
                ' ------ Load the data from the .CSV file: ----------
                ' NOTE: "Source' is text user enters that gets put into db
                ' Grab "Source" text from ASP.NET textbox:
                Dim strSource As String = Me.txtSource.Text

                Dim strSQL As String = "INSERT INTO [dbTable] " & _
                "(AreaCode, PhoneNumber, Source, DateTimeAdded)" & _
                "SELECT F1, F2,'" & strSource & "', Now() AS DateTimeStamp" & _
                " FROM [Text;HDR=NO;DATABASE=C:\Documents and" & _              
               "Settings\...\Website;].[importFile.csv]"

                objConn.Open()
                Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConn)
                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                objConn.Close()
             Catch ex as Exception
                'handle error here
             End Try

Obviously in the actual app I will not hard-code the directory and filename; they will come from a file uploaded by the user.


Maybe this will help someone else to avoid the misery I've been in the last week or so...


-Andrew

[Andrew Cushen]

One other thing:

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.

Put the following in the system.web section of web.config:

<httpRuntime
             executionTimeout="10000"
	maxRequestLength="102400"
	requestLengthDiskThreshold="80"
/>

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.

-Andrew

[ajwaka]

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?

Thanks much!!!!

[Andrew Cushen]

Hi ajwaka:

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


HTH,

-Andrew

[ajwaka]

Andrew,

Thanks for the reply. I started playing with it yesterday, but kept getting errors. I'm sure it works and works great.

I did find another easy solution using SqlBulkCopy in an article by David Hayden - here: http://davidhayden.com/blog/dave/arc...5/31/2976.aspx

I do appreciate all your help though!!