CSV batch import into MSSQL

[Scooter Pooter]

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?

[Todd Anglin]

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~

[Scooter Pooter]

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.

[Todd Anglin]

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~

[LisaK]

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

[Scooter Pooter]

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

Thanks!

[Scooter Pooter]

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!

[Ingo Oltmann]

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