Working with commas in a comma delimited file

Question

I have a vb project that imports a csv file and some of the data contains commas. The fields with the commas are in double quotes.

I am creating a datagridview from the header row of the csv then importing the remainder of the file into the dgv but the fields with commas are causing a problem. The fields are not fixed width.

I think I need a way to qualify the commas as a delimiter based on double quote or some other method of importing the data into the dgv.

Thanks

Using objReader As New StreamReader(FName)
Dim line As String = objReader.ReadLine()
Do While objReader.Peek() <> -1
    line = objReader.ReadLine()
    Dim splitLine() As String = line.Split(",")
    DataGridView1.Rows.Add(splitLine)
    Application.DoEvents()
Loop
End Using

Example Data:

1,"VALIDFLAG, NOGPS",0,1.34,3.40,0.17,1

**Don't use Split() to read CSV data.** The problem you ran into here with commas is only the beginning. _Don't use RegEx, either._ Same issue: there are _way_ more edge cases than you might think. Do get a dedicated CVS reader. There are at least three included in the .Net framework, including TextFieldParser. — Joel Coehoorn, Nov 14 '18 at 22:29
Possible duplicate of [read csv file in vb.net](https://stackoverflow.com/questions/26791786/read-csv-file-in-vb-net) — Slai, Nov 14 '18 at 22:34
I have to agree with @JoelCoehoorn and suggest that you use a `TextFieldParser`. In that case, all you have to do is set the `HasFieldsEnclosedInQuotes` property to `True` and the work is done for you. — jmcilhinney, Nov 14 '18 at 22:41

score 2 · Answer 1 · answered Nov 14 '18 at 22:50

Thinks very much for the suggestions.

I am going to use textfieldparser for my import.

Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FName)
    MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
    MyReader.Delimiters = New String() {","}
    Dim currentRow As String()
    Dim firstline As Boolean = True
    While Not MyReader.EndOfData
        Try
            currentRow = MyReader.ReadFields()
            If firstline = True Then
                firstline = False
            Else
                Me.DataGridView1.Rows.Add(currentRow)
            End If
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & " is invalid.  Skipping")
        End Try
        Application.DoEvents()
    End While
End Using

Working with commas in a comma delimited file

1 Answers1