[RESOLVED] Importing CSV to SQL

[mbutler755]

I am trying to import a CSV file to SQL Server 2005. The CSV file has several fields, but I am not sure of the best way to import them. This is what I have so far.

Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Try
            Dim filename As String = TextBox1.Text
            Dim fields As String()
            Dim delimiter As String = ","
            Using parser As New TextFieldParser(filename)
                parser.SetDelimiters(delimiter)
                While Not parser.EndOfData
                    ' Read in the fields for the current line
                    fields = parser.ReadFields()

                    ' Add code here to use data in fields variable.

                End While
            End Using

        Catch ex As ApplicationException

        End Try

    End Sub

This is the first time I am doing this sort of work. Thus far, I have connected the table that I need to connect to on the SQL server and I have setup an insert command for it.

INSERT INTO dbo.WendysDailyPayroll
                      (UniqueID, Company, FKStoreID, DateOfBusiness, SSN, LawsonID, TotalHours)
VALUES     (@UniqueID,@Company,@FKStoreID,@DateOfBusiness,@SSN,@LawsonID,@TotalHours)

I know that I must setup the fields for the insert command, but I am not sure how to do it. I want to do it all in one shot.

I have to insert the following fields from the CSV file: CompanyNo, StoreNo, Effdate, SSN, LawsonID, TotalHours.

Does anybody have any suggestions on how to do this? Your input is much appreciated.

[jmcilhinney]

To do it that way you would create a DataTable with the appropriate columns and add a row for each line in the file. You'd then use a DataAdapter to save the lot in one go. That said, you'd probably be better off using the SqlBulkCopy class.

[mbutler755]

To do it that way you would create a DataTable with the appropriate columns and add a row for each line in the file. You'd then use a DataAdapter to save the lot in one go. That said, you'd probably be better off using the SqlBulkCopy class.

On average, there are more than 8000 rows. I will look in the SqlBulkCopy class. Do you have any suggestions on the best way to implement it?

According to MSDN, the BulkCopy command requires owner permissions on the database. Additionally, bulkcopy also copies the information in its entirety. There is one field on the database side (UniqueID) that will be generated by the DBMS. Does this change things?

[jmcilhinney]

I've never used it but it's relatively simple, intended for simple scenarios.

[techgnome]

Load the CSV into a datatable. Create the SQLBulkCopy object, set the connection and destination parameters, then call the bulk copy method (I think it's actually called BulkCopy... it's going to be obvious)... pass it your datatable... Now... here comes the gotcha... the source data MUST match the destination table... column names, the types, etc...

What I've done in the past is to create a staging table that maps directly to the file being imported. When I then bulkimport the file, it goes into the staging table. From there I run another query which does data scrubbing, validation, cleanup, etc. And then copy the data out of there into its final table. Then the staging table gets cleared out. It's a bit more effort, but makes things safer incase the data coming in decides to go south on you.

-tg

[mbutler755]

Load the CSV into a datatable. Create the SQLBulkCopy object, set the connection and destination parameters, then call the bulk copy method (I think it's actually called BulkCopy... it's going to be obvious)... pass it your datatable... Now... here comes the gotcha... the source data MUST match the destination table... column names, the types, etc...

What I've done in the past is to create a staging table that maps directly to the file being imported. When I then bulkimport the file, it goes into the staging table. From there I run another query which does data scrubbing, validation, cleanup, etc. And then copy the data out of there into its final table. Then the staging table gets cleared out. It's a bit more effort, but makes things safer incase the data coming in decides to go south on you.

-tg

TG,

Thanks for the reply. I don't think the SQLBulkCopy will work with me because I have a UniqueID field that is being generated by the DBMS. Am I correct on this?

[techgnome]

That's why you dump it into a staging table first.... with out the Unique ID... then when you do your INSERT/SELECT query, you can take that into consideration. It's a common practice.

-tg

[mbutler755]

That's why you dump it into a staging table first.... with out the Unique ID... then when you do your INSERT/SELECT query, you can take that into consideration. It's a common practice.

-tg

TG & Jmic,

Could either of you tell me how to assign the values from the CSV to the SQL table? I think I am going to build the program both ways (I could use the practice).

The SQL server is on this LAN, so I want to see how fast it would be looping through the records. (This was the idea in the original post.) Any ideas on how to do this?

[mbutler755]

TG & Jmic,

Could either of you tell me how to assign the values from the CSV to the SQL table? I think I am going to build the program both ways (I could use the practice).

The SQL server is on this LAN, so I want to see how fast it would be looping through the records. (This was the idea in the original post.) Any ideas on how to do this?

I ended up using SQLBulkCopy. I removed the uniqueidentifier to make it work for now. It works very quickly (about 2 seconds for 8000+ records). Thanks for the ideas.