VS 2015 Import date from CSV to sql table with sqlbulkcopy-VBForums
Results 1 to 8 of 8

Thread: Import date from CSV to sql table with sqlbulkcopy

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    13

    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

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

    Re: Import date from CSV to sql table with sqlbulkcopy

    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:
    1. Dim d As Date
    2.  
    3. If Date.TryParse(myText, d) Then
    4.     myDataRow("Date") = d
    5. Else
    6.     myDataRow("Date") = DBNull.Value
    7. End If
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    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
    New Member
    Join Date
    Nov 2016
    Posts
    13

    Re: Import date from CSV to sql table with sqlbulkcopy

    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

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

    Re: Import date from CSV to sql table with sqlbulkcopy

    '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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    13

    Re: Import date from CSV to sql table with sqlbulkcopy

    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.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,453

    Re: Import date from CSV to sql table with sqlbulkcopy

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

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    13

    Re: Import date from CSV to sql table with sqlbulkcopy

    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 ?

  8. #8
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    956

    Re: Import date from CSV to sql table with sqlbulkcopy

    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
    Last edited by ChrisE; Jan 27th, 2018 at 02:38 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

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