CSV batch import into MSSQL

8 posts, 0 answers
  1. Scooter Pooter
    Scooter Pooter avatar
    18 posts
    Member since:
    Dec 2004

    Posted 30 Aug 2006 Link to this post

    Hey. This question has nothing to do with Telerik controls, but I know you all are .NET experts and if anybody has any idea how to help me on this, it would be very much appreciated.

    I have a CSV file that I want to import into an MSSQL table. I am in .NET 1.1 (VB) and I don't think there is a simple and easy way to do this. Please tell me I'm wrong.

    All the Batch-Import-From-CSV routines seem to be set up for .NET 2.0.

    So what I am currently doing is importing the data into a DataSet, then going through each and every individual line of the DataSet and executing an INSERT statement.

    IT'S HORRIBLY SLOW!!!

    Any suggestions?
  2. Todd Anglin
    Todd Anglin avatar
    2040 posts
    Member since:
    Aug 2005

    Posted 30 Aug 2006 Link to this post

    Scooter,

    If you have to do this via a web interface (in other words, you don't have access to the SQL Server and DTS), I think you may be out of luck for finding a better solution with 1.1.

    In 2.0, we have a nice new class in the Framework called SqlBulkCopy (I think). It allows you to pass a datatable and it automatically inserts all of the rows into a named destination table in SqlServer. I've used it very recently to parse a CSV file with email addresses and names and quickly add them to a table and it works like a charm!

    That doesn't improve your situation, I know, but when you are able to upgrade to 2.x you've got some good tools waiting. =)

    Thanks~
  3. Scooter Pooter
    Scooter Pooter avatar
    18 posts
    Member since:
    Dec 2004

    Posted 30 Aug 2006 Link to this post

    Thanks, Todd for your honesty. Needless to say I'm somewhat sad.

    Is there an easy way that you know of to show a progress bar as the CSV data is inserting? I have a client going through hundreds of thousands of records, and it would be nice to tell him how many have been uploaded along the way.

    What I am doing at present is inserting 500, then forwarding the user to another page that says "500 uploaded" where the next 500 are uploaded, and then the user is forwarded to a page that says "1000 uploaded" while the next 500 are uploaded, etc.

    The quantity uploaded (500, 1000, 1500 etc) is passed as a variable in the URL.

    Surely there must be an easier and more secure way to do this, at least?

    Thanks for any additional suggestions.
  4. Todd Anglin
    Todd Anglin avatar
    2040 posts
    Member since:
    Aug 2005

    Posted 30 Aug 2006 Link to this post

    Scooter,

    Have you looked at the upload Progress Area? It's been decoupled from the upload control so that you can use it to indicate progress for any measurable activity. Take a look at the demo here:

    http://www.telerik.com/r.a.d.controls/Upload/Examples/
    MonitoringCustomProgress/DefaultCS.aspx


    If you don't like that option, you could also take a look at the r.a.d.ajax timer. Using the timer, you could poll the server at regular intervals and update the page progress indicators using ajax panel or manager. I'm not sure if you'll have to consider using multiple threads to avoid page timeouts (are multiple threads available in .NET 1.x? It's been too long for me to remember...sorry...), but the timer may help with that problem. You can find a demo of ajax timer here:

    http://www.telerik.com/r.a.d.controls/Ajax/Examples/
    AjaxTimer/TimerInPanel/DefaultCS.aspx


    Hope this helps!

    Thanks~
  5. LisaK
    LisaK avatar
    1 posts
    Member since:
    Dec 2005

    Posted 30 Aug 2006 Link to this post

    Scooter,

    Here's some code i've been using for awhile to do bulk inserts from excel to sql. i'm sure it can be modified further to work better, but it might be a good starting point.

    Public Function readExcelSheet(ByVal cExcelFileName As String, ByVal strQuery As String) As DataTable

    'open spreadsheet and return datatable based on query passed

    Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & cExcelFileName & """;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

    Dim Adapter As New OleDbDataAdapter(strQuery, strCon)

    Dim dt As New DataTable

    Adapter.Fill(dt)

    Adapter.Dispose()

    Return dt

    End Function

    Public Sub ImportJob_ReadFile(ByVal cExcelFileName As String, ByVal cSQLCon As String)

    Dim dtTempTable As New DataTable

    Dim ds As New DataSet

    Dim myConnection As New SqlConnection(cSQLCon)

    'query that is used to create empty dataset

    Dim strSQL As String = "SELECT table1.Field1, table1.Field2 FROM table1 WHERE (((table1.JobId)=Parameter1))"

    'return datatable from spreadsheet

    dtTempTable = readExcelSheet(cExcelFileName, "SELECT * FROM [Sheet1$]")

    Dim drTempRow As DataRow

    Dim myAdapter As New SqlDataAdapter(strSQL, myConnection)

    Dim myCB As New SqlCommandBuilder(myAdapter)

    myAdapter.SelectCommand.CommandType = CommandType.Text

    myCB.RefreshSchema()

    myAdapter.Fill(ds, "tmpImp")

    'loop through all the rows in the excel datatable

    For Each drTempRow In dtTempTable.Rows()

    Dim dr As DataRow = ds.Tables("tmpImp").NewRow()

    'set field values

    dr("Field1") = drTempRow("Field1")

    dr("Field2") = drTempRow("Field2")

    'add row to dataset

    ds.Tables("tmpImpJob").Rows.Add(dr)

    Next

    'dataset to hold changes

    Dim ds1 As DataSet

    If ds.HasChanges() Then

    'get the new rows

    ds1 = ds.GetChanges()

    'update original table (the one used in sqlSQL above)

    myAdapter.Update(ds1, "tmpImp")

    End If

    myConnection.Close()

    End Sub



  6. Scooter Pooter
    Scooter Pooter avatar
    18 posts
    Member since:
    Dec 2004

    Posted 30 Aug 2006 Link to this post

    Thanks Mike. I will look into this method. It's different than what I am currently doing.

    Thanks!
  7. Scooter Pooter
    Scooter Pooter avatar
    18 posts
    Member since:
    Dec 2004

    Posted 31 Aug 2006 Link to this post

    Mike, Thanks for the code suggestion. It worked much better than inserting rows to the database line by line! This cut my time down from 7 or 8 minutes to about 15 seconds.

    Many thanks!
  8. Ingo Oltmann
    Ingo Oltmann avatar
    44 posts
    Member since:
    Nov 2005

    Posted 14 Sep 2006 Link to this post

    Hi Scooter Pooter,

    I had exactly the same problem a while ago. I coded a windows services which monitors a directory (in my case an ftp directory) and if a new csv file is available, the service bulk imports it to the database and delete or backup the csv file. I did it as a service because it has to run without a user logged in on the server.
    Please let me know, if your problem still exists. I could send you some code.

    Regards,
    Ingo
Back to Top