ReadFields() in TextFieldParser throwing exception

Question

I have a bunch of csv files in a folder. Here is a sample:

Item    Value
Row1    Val1
Row2    Val2
Row3    Val3
Row4    Val4"
Row5    Val5

I had written a code to plot a chart based on the information available in all the csv file in that folder. Here is my button click event:

Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles generatePlot.Click

        Dim dirs As FileInfo() = GetFilesInDirectory("*.csv", True) 'Get all the csv file from result folder in descending order (creation date)
        Dim diNext As FileInfo

        Try
            For Each diNext In dirs.Reverse
                Using MyReader As New FileIO.TextFieldParser(diNext.FullName)
                    MyReader.TextFieldType = FileIO.FieldType.Delimited
                    MyReader.SetDelimiters(",")
                    Dim currentRow As String()
                    While Not MyReader.EndOfData
                        currentRow = MyReader.ReadFields()
                        processRow(diNext, currentRow)
                    End While
                End Using
            Next
        Catch ex As Exception
            MessageBox.Show(ErrorToString)
        End Try

        'Save chart as an image
        Chart1.SaveImage(imageSave, System.Drawing.Imaging.ImageFormat.Bmp)

    End Sub

If you look at my sample csv, Row4 has a value of Val4". Note the double quote in it. And, I am getting an exception in my code at currentRow = MyReader.ReadFields() which says Line 5 cannot be parsed using the current delimiter. I know that the reason is because of the presence of double quote. Since this is a string array, I thought that I need to create a function to process each item in the array and trim out the double quote. But, I can't do it as the exception is thrown even before I can process the string array.

Any idea on how to solve this?

Hari

Answer 1

A StreamReader can be used to read text files, just look at the example below to achieve your needs:

Note that the MemoryStream and the Writer are not needed for you, just the Reader.

Public Sub ReadTest()
    Using MemoryStream As New IO.MemoryStream()
        Dim Writer As New IO.StreamWriter(MemoryStream) 'Writing on a memory stream to emulate a File
        Writer.WriteLine("Item,Value")
        Writer.WriteLine("Row1,Val1")
        Writer.WriteLine("Row2,Val2")
        Writer.WriteLine("Row3,Val3")
        Writer.WriteLine("Row4,Val4""")
        Writer.WriteLine("Row5,Val5")

        Writer.Flush()
        MemoryStream.Position = 0 'Reseting the MemoryStream to Begin Reading

        Dim Reader As New IO.StreamReader(MemoryStream) 'Reading from the Memory but can be changed into the File Path
        While Not Reader.EndOfStream
            Dim Line As String = Reader.ReadLine
            Dim Values() = Line.Split(",")
            'Values(0) will contain the First Item
            'Values(1) will contain the second Item

            Values(1).Replace("""", "") 'Remove the quote from the value string

        End While

    End Using
End Sub

Answer 2

Thanks to the suggestion given by @jmcilhinney and @AugustoQ.

Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles generatePlot.Click

        Dim dirs As FileInfo() = GetFilesInDirectory("*.csv", True) 'Get all the csv file from result folder in descending order (creation date)
        Dim diNext As FileInfo
        Dim currentRow As String()
        Try
            For Each diNext In dirs.Reverse
                For Each rawRows As String In File.ReadLines(diNext.FullName)
                    currentRow = processRawRow(rawRows)
                    processRow(diNext, currentRow)
                Next
            Next
        Catch ex As Exception
            MessageBox.Show(ErrorToString)
        End Try

        'Save chart as an image
        Chart1.SaveImage(imageSave, System.Drawing.Imaging.ImageFormat.Bmp)

    End Sub

I had replaced the TextFieldParser completely and used this function:

Private Function processRawRow(ByVal rawRows As String) As String()

        rawRows = rawRows.Replace("""", "").Trim()
        Dim processedList = rawRows.Split(",")
        Return processedList

    End Function

And it works perfectly. Thanks everyone...

Hari