CSV with commas in file

[MacRaider4]

I'm new to using CSV Files, and I've been looking around for a few hours now for a good example on them and no luck. This is what I have so far, which does work... I'm just displaying it in a text box while I work the kinks out and learn more about this...

Private Sub ReadCSVFileToArray()

        Dim strfilename As String
        Dim num_rows As Long
        Dim num_cols As Long
        Dim x As Integer
        Dim y As Integer
        Dim strarray(1, 1) As String

        ' Load the file.
        strfilename = "C:\myTest.csv"

        'Check if file exist
        If File.Exists(strfilename) Then
            Dim tmpstream As StreamReader = File.OpenText(strfilename)
            Dim strlines() As String
            Dim strline() As String

            'Load content of file to strLines array
            strlines = tmpstream.ReadToEnd().Split(Environment.NewLine)

            ' Redimension the array.
            num_rows = UBound(strlines)
            strline = strlines(0).Split(",")
            num_cols = UBound(strline)
            ReDim strarray(num_rows, num_cols)

            ' Copy the data into the array.
            For x = 0 To num_rows - 1
                strline = strlines(x).Split(",")
                For y = 0 To num_cols
                    strarray(x, y) = strline(y)
                Next
            Next

            ' Display the data in textbox
            For x = 0 To num_rows
                For y = 0 To num_cols
                    TextBox1.Text = TextBox1.Text & strarray(x, y) & ","
                Next
                TextBox1.Text = TextBox1.Text & Environment.NewLine
            Next

        End If
        
    End Sub

What I need is to figure out how to ignore commas within the file... for example lets stay Data Entry Guru entered 78 Main St, Apt 1 and the raw file reads ,"78 Main St, Apt 1",Anytown, CA
I would like 78 Main St, Apt 1 to be one "field", Anytown another and CA another.

I'm using VB.Net 2005 for this.
Thank you in advance.

 

[JohnH]

From Edit menu (Intellisense) or with context menu select: Insert Snippet > Fundamentals > File System > Read a Delimited Text File
You get a basic code snippet inserted in code that uses that TextFieldParser class, it handles quoted fields perfectly.

 

[MacRaider4]

Tried that, all I get is the following which is just a different way of getting the same info I already have:

Dim filename As String = "C:\Test.txt"
        Dim fields As String()
        Dim delimiter As String = ","
        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData
                ' Read in the fields for the current line
                fields = parser.ReadFields()
                ' Add code here to use data in fields variable.

            End While
        End Using

Though I do think I'm going to try to get this version working as it does seem a little easier. But still no luck with the quotes...

 

[JohnH]

What do you mean no luck? I tested the snippet with the mixed quoted data string you posted and it does handle that exactly as it should. TextFileParser is nothing like doing a simple string split.

 

[MacRaider4]

I've found it really helps to click the right button to test your code... doh!


Thank you... just have to work out the rest of it now, but that problem is now solved

 

[MacRaider4]

This is what I have and it seems to work...

Dim filename As String = "C:\myTest.csv"
        Dim fields() As String
        Dim delimiter As String = ","
        Dim x As Integer

        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData
                ' Read in the fields for the current line
                fields = parser.ReadFields()

                For x = 0 To CStr(fields.Length) - 1
                    TextBox1.Text = TextBox1.Text & fields(x) & ","
                    'MessageBox.Show(fields(3)) 'Test to see if it ignores comma inside quotes
                Next
                TextBox1.Text = TextBox1.Text & Environment.NewLine
                'MessageBox.Show(CStr(fields.Length)) 'Test to get length of array
            End While

        End Using