2 Attachment(s)
[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)
Attachment 178797
Attachment 178798.
Can someone please help or direct me to a tutorial to do this?
Regards
Re: Import from .csv file into MySQL
First things first, don't do this:
vb.net Code:
.Columns.Add("Vessel", System.Type.GetType("System.String"))
when you can do this:
vb.net Code:
.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.
Re: Import from .csv file into MySQL
As for the issue, why would you go and do this:
vb.net Code:
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.
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
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.
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
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
Re: Import from .csv file into MySQL
