dcsimg
Results 1 to 9 of 9

Thread: [RESOLVED] Importing CSV to SQL

  1. #1

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Resolved [RESOLVED] Importing CSV to SQL

    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.

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

    Code:
    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.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,879

    Re: Importing CSV to SQL

    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.

  3. #3

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: Importing CSV to SQL

    Quote Originally Posted by jmcilhinney View Post
    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?
    Last edited by mbutler755; Apr 9th, 2010 at 09:50 AM. Reason: Added more info
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,879

    Re: Importing CSV to SQL

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

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,677

    Re: Importing CSV to SQL

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: Importing CSV to SQL

    Quote Originally Posted by techgnome View Post
    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?
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,677

    Re: Importing CSV to SQL

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: Importing CSV to SQL

    Quote Originally Posted by techgnome View Post
    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?
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  9. #9

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: Importing CSV to SQL

    Quote Originally Posted by mbutler755 View Post
    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.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width