TextFieldParser to read file and insert into database

Question

I am trying to read to data from a .csv file and insert the data in a mysql database. The .csv file and the database have 4 columns.

I am able to insert the data to the database. The connections works fine, but i can't get the fields from the .csv file in the right fields in the database.

So how do i get a field from a .csv file in the same field in the database with the method i am using?

My code is similar to this:

Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\ParserText.csv")
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {","}
Dim currentRow As String()
While Not MyReader.EndOfData
    Try
        currentRow = MyReader.ReadFields()
        For Each currentField As String In currentRow

            /*Use sql to insert data to database here*/

            cmd.commandText = "INSERT INTO mydatabase(column1, column2, column3, column4) VALUES(??, ??, ??, ??)"
            cmd.ExecuteNonQuery()

        Next 
    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
        MsgBox("Line " & ex.Message & " is invalid.  Skipping")
    End Try 
    End While 
End Using

are you loading the csv file to datagridview or directly inserting it to database? — akhil kumar, Apr 10 '15 at 10:04
No i am just reading every field from every row in the .csv file and trying to insert the field values in the database. All the code used to read the file is shown above — Koiski, Apr 10 '15 at 10:08
you can use a datatable to hold the data then insert the data in table to sql right?? — akhil kumar, Apr 10 '15 at 10:17

akhil kumar · Accepted Answer · 2015-04-10T11:56:45.070

1

try this code

    Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\ParserText.csv")
   dim table as new datatable 
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
    MyReader.Delimiters = New String() {","}
    myreader.Readline()

Do Until myreader.EndOfData = True
     table.Rows.Add(myreader.ReadFields())
Loop
                       'Use sql to insert data to database here
    for intconut=0 to table.rows.count-1
                cmd.commandText = "INSERT INTO mydatabase(column1, column2, column3, column4) VALUES('" &table.rows(intcount).item(0) & "','" & table.rows(intcount).item(1) & "','" & table.rows(intcount).item(2) & "', '" & table.rows(intcount).item(3)) & "')"
                cmd.ExecuteNonQuery()

     Next 
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & " is invalid.  Skipping")
        End Try 
        End While 
    End Using

this may help.

edited Apr 10 '15 at 11:56

answered Apr 10 '15 at 10:22

akhil kumar

1,598
1
13
26

Getting an error on the line: table.rows.Add(myreader.ReadFields()). The error: An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll. Additional information: Input array is longer than the number of columns in this table. – Koiski Apr 10 '15 at 11:06
add four columns to the datatable using `table.columns.add("column1")` before readfields(). – akhil kumar Apr 10 '15 at 11:08
Getting new error: Incorrect syntax near the keyword 'table'. From my sql query – Koiski Apr 10 '15 at 11:37
Koiski,i cant give you full code so you can just copy paste and move on,i can just deliver some logic to your problem.it's the problem with the query syntax.use proper qoutes and &'s for variables and try. – akhil kumar Apr 10 '15 at 11:55
thanks, didn't know how to end the query with variables and quotes – Koiski Apr 10 '15 at 12:41

score 1 · Answer 2 · edited May 23 '17 at 10:24

Without using sql parameterised queries. You could change the inside of your while clause to match the following:

While Not MyReader.EndOfData
    Try
        currentRow = MyReader.ReadFields()

        cmd.commandText = string.Format("INSERT INTO mydatabase(column1, column2, column3, column4) VALUES (""{0}"", ""{1}"", ""{2}"", ""{3}"")", currentRow[0], currentRow[1], currentRow[2], currentRow[3])

        cmd.ExecuteNonQuery()

        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
        MsgBox("Line " & ex.Message & " is invalid.  Skipping")
    End Try 
End While

Once you understand how this code works, you should look to replace it with a parameterised query.

TextFieldParser to read file and insert into database

2 Answers2