Import date from CSV to sql table with sqlbulkcopy

I am having trouble with the following code. I want to import CSV data into a sql table. The code I am using is the following:

Code:

---

] Private Sub IMPORTHISTORIESToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles IMPORTHISTORIESToolStripMenuItem.Click
        Dim dt As New DataTable()
        Dim line As String = Nothing
        Dim i As Integer = 0

        Using sr As StreamReader = File.OpenText("C:\Users\Administrator\Desktop\SKEDULERING\EZY Wine Data\skedp_lb.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

   


        Dim SqlconnectionStringBlokkeklaar As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;Integrated Security=True"

        Using connection As New SqlClient.SqlConnection(SqlconnectionStringBlokkeklaar)

            connection.Open()
            Using copy As New SqlBulkCopy(connection)
                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.ColumnMappings.Add(14, 14)
                copy.ColumnMappings.Add(15, 15)
                copy.ColumnMappings.Add(16, 16)
                copy.DestinationTableName = "Ontledings_Vyfjaar_Histories"
                copy.WriteToServer(dt)
            End Using
        End Using




    End Sub

---

When I do the sqlbulkcopy I get the following error : The given value of type String from the data source cannot be converted to type date of the specified target column.

My problem lies with column 10. In my database table it is of type date. In the data table(dt) it is enclosed in double quotes. I presume it is seen as an string. Can someone please help me understand what I am doing wrong with the sqpbulkcopy and possibly my perception of column mappings?

Regards

When you save data to your database, it has to be the type that the database expects. If you're reading data from a text file then it is all Strings to begin with. If the database expects anything other than text (it appears to expect dates in your case, but it might be numbers too) then it's up to you to convert the text you have to that data type before adding it to the DataTable. You can use CDate, Convert.ToDate or Date.Parse but they all assume that the data will be in a valid format understood by the system. It would be far better to use Date.TryParseExact to test whether the text is in the specific format you expect and, if it's not, either use NULL or provide appropriate feedback to the user.

It may actually be that valid data can be converted implicitly and your issue is that you're passing some invalid data. For instance, if you have an empty field in your CSV file then you you'll get an empty String in your DataTable, which cannot be converted to a Date. It's up to you to detect that and set the field to NULL instead, which you do using DBNull.Value, e.g.

vb.net Code:

Dim d As Date
 
If Date.TryParse(myText, d) Then
    myDataRow("Date") = d
Else
    myDataRow("Date") = DBNull.Value
End If

Thank you.So I have inserted your code where the rows gets inserted into the date. However I am not sure if this is correct, as I get an error (red line under data):

Code:

---

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


                    Dim d As Date

                    If Date.TryParse(data, d) Then
                        row.ItemArray("Date") = d
                    Else
                        row.ItemArray("Date") = DBNull.Value
                    End If





                    dt.Rows.Add(row)
                End If
                line = sr.ReadLine()
            Loop
        End Using

---

'data' is a String array in your code. You're not converting the whole array to a Date, just one element of that array.

Don't set the ItemArray and set fields explicitly. Do one or the other. As you have to set this one field explicitly at least, that means setting them all explicitly. Either that or create a new Object array that contains the final data for each field and assign that to the ItemArray property.

Hi jmcilhinney.

Gosh I am struggling with this one. OK so I have set all the fields in my data table explicitly. You are correct I do have zero dates in my csv file ( ," / /",).

I just cannot figure out how to use your code.

My code is :

Code:

---

  Dim datatabel As New DataTable()
        datatabel.Columns.Add(New DataColumn("LidNo", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Lidnaam", GetType(String)))
        datatabel.Columns.Add(New DataColumn("PlaasNo", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Plaasnaam", GetType(String)))
        datatabel.Columns.Add(New DataColumn("BlokNo", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Area", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Alias", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Aliasnaam", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Kult", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Datum", GetType(Date)))
        datatabel.Columns.Add(New DataColumn("Stadium", GetType(String)))
        datatabel.Columns.Add(New DataColumn("Suiker", GetType(Decimal)))
        datatabel.Columns.Add(New DataColumn("pH", GetType(Decimal)))
        datatabel.Columns.Add(New DataColumn("Suur", GetType(Decimal)))
        datatabel.Columns.Add(New DataColumn("Gelewer", GetType(Decimal)))
        datatabel.Columns.Add(New DataColumn("Skatting", GetType(Decimal)))

        Dim parser As New FileIO.TextFieldParser("C:\Users\Administrator\Desktop\SKEDULERING\EZY Wine Data\skedp_lb.csv")
        parser.Delimiters = New String() {","}
        parser.HasFieldsEnclosedInQuotes = True

        parser.TrimWhiteSpace = True

        parser.ReadLine()


        Do Until parser.EndOfData = True
            Dim d As Date


            If Date.TryParse(mytext, d) Then
                '  myDataRow("Date") = d
                datatabel.Rows(0)("Date") = d
            Else
                '  myDataRow("Date") = DBNull.Value.ToString
                datatabel.Rows(0)("Date") = DBNull.Value.ToString
            End If

            datatabel.Rows.Add(parser.ReadFields())

        Loop


    End Sub

---

My issue is with the following :

Code:

---

Do Until parser.EndOfData = True
            Dim d As Date


            If Date.TryParse(mytext, d) Then
                '  myDataRow("Date") = d
                datatabel.Rows(0)("Date") = d
            Else
                '  myDataRow("Date") = DBNull.Value.ToString
                datatabel.Rows(0)("Date") = DBNull.Value.ToString
            End If

            datatabel.Rows.Add(parser.ReadFields())

        Loop

---

What do I use instead of mytext? And is datatabel.Rows(0)("Date") = d and datatabel.Rows(0)("Date") = DBNull.Value.ToString correct ?. I cannot test it as I get an error at mytext.


Thank you.

parser.ReadFields -- this should return a string array with the field values in it... so that's what you need instead of mytext (or you can assign the appropriate array element to mytext).... once you have that array -- don't call it inline with the .Rows.Add -- you can copy over each of the values from the returned array into the appropriate column in the datarow, processing the date as necessary as you go. Once the datarow is filled, you add it to the datatable.


-tg

Hi Techgnome

I have adapted the code as follows :

Code:

---

  Dim parser As New FileIO.TextFieldParser("C:\Users\Administrator\Desktop\SKEDULERING\EZY Wine Data\skedp_lb.csv")
        parser.Delimiters = New String() {","}
        parser.HasFieldsEnclosedInQuotes = True
        parser.TrimWhiteSpace = True
        parser.ReadLine()

        Do Until parser.EndOfData = True
            Dim d As Date

            If Date.TryParse(parser.ReadFields.ToString, d) Then
                datatabel.Rows(0)("Date") = d
            Else
                datatabel.Rows(0)("Date") = DBNull.Value.ToString
            End If

            datatabel.Rows.Add(parser.ReadFields())

        Loop

---

I have changed this to .Tostring otherwise I get the error Cannot convert string() to string

Code:

---

If Date.TryParse(parser.ReadFields.ToString, d) Then

---

Now I get an error : There is no row at position 0.


Could you please refer me to a tutorial for beginners how to handle an empty string ( "/ /") in a CSV file and put it as a NULL date value in a data table ?

Hi GideonE,

try this just to get it to a Datagridview first..
set a Filter if tha Data is to large

Code:

---

Imports System.Data.OleDb


'Dim SQL As String = "Select * From [Adressen.csv] Where AD_Land Like 'D%';"
'Dim SQL As String = "Select * From [Adressen.csv] Where AD_Name1 Like  '" & SearchBox & "%';"
'Dim SearchBox As String
'SearchBox = TextBox1.Text


Public Class Form5

 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim _tb As New DataTable

        Me.DataGridView1.DataSource = _tb

        Dim SrcDir As String = "C:\"
        Dim sConn As String = String.Join(";", New String() { _
          "Provider=Microsoft.Jet.OLEDB.4.0", _
          "Data Source=" & SrcDir, _
          "Extended Properties=""Text; HDR=Yes; FMT=Delimited"""})

     
        Dim SQL As String = "Select * From [Adressen.csv];"

        Using Cn As New OleDbConnection(sConn), ta As New OleDbDataAdapter(SQL, Cn)
            Cn.Open()
            ta.Fill(_tb)
        End Using
    End Sub

'Copy to new Table, I know this is Access you have to change to SQL DB
 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")
        'copy CSV to new Table in Access
        'the CSV/Location is = C:\Adressen.csv
        sSql = "Select * Into CSVImport From [Adressen.csv] IN 'c:\' 'Text;'"
        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
    End Sub
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                    ByVal sSQL As String, _
                                    Optional ByRef ErrMessage As String = Nothing, _
                                    Optional ByVal TransAction As  _
                                    OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function


End Class

---

regards
Chris