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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

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

Hybrid View

  1. #1
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538

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

    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
    Share on Google+

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Why not just import using SQL and create the date/timestamp/string data in your SQL statement?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)
    Share on Google+

  3. #3
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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
    Share on Google+

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Code:
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)
    Share on Google+

  5. #5
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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
    Share on Google+

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    You just need to tweak the SQL statement if you're importing into an existing table:

    Code:
    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]"
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)
    Share on Google+

  7. #7
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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
    Share on Google+

  8. #8
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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:
    Code:
    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
    Last edited by Andrew Cushen; 05-23-2006 at 05:32 PM. Reason: security issue
    Share on Google+

  9. #9
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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:
    Code:
    <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
    Last edited by Andrew Cushen; 05-23-2006 at 05:30 PM. Reason: typo
    Share on Google+

  10. #10
    Join Date
    Aug 2006
    Posts
    3
    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!!!!
    Share on Google+

  11. #11
    Join Date
    Apr 2004
    Location
    New York City
    Posts
    538
    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
    Share on Google+

  12. #12
    Join Date
    Aug 2006
    Posts
    3
    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!!
    Share on Google+

Similar Threads

  1. Importing CSV file to data grid
    By software_develo in forum .NET
    Replies: 6
    Last Post: 08-14-2006, 03:50 PM
  2. Replies: 60
    Last Post: 09-13-2002, 06:41 PM
  3. Replies: 2
    Last Post: 01-02-2002, 11:23 AM
  4. Replies: 0
    Last Post: 12-19-2000, 04:14 PM
  5. Replies: 0
    Last Post: 04-17-2000, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center