Results 1 to 8 of 8

Thread: [RESOLVED] Import from .csv file into MySQL

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Resolved [RESOLVED] Import from .csv file into MySQL

    I am trying to import data from a .csv file into a MySQL database, but are getting an error : 'Input string was not in a correct format.'

    I have searched and tried many solutions with no success.

    First I have created a data table to store the data from the .csv file. If create a breakpoint in my vb.net code and test the data table, it seems fine, however when I try to update the adapter I get the error. This is the code for my data table and the filling thereof :

    Code:
    Dim dt As New System.Data.DataTable
    
            With dt
                .Columns.Add("Vessel", System.Type.GetType("System.String"))
                .Columns.Add("Currvol", System.Type.GetType("System.Double"))
                .Columns.Add("Wine", System.Type.GetType("System.String"))
                .Columns.Add("Winename", System.Type.GetType("System.String"))
                .Columns.Add("Winebatch", System.Type.GetType("System.String"))
                .Columns.Add("Comments", System.Type.GetType("System.String"))
                .Columns.Add("Grower", System.Type.GetType("System.String"))
                .Columns.Add("Growername", System.Type.GetType("System.String"))
                .Columns.Add("Region", System.Type.GetType("System.String"))
                .Columns.Add("Block", System.Type.GetType("System.String"))
                .Columns.Add("Section", System.Type.GetType("System.String"))
                .Columns.Add("Variety", System.Type.GetType("System.String"))
                .Columns.Add("Vintage", System.Type.GetType("System.Int32"))
                .Columns.Add("Sectionvol", System.Type.GetType("System.Double"))
                .Columns.Add("Cdocket", System.Type.GetType("System.String"))
                .Columns.Add("Wdocket", System.Type.GetType("System.String"))
                .Columns.Add("Equivton", System.Type.GetType("System.Double"))
                .Columns.Add("Costperlit", System.Type.GetType("System.Double"))
                .Columns.Add("Payclass", System.Type.GetType("System.String"))
                .Columns.Add("EZYVerslagDatum", System.Type.GetType("System.DateTime"))
                .Columns.Add("FileGebruik", System.Type.GetType("System.String"))
    
            End With
    
    
            Using myReader As New TextFieldParser(fileinvoer, Encoding.Default)
                myReader.TextFieldType = FieldType.Delimited
                myReader.SetDelimiters(",")
                myReader.HasFieldsEnclosedInQuotes = True
                myReader.ReadLine()
    
                Do While Not myReader.EndOfData
                    Dim myData() As String = myReader.ReadFields
    
                    dt.Rows.Add(myData(0),
                    myData(1),
                    myData(2),
                    myData(3),
                    myData(4),
                    myData(5),
                    myData(6),
                    myData(7),
                    myData(8),
                    myData(9),
                    myData(10),
                    myData(11),
                    myData(12),
                    myData(13),
                    myData(14),
                    myData(15),
                    myData(16),
                    myData(17),
                    myData(18),
                    FDate.ToString("dd/MM/yyyy h:mm:ss tt"),
                    fileinvoer.ToString)
    
                Loop
            End Using
    When I test dt here everthing seems OK.

    I then use the following code to load the data from dt into MySQL :


    Code:
     Dim strsql As String = "Insert into EZYComposition (vessel,currvol,wine,winename,winebatch,comments,grower,growername,region,block,section,variety,vintage,sectionvol,cdocket,wdocket,equivton,costPerLit,payclass,eZYVerslagDatum,fileGebruik) " &
                               "values ( @Vessel,@Currvol,@Wine,@Winename,@Winebatch,@comments,@Grower,@Growername,@Region,@Block,@Section,@Variety,@Vintage,@Sectionvol,@Cdocket,@Wdocket,@Equivton,@CostPerLit,@Payclass,@eZYVerslagDatum,@FileGebruik) "
    
    
    
    
            Using connection As New MySqlConnection(MySqlConnectionstring)
                connection.Open()
                Dim cmd As New MySqlCommand(strsql, connection)
                With cmd.Parameters
    
                    .Add("@Vessel", DbType.String, 50, "vessel")
                    .Add("@Currvol", DbType.Decimal, 50, "currvol")
                    .Add("@Wine", DbType.String, 50, "wine")
                    .Add("@Winename", DbType.String, 50, "Winename")
                    .Add("@Winebatch", DbType.String, 50, "winebatch")
                    .Add("@Comments", DbType.String, 50, "comments")
                    .Add("@Grower", DbType.String, 50, "grower")
                    .Add("@Growername", DbType.String, 50, "Growername")
                    .Add("@Region", DbType.String, 50, "region")
                    .Add("@Block", DbType.String, 50, "block")
                    .Add("@Section", DbType.String, 50, "section")
                    .Add("@Variety", DbType.String, 50, "variety")
                    .Add("@Vintage", DbType.Int32, 50, "vintage")
                    .Add("@Sectionvol", DbType.Decimal, 50, "sectionvol")
                    .Add("@Cdocket", DbType.Int32, 50, "cdocket")
                    .Add("@Wdocket", DbType.Int32, 50, "wdocket")
                    .Add("@Equivton", DbType.Decimal, 50, "equivton")
                    .Add("@CostperLit", DbType.Decimal, 50, "costperLit")
                    .Add("@Payclass", DbType.String, 50, "payclass")
                    .Add("@FileGebruik", DbType.String, 50, "fileGebruik")
                    .Add("@EZYVerslagDatum", DbType.DateTime, 50, "eZYVerslagDatum")
                End With
    
    
                Dim adapter As New MySqlDataAdapter
                adapter.InsertCommand = cmd
    
                Dim iRowsInserted As Int32 = adapter.Update(dt)
    
            End Using
    
    
            For i As Integer = 0 To 50
    
                Threading.Thread.Sleep(50)
    
    
            Next

    What am I missing. The code was working perfectly for SQLite? I am including the MYSQL create table command as well as my .csv data as .txt
    I get the error at this line :

    Code:
    Dim iRowsInserted As Int32 = adapter.Update(dt)


    composition.txt
    MySQLTablecreate.txt.

    Can someone please help or direct me to a tutorial to do this?

    Regards

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import from .csv file into MySQL

    First things first, don't do this:
    vb.net Code:
    1. .Columns.Add("Vessel", System.Type.GetType("System.String"))
    when you can do this:
    vb.net Code:
    1. .Columns.Add("Vessel", GetType(String))
    The latter takes an actual data type, rather than a String containing the name of a type, and thus gets type-checking support at compile time. Only use the first option if the String will be coming from another source and you won't know what it is until run time.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import from .csv file into MySQL

    As for the issue, why would you go and do this:
    vb.net Code:
    1. FDate.ToString("dd/MM/yyyy h:mm:ss tt")
    You want to save a date so save a Date, not a String. SQLite has no dedicated date data type so you have to save text. MySQL does have a dedicated date data type and I would hope that your database column is that type, so you should be saying Date values, not String values. Just use FDate as it is. Don't convert it to a String. If you want to trim off the milliseconds or something like that then do so, but create a new Date value and save that.

    I'm assuming that its the dates that is the issue because it usually is but it may not be. You've got several other columns that you have specified are not text and yet you're saving String values. That is ALWAYS bad. Take responsibility for your data and convert it to the appropriate type if it isn't already that type. If you do all the casting/converting yourself then, if there's an issue, you'll be able to see exactly where it is, instead of wondering which of the many implicit conversions you're relying on has failed.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import from .csv file into MySQL

    Thank you for that insight.
    I have done as you suggested, but I am still getting the error. I have reduced the data table to only one column, i.e. vessel.
    That is definitely a string and is declared in MySQL table as TEXT, but still the same error : 'Input string was not in a correct format.' at the same line.


    Code:
    Dim dt2 As New System.Data.DataTable
    
            With dt2
    
                .Columns.Add("Vessel", GetType(String))
    
            End With
    
    
            Using myReader As New TextFieldParser(fileinvoer, Encoding.Default)
                myReader.TextFieldType = FieldType.Delimited
                myReader.SetDelimiters(",")
                myReader.HasFieldsEnclosedInQuotes = True
                myReader.ReadLine()
    
                Do While Not myReader.EndOfData
                    Dim myData() As String = myReader.ReadFields
    
                    dt2.Rows.Add(myData(0))
    
                Loop
            End Using
    
    
            Dim strsql2 As String = "Insert into EZYComposition (vessel) " &
                               "values ( @Vessel) "
    
    
            Using connection As New MySqlConnection(MySqlConnectionstring)
                connection.Open()
                Dim cmd As New MySqlCommand(strsql2, connection)
                With cmd.Parameters
    
                    .Add("@Vessel", DbType.String, 50, "vessel")
                End With
    
    
                Dim adapter As New MySqlDataAdapter
                adapter.InsertCommand = cmd
    
                Dim iRowsInserted As Int32 = adapter.Update(dt2)
    
            End Using
    
    
            For i As Integer = 0 To 50
    
                Threading.Thread.Sleep(50)
    
    
            Next

    Regards

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import from .csv file into MySQL

    I didn't notice the first time but this is wrong:
    Code:
    .Add("@Vessel", DbType.String, 50, "vessel")
    That second parameter should be type MySqlDbType. The numerical value of DbType.String is 16, as you can see here. Your code is causing that value to be implicitly converted and 16 corresponds to MySqlDbType.Bit. You are providing Strings that are not in the correct format to be converted to Bit values, hence the exception.

    This is a perfect example of why you should have Option Strict On. If you had, that would have been flagged as a compilation error and you'd have been told that the expected type was MySqlDbType. Turn Option Strict On in the project properties and also in the IDE options, so it will be On by default for all future projects.

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Import from .csv file into MySQL

    JMC beat me

    I'll post anyway

    Code:
     With cmd.Parameters
                    .Add("@Vessel", MySqlDbType.VarChar, 50, "vessel")
                End With
    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.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import from .csv file into MySQL

    Fantastic. Thank you very much, especially the explaining. Indeed that was the issue.

    When I turned Option Strict on I got the following error which is much more explanatory : Option Strict On disallows implicit conversions from 'DbType' to 'MySqlDbType'

    I will from now on always use that option and have also set my IDE as default.

    For others : Turning the Option as default in Visual Studio (the IDE I'm using) : Tools-Options-Projects and Solutions-VB Defaults

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import from .csv file into MySQL

    Thank you Chris.

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
  •  



Click Here to Expand Forum to Full Width