VS 2010 Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010-VBForums
Results 1 to 8 of 8

Thread: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

  1. #1

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    53

    Question Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    I need to import records from a comma delimited CSV file into an existing table in SQL Server R2 database using Visual Basic .Net 2010. Existing records in the table are to be deleted prior to import. I have been able to create an in-memory temporary DataTable and populate the records from CSV file using TextFieldParser. I have checked it by binding the in-memory DataTable to a DataGridView. But I am clueless in the second part i.e. how to insert records into the SQL table from the in-memory DataTable.

    I have done the following:
    Code:
            Dim TextFileReader As New TextFieldParser("C:\csvtosql\StockVB\VFPFiles\ExpSysusers.csv")
            
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(",")
    
            Dim TextFileTable As DataTable = Nothing
    
            Dim Column As DataColumn
            Dim Row As DataRow
            Dim UpperBound As Int32
            Dim ColumnCount As Int32
            Dim CurrentRow As String()
    
            Dim intCount As Integer
            intCount = 0
            CurrentRow = TextFileReader.ReadFields() ' Ignore the header
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
    
                    If Not CurrentRow Is Nothing Then
                        ''# Check if DataTable has been created
                        If TextFileTable Is Nothing Then
                            TextFileTable = New DataTable("TextFileTable")
                            ''# Get number of columns
                            UpperBound = CurrentRow.GetUpperBound(0)
                            ''# Create new DataTable
                            For ColumnCount = 0 To UpperBound
                                Column = New DataColumn()
                                Column.DataType = System.Type.GetType("System.String")
                                Column.ColumnName = "Column" & ColumnCount
                                Column.Caption = "Column" & ColumnCount
                                Column.ReadOnly = True
                                Column.Unique = False
                                TextFileTable.Columns.Add(Column)
                            Next
                        End If
                        intCount = intCount + 1
                        Row = TextFileTable.NewRow
                        For ColumnCount = 0 To UpperBound
                            Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                        Next
                        TextFileTable.Rows.Add(Row)
    
                    End If
    
                Catch ex As Exception
                    MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
            TextFileReader.Dispose()
            DataGridView1.DataSource = TextFileTable
            MsgBox(intCount & " Records Read.")
    Can anybody please help/guide me??

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

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    Use a data adapter to save all the data in the DataTable to the database. For an example, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    53

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.

    Code:
    For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
            Dim Column0 As String = TextFileTableDataRow("Column0")
            Dim Column1 As String = TextFileTableDataRow("Column1")
            Dim Column2 As Int16 = TextFileTableDataRow("Column2")
            Dim Column3 As Boolean = TextFileTableDataRow("Column3")
    
            Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
            Dim SqlconnectionString As String = gcconnect
    
            Using connection As New SqlClient.SqlConnection(SqlconnectionString)
                Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)
    
                ' create command objects and add parameters
                With cmd.Parameters
                    .AddWithValue("@Operator", Column0)
                    .AddWithValue("@OpPassword", Column1)
                    .AddWithValue("@SecurityLevel", Column3)
                    .AddWithValue("@LoggedOn", Column7)
                End With
    
                Dim adapter As New SqlClient.SqlDataAdapter()
                adapter.InsertCommand = cmd
    
                '--Update the original SQL table from the datatable
                Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
            End Using
        Next
    I am getting the following error: "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,096

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    Fastest way is to use the SQLBulkCopy class and the WriteToServer method...

    -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??? *

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,541

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    Quote Originally Posted by Eager_Beever View Post
    I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.

    Code:
    For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
            Dim Column0 As String = TextFileTableDataRow("Column0")
            Dim Column1 As String = TextFileTableDataRow("Column1")
            Dim Column2 As Int16 = TextFileTableDataRow("Column2")
            Dim Column3 As Boolean = TextFileTableDataRow("Column3")
    
            Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
            Dim SqlconnectionString As String = gcconnect
    
            Using connection As New SqlClient.SqlConnection(SqlconnectionString)
                Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)
    
                ' create command objects and add parameters
                With cmd.Parameters
                    .AddWithValue("@Operator", Column0)
                    .AddWithValue("@OpPassword", Column1)
                    .AddWithValue("@SecurityLevel", Column3)
                    .AddWithValue("@LoggedOn", Column7)
                End With
    
                Dim adapter As New SqlClient.SqlDataAdapter()
                adapter.InsertCommand = cmd
    
                '--Update the original SQL table from the datatable
                Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
            End Using
        Next
    I am getting the following error: "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."
    You don't call Update in a loop. The whole point of a data adapter is to save an entire DataTable worth of changes in one go. Did you read my CodeBank thread? If not, why not? If so, why are you not doing what it does?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    53

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    @jmcilhinney. I am sorry. I did went through your CodeBank link but got confused between handling a DataTable (temporary in-memory table) and a regular (physical) Table in a Database. I re-read the thread again and will now try to follow your lead. Now my aim is to read the DataTable (all records) and insert the records into the table. Hopefully I will be able to solve my problem. I think I am also facing problem due to the presence of a primary key in the target table. But my input DataTable contains unique records only.

    Sorry again for being a pain. I am a newbie in VB.Net and also facing health issues (Thyroid induced difficulty in concentration and a little memory lapse).

    Thanks again for your patience.

  7. #7

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    53

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    @jmcilhinney. I followed your example code #7 in your CodeBank (Retrieving and Saving Data in Databases) and did the following:

    Code:
    For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
                Dim Column0 As String = TextFileTableDataRow("Column0")
                Dim Column1 As String = TextFileTableDataRow("Column1")
                Dim Column2 As String = TextFileTableDataRow("Column2")
                Dim Column3 As Int16 = TextFileTableDataRow("Column3")
                Dim Column4 As String = TextFileTableDataRow("Column4")
                Dim Column5 As Int16 = TextFileTableDataRow("Column5")
                Dim Column6 As Boolean = TextFileTableDataRow("Column6")
                Dim Column7 As Boolean = TextFileTableDataRow("Column7")
    
                Dim SqlconnectionString As String = gcconnect
                Using connection As New SqlClient.SqlConnection(SqlconnectionString)
                    Using command As New SqlCommand("INSERT INTO Personnel ([Operator], OpPassword, OpName, SecurityLevel, BriefCode, Branch, Active, LoggedOn) VALUES (@Operator, @OpPassword, @OpName, @SecurityLevel, @BriefCode, @Branch, @Active, @LoggedOn)", connection)
                        command.Parameters.AddWithValue("@Operator", Column0)
                        command.Parameters.AddWithValue("@OpPassword", Column1)
                        command.Parameters.AddWithValue("@OpName", Column2)
                        command.Parameters.AddWithValue("@SecurityLevel", Column3)
                        command.Parameters.AddWithValue("@BriefCode", Column4)
                        command.Parameters.AddWithValue("@Branch", Column5)
                        command.Parameters.AddWithValue("@Active", Column6)
                        command.Parameters.AddWithValue("@LoggedOn", Column7)
    
                        connection.Open()
                        command.ExecuteNonQuery()
                    End Using
                End Using
            Next
    Now my code is working OK, though I know it needs refining. I also noticed that the SQL Table field name "Operator" is a reserved word in Visual Basic and therefore I enclosed it within square braces.

    Is there a better approach (more efficient) to do the same??

    Thanks again for your patience and guidance.

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,541

    Re: Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

    That is NOT what you should be doing. You should be following one of the examples in post #3 of that thread. You populate a DataTable with ALL the data and you save it in a single batch. That post in that thread includes an example using a data adapter and another using SqlBulkCopy, which tg recommended earlier. There's no real advantage unless you have a large amount of data though.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

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

Survey posted by VBForums.