none
Write Data from CSV to SQL Server Table. Issue with Data Type. RRS feed

  • Question

  • A short time ago, I got a great tip from Kevininstructor.  He turned me on to this site:
    http://www.dotnetcurry.com/ShowArticle.aspx?ID=323

    I leveraged that concept; this is my code:

        Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click

            Dim dt As New DataTable()
            Dim line As String = Nothing
            Dim i As Integer = 0

            Using sr As StreamReader = File.OpenText("C:\Users\Excel\Desktop\OrdersTest.csv")
                line = sr.ReadLine()
                Do While line IsNot Nothing
                    Dim data() As String = line.Split(","c)
                    If data.Length > 0 Then
                        If i = 0 Then
                            For Each item In data
                                dt.Columns.Add(New DataColumn())
                            Next item
                            i += 1
                        End If
                        Dim row As DataRow = dt.NewRow()
                        row.ItemArray = data
                        dt.Rows.Add(row)
                    End If
                    line = sr.ReadLine()
                Loop
            End Using

            'Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConsoleApplication3.Properties.Settings.daasConnectionString").ConnectionString)
            Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;")
                    cn.Open()
                    Using copy As New SqlBulkCopy(cn)
                        copy.ColumnMappings.Add(0, 0)
                        copy.ColumnMappings.Add(1, 1)
                        copy.ColumnMappings.Add(2, 2)
                        copy.ColumnMappings.Add(3, 3)
                        copy.ColumnMappings.Add(4, 4)
                        copy.ColumnMappings.Add(5, 5)
                        copy.ColumnMappings.Add(6, 6)
                        copy.ColumnMappings.Add(7, 7)
                        copy.ColumnMappings.Add(8, 8)
                        copy.ColumnMappings.Add(9, 9)
                        copy.ColumnMappings.Add(10, 10)
                        copy.ColumnMappings.Add(11, 11)
                        copy.ColumnMappings.Add(12, 12)
                    copy.ColumnMappings.Add(13, 13)
                    copy.DestinationTableName = "[Orders]"
                        copy.WriteToServer(dt)
                    End Using
                End Using

        End Sub

    The code errors out on this line:  
    copy.WriteToServer(dt)
    Error Message: 'The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.'
    Here is an image of the design of the table:

    This is AT LEAST the 4th error that I got.  I got errors about datetime, int, nvarchar, etc.  Is there a simple way to ensure that this will ALWAYS work.  I've tried many different things, and it still doesn't work.  I can fiddle with it more, and maybe I will get it to work, or maybe not.  I'm wondering how most people do this though.  Most people can't spend hours and hours making sure each and every Data Type is correct, to ensure the process will run smoothly, right.  Isn't there something simple that I can do to the CSV file to ensure that this will  work (I know CSV files don't allow formatting)?  Or, is there some simple line of code that I can write to tie everything together, to make sure the Fields are of the correct data type???
    Thanks everyone!!!

     

    Ryan Shuell

    Friday, February 22, 2013 12:49 AM

Answers

  • Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
    ' Define the Column Definition                             
    Dim dt As New DataTable()
    dt.Columns.Add("OrderID", GetType(Integer))
                                dt.Columns.Add("CustomerID", GetType(String))
                                dt.Columns.Add("EmployeeID", GetType(Integer))
                                dt.Columns.Add("OrderDate", GetType(Date))
                                dt.Columns.Add("RequiredDate", GetType(Date))
                                dt.Columns.Add("ShippedDate", GetType(Date))
                                dt.Columns.Add("ShipVia", GetType(Integer))
                                dt.Columns.Add("Freight", GetType(Decimal))
                                dt.Columns.Add("ShipName", GetType(String))
                                dt.Columns.Add("ShipAddress", GetType(String))
                                dt.Columns.Add("ShipCity", GetType(String))
                                dt.Columns.Add("ShipRegion", GetType(String))
                                dt.Columns.Add("ShipPostalCode", GetType(String))
                                dt.Columns.Add("ShipCountry", GetType(String))
    Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;")
                            cn.Open()
            Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
            Dim currentRow As String()
            Dim dr As DataRow
            Dim sqlColumnDataType As String
            reader = My.Computer.FileSystem.OpenTextFieldParser("C:\Users\Excel\Desktop\OrdersTest.csv")
            reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            reader.Delimiters = New String() {","}
            While Not reader.EndOfData
                Try
                    currentRow = reader.ReadFields()
                    dr = dt.NewRow()
                    For currColumn = 0 To dt.Columns.Count - 1
     sqlColumnDataType = dt.Columns(currColumn).DataType.Name
                        Select Case sqlColumnDataType
                            Case "String"
                                If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                    dr.Item(currColumn) = ""
                                Else
                                    dr.Item(currColumn) = Convert.ToString(currentRow(currColumn))
                                End If
                            Case "Decimal"
                                If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                    dr.Item(currColumn) = 0
                                Else
                                    dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn))
                                End If
                            Case "DateTime"
                                If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                    dr.Item(currColumn) = ""
                                Else
                                    dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn))
                                End If
                        End Select
    next
    dt.Rows.Add(dr)
    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.")
                    reader.Close()
                    reader.Dispose()
                    Return False
                Finally
                    dr = Nothing
    End Try
    End While
    Using copy As New SqlBulkCopy(cn)
        copy.DestinationTableName = "[dbo].[Orders]"
        copy.WriteToServer(dt)
    End Using
    End Using
       
    End Sub
    Try the corrected one

    • Edited by sendilg Sunday, February 24, 2013 6:52 AM
    • Marked as answer by ryguy72 Sunday, February 24, 2013 8:50 PM
    Sunday, February 24, 2013 6:23 AM
  • Ryan Shuell

    Find the bold lines added to skip the no of rows from the CSV.

    Related the problem in the previous post, I hadnt used VStudio to check the program, i used a it was due to sample source from MSDN & modified for your requirement.

    Actually in your code

    1. The CSV file name was directly used but it was referred as CSVFileName

    2. DataTable was declared as dt but i have used CSVTable

    3. SQLConnection (cn) was out of scope where it has been used since it was inside Using Block

    Find the modified code

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click ' Define the Column Definition Dim dt As New DataTable() dt.Columns.Add("OrderID", GetType(Integer)) dt.Columns.Add("CustomerID", GetType(String)) dt.Columns.Add("EmployeeID", GetType(Integer)) dt.Columns.Add("OrderDate", GetType(Date)) dt.Columns.Add("RequiredDate", GetType(Date)) dt.Columns.Add("ShippedDate", GetType(Date)) dt.Columns.Add("ShipVia", GetType(Integer)) dt.Columns.Add("Freight", GetType(Decimal)) dt.Columns.Add("ShipName", GetType(String)) dt.Columns.Add("ShipAddress", GetType(String)) dt.Columns.Add("ShipCity", GetType(String)) dt.Columns.Add("ShipRegion", GetType(String)) dt.Columns.Add("ShipPostalCode", GetType(String)) dt.Columns.Add("ShipCountry", GetType(String)) Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;") cn.Open() Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser Dim currentRow As String() Dim dr As DataRow Dim sqlColumnDataType As String reader = My.Computer.FileSystem.OpenTextFieldParser("C:\Users\Excel\Desktop\OrdersTest.csv") reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited reader.Delimiters = New String() {","} While Not reader.EndOfData Try ' Lines to skip If reader.LineNumber <= 2 Then reader.ReadLine() Continue While End If currentRow = reader.ReadFields() dr = dt.NewRow() For currColumn = 0 To dt.Columns.Count - 1 sqlColumnDataType = dt.Columns(currColumn).DataType.Name Select Case sqlColumnDataType Case "String" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = "" Else dr.Item(currColumn) = Convert.ToString(currentRow(currColumn)) End If Case "Decimal" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = 0 Else dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn)) End If Case "DateTime" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = "" Else dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn)) End If End Select Next dt.Rows.Add(dr) Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.") reader.Close() reader.Dispose() Finally dr = Nothing End Try End While Using copy As New SqlBulkCopy(cn) copy.DestinationTableName = "[dbo].[Orders]" copy.WriteToServer(dt) End Using End Using

    Feel free to ask questions, forum will support you ..

    It all Happenz Sendil

    • Marked as answer by ryguy72 Tuesday, February 26, 2013 4:57 AM
    Monday, February 25, 2013 4:58 AM

All replies

  • While creating the datatable where you are setting the value of the CSV,define the datatype of each column. Convert the type of the CSV field values before assingning it to the datatable.

     Refer the following post for code samples http://social.msdn.microsoft.com/Forums/ta/winformsdatacontrols/thread/34b6a1e8-5103-42a3-aa45-cdc0cea461f2


    It all Happenz Sendil

    Friday, February 22, 2013 2:32 AM
  • That seems pretty much like what I was looking for.  However, I tried the code below, and I'm still getting an error:

                Using copy As New SqlBulkCopy(cn)
                    dt.Columns.Add("OrderID", GetType(Integer))
                    dt.Columns.Add("CustomerID", GetType(String))
                    dt.Columns.Add("EmployeeID", GetType(Integer))
                    dt.Columns.Add("OrderDate", GetType(Date))
                    dt.Columns.Add("RequiredDate", GetType(Date))
                    dt.Columns.Add("ShippedDate", GetType(Date))
                    dt.Columns.Add("ShipVia", GetType(Integer))
                    dt.Columns.Add("Freight", GetType(Decimal))
                    dt.Columns.Add("ShipName", GetType(String))
                    dt.Columns.Add("ShipAddress", GetType(String))
                    dt.Columns.Add("ShipCity", GetType(String))
                    dt.Columns.Add("ShipRegion", GetType(String))
                    dt.Columns.Add("ShipPostalCode", GetType(String))
                    dt.Columns.Add("ShipCountry", GetType(String))
                    copy.DestinationTableName = "[dbo].[Orders]"
                    copy.WriteToServer(dt)
                End Using

    The error is on this line:

    copy.WriteToServer(dt)

    Error Message: 'The given ColumnMapping does not match up with any column in the source or destination.'

    I'm pretty sure they all match up!!  I triple checked it!!

    What could be the problem now?


    Ryan Shuell

    Friday, February 22, 2013 3:30 AM
  • Hi Ryan Shuell,

    You have done only part of what i told you to do

    1. Define the datatype of each column. - OK

    2. Convert the type of the CSV field values before assingning it to the datatable. - Not Done

    Related to the current Issue:

    It seems that the datatable doesn't match the column definition of the target SQL table.

    Cross verify the datatable column definition with the SQL Table details. Keep in mind

    ColumnMappings are case-sensitive.

    Revert back in case of further details..


    It all Happenz Sendil

    Friday, February 22, 2013 2:56 PM
  • Ok, I tried a few more things.  It's still not coming together for me.  I'm working with this code sample now.

        Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click

            Dim dt As New DataTable()
            Dim line As String = Nothing
            Dim i As Integer = 0

            Using sr As StreamReader = File.OpenText("C:\Users\Excel\Desktop\OrdersTest.csv")
                line = sr.ReadLine()
                Do While line IsNot Nothing
                    Dim data() As String = line.Split(","c)
                    If data.Length > 0 Then
                        If i = 0 Then
                            For Each item In data
                                dt.Columns.Add(New DataColumn())
                            Next item
                            i += 1
                        End If
                        Dim row As DataRow = dt.NewRow()
                        row.ItemArray = data
                        dt.Rows.Add(row)
                    End If
                    line = sr.ReadLine()
                Loop
            End Using

            'Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConsoleApplication3.Properties.Settings.daasConnectionString").ConnectionString)

            Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
            Dim currentRow As String()
            Dim dr As DataRow
            Dim sqlColumnDataType As String
            reader = My.Computer.FileSystem.OpenTextFieldParser(CSVFileName)
            reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            reader.Delimiters = New String() {","}
            While Not reader.EndOfData
                Try
                    currentRow = reader.ReadFields()
                    dr = CSVTable.NewRow()
                    For currColumn = 0 To CSVTable.Columns.Count - 1

                        Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;")
                            cn.Open()
                            Using copy As New SqlBulkCopy(cn)
                                'currentRow = reader.ReadFields()
                                'dr = CSVTable.NewRow()
                                dr.Item(currColumn) = Convert.ToInteger(currentRow(currColumn))
                                dt.Columns.Add("OrderID", GetType(Integer))
                                dt.Columns.Add("CustomerID", GetType(String))
                                dt.Columns.Add("EmployeeID", GetType(Integer))
                                dt.Columns.Add("OrderDate", GetType(Date))
                                dt.Columns.Add("RequiredDate", GetType(Date))
                                dt.Columns.Add("ShippedDate", GetType(Date))
                                dt.Columns.Add("ShipVia", GetType(Integer))
                                dt.Columns.Add("Freight", GetType(Decimal))
                                dt.Columns.Add("ShipName", GetType(String))
                                dt.Columns.Add("ShipAddress", GetType(String))
                                dt.Columns.Add("ShipCity", GetType(String))
                                dt.Columns.Add("ShipRegion", GetType(String))
                                dt.Columns.Add("ShipPostalCode", GetType(String))
                                dt.Columns.Add("ShipCountry", GetType(String))
                                copy.DestinationTableName = "[dbo].[Orders]"
                                copy.WriteToServer(dt)
                            End Using
                        End Using

                    Next
                    CSVTable.Rows.Add(dr)
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.")
                    reader.Close()
                    reader.Dispose()
                    Return False
                Finally
                    dr = Nothing
                End Try
            End While
        End Sub


    As you can see, I'm doing the conversion now.  Here's one sample.

    dr.Item(currColumn) = Convert.ToInteger(currentRow(currColumn))

    Error Message:  'ToInteger' is not a member of 'System.Convert'.

    What am I doing wrong now???


    Ryan Shuell

    Saturday, February 23, 2013 5:58 AM
  • Hi Ryan Shuell

    I have corrected your code, check it out.

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click Dim line As String = Nothing Dim i As Integer = 0 Using sr As StreamReader = File.OpenText("C:\Users\Excel\Desktop\OrdersTest.csv") line = sr.ReadLine() Do While line IsNot Nothing Dim data() As String = line.Split(","c) If data.Length > 0 Then If i = 0 Then For Each item In data dt.Columns.Add(New DataColumn()) Next item i += 1 End If Dim row As DataRow = dt.NewRow() row.ItemArray = data dt.Rows.Add(row) End If line = sr.ReadLine() Loop End Using ' Define the Column Definition Dim dt As New DataTable() dt.Columns.Add("OrderID", GetType(Integer)) dt.Columns.Add("CustomerID", GetType(String)) dt.Columns.Add("EmployeeID", GetType(Integer)) dt.Columns.Add("OrderDate", GetType(Date)) dt.Columns.Add("RequiredDate", GetType(Date)) dt.Columns.Add("ShippedDate", GetType(Date)) dt.Columns.Add("ShipVia", GetType(Integer)) dt.Columns.Add("Freight", GetType(Decimal)) dt.Columns.Add("ShipName", GetType(String)) dt.Columns.Add("ShipAddress", GetType(String)) dt.Columns.Add("ShipCity", GetType(String)) dt.Columns.Add("ShipRegion", GetType(String)) dt.Columns.Add("ShipPostalCode", GetType(String)) dt.Columns.Add("ShipCountry", GetType(String)) Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;") cn.Open() Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser Dim currentRow As String() Dim dr As DataRow Dim sqlColumnDataType As String reader = My.Computer.FileSystem.OpenTextFieldParser(CSVFileName) reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited reader.Delimiters = New String() {","} While Not reader.EndOfData Try currentRow = reader.ReadFields() dr = dt.NewRow() For currColumn = 0 To dt.Columns.Count - 1 sqlColumnDataType = CSVTable.Columns(currColumn).DataType.Name Select Case sqlColumnDataType Case "String" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = "" Else dr.Item(currColumn) = Convert.ToString(currentRow(currColumn)) End If Case "Decimal" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = 0 Else dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn)) End If Case "DateTime" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = "" Else dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn)) End If End Select next dt.Rows.Add(dr) End While End Using Using copy As New SqlBulkCopy(cn) copy.DestinationTableName = "[dbo].[Orders]" copy.WriteToServer(dt) End Using Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.") reader.Close() reader.Dispose() Return False Finally dr = Nothing End Try End Sub



    It all Happenz Sendil

    Sunday, February 24, 2013 4:22 AM
  • Thanks again, sendilg.  You know, there is still something wrong with this, and I can’t seem to figure it out.  For one thing, the Try Catch Finally was not correct, due to the Using…End Using.  I fixed that, but I still have a few errors, which is preventing this from running.

     

    Descriptive images below:

    3 of the errors are:

    'CSVFileName' is not declared. It may be inaccessible due to its protection level’

    1 of the errors is:

    'cn' is not declared. It may be inaccessible due to its protection level’

     

    There must be an easy solution for this, right.

     

     

    Thanks for helping me to get this up and running!!

     


    Ryan Shuell

    Sunday, February 24, 2013 5:49 AM
  • Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
    ' Define the Column Definition                             
    Dim dt As New DataTable()
    dt.Columns.Add("OrderID", GetType(Integer))
                                dt.Columns.Add("CustomerID", GetType(String))
                                dt.Columns.Add("EmployeeID", GetType(Integer))
                                dt.Columns.Add("OrderDate", GetType(Date))
                                dt.Columns.Add("RequiredDate", GetType(Date))
                                dt.Columns.Add("ShippedDate", GetType(Date))
                                dt.Columns.Add("ShipVia", GetType(Integer))
                                dt.Columns.Add("Freight", GetType(Decimal))
                                dt.Columns.Add("ShipName", GetType(String))
                                dt.Columns.Add("ShipAddress", GetType(String))
                                dt.Columns.Add("ShipCity", GetType(String))
                                dt.Columns.Add("ShipRegion", GetType(String))
                                dt.Columns.Add("ShipPostalCode", GetType(String))
                                dt.Columns.Add("ShipCountry", GetType(String))
    Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;")
                            cn.Open()
            Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
            Dim currentRow As String()
            Dim dr As DataRow
            Dim sqlColumnDataType As String
            reader = My.Computer.FileSystem.OpenTextFieldParser("C:\Users\Excel\Desktop\OrdersTest.csv")
            reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            reader.Delimiters = New String() {","}
            While Not reader.EndOfData
                Try
                    currentRow = reader.ReadFields()
                    dr = dt.NewRow()
                    For currColumn = 0 To dt.Columns.Count - 1
     sqlColumnDataType = dt.Columns(currColumn).DataType.Name
                        Select Case sqlColumnDataType
                            Case "String"
                                If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                    dr.Item(currColumn) = ""
                                Else
                                    dr.Item(currColumn) = Convert.ToString(currentRow(currColumn))
                                End If
                            Case "Decimal"
                                If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                    dr.Item(currColumn) = 0
                                Else
                                    dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn))
                                End If
                            Case "DateTime"
                                If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                    dr.Item(currColumn) = ""
                                Else
                                    dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn))
                                End If
                        End Select
    next
    dt.Rows.Add(dr)
    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.")
                    reader.Close()
                    reader.Dispose()
                    Return False
                Finally
                    dr = Nothing
    End Try
    End While
    Using copy As New SqlBulkCopy(cn)
        copy.DestinationTableName = "[dbo].[Orders]"
        copy.WriteToServer(dt)
    End Using
    End Using
       
    End Sub
    Try the corrected one

    • Edited by sendilg Sunday, February 24, 2013 6:52 AM
    • Marked as answer by ryguy72 Sunday, February 24, 2013 8:50 PM
    Sunday, February 24, 2013 6:23 AM
  • Yeap, yeap.  That didn't work with the headers in the first row of the CSV file.  However when I removed the headers, it worked perfect.  I think the date headers were throwing an errors.  Is there 1 or 2 lines of code that I can add to ignore the headers, or just start on row 2?  There is nothing obvious to me that says start on row 1.

    What was wrong last time?  What was the problem with the 'CSVFileName', 'CSVTable', and the 'cn'?

    Thanks for seeing this through to the end.  Just yesterday I developed a MS Word Macro to help a guy import email addresses from thousands of Word files in a folder.  I don't really program in MS Word, but I was able to figure it out.  It was a good learning experience for me, and I know automating the task saved him a TON of time.  I certainly know what it's like to be on the receiving-end of a helping situation.


    Ryan Shuell

    Sunday, February 24, 2013 8:58 PM
  • Ryan Shuell

    Find the bold lines added to skip the no of rows from the CSV.

    Related the problem in the previous post, I hadnt used VStudio to check the program, i used a it was due to sample source from MSDN & modified for your requirement.

    Actually in your code

    1. The CSV file name was directly used but it was referred as CSVFileName

    2. DataTable was declared as dt but i have used CSVTable

    3. SQLConnection (cn) was out of scope where it has been used since it was inside Using Block

    Find the modified code

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click ' Define the Column Definition Dim dt As New DataTable() dt.Columns.Add("OrderID", GetType(Integer)) dt.Columns.Add("CustomerID", GetType(String)) dt.Columns.Add("EmployeeID", GetType(Integer)) dt.Columns.Add("OrderDate", GetType(Date)) dt.Columns.Add("RequiredDate", GetType(Date)) dt.Columns.Add("ShippedDate", GetType(Date)) dt.Columns.Add("ShipVia", GetType(Integer)) dt.Columns.Add("Freight", GetType(Decimal)) dt.Columns.Add("ShipName", GetType(String)) dt.Columns.Add("ShipAddress", GetType(String)) dt.Columns.Add("ShipCity", GetType(String)) dt.Columns.Add("ShipRegion", GetType(String)) dt.Columns.Add("ShipPostalCode", GetType(String)) dt.Columns.Add("ShipCountry", GetType(String)) Using cn = New SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;") cn.Open() Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser Dim currentRow As String() Dim dr As DataRow Dim sqlColumnDataType As String reader = My.Computer.FileSystem.OpenTextFieldParser("C:\Users\Excel\Desktop\OrdersTest.csv") reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited reader.Delimiters = New String() {","} While Not reader.EndOfData Try ' Lines to skip If reader.LineNumber <= 2 Then reader.ReadLine() Continue While End If currentRow = reader.ReadFields() dr = dt.NewRow() For currColumn = 0 To dt.Columns.Count - 1 sqlColumnDataType = dt.Columns(currColumn).DataType.Name Select Case sqlColumnDataType Case "String" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = "" Else dr.Item(currColumn) = Convert.ToString(currentRow(currColumn)) End If Case "Decimal" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = 0 Else dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn)) End If Case "DateTime" If String.IsNullOrEmpty(currentRow(currColumn)) Then dr.Item(currColumn) = "" Else dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn)) End If End Select Next dt.Rows.Add(dr) Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.") reader.Close() reader.Dispose() Finally dr = Nothing End Try End While Using copy As New SqlBulkCopy(cn) copy.DestinationTableName = "[dbo].[Orders]" copy.WriteToServer(dt) End Using End Using

    Feel free to ask questions, forum will support you ..

    It all Happenz Sendil

    • Marked as answer by ryguy72 Tuesday, February 26, 2013 4:57 AM
    Monday, February 25, 2013 4:58 AM
  • Perfect!!  Thanks so much!!  Ya know, sometimes I can see the solution when I look at the problem for a short time; sometimes I can't see it.  I couldn't see it thins time.  Thanks for helping me to get this thing straightened out!!


    Ryan Shuell

    Tuesday, February 26, 2013 5:15 AM