Fast & correctly reading CSV parser (Comma-separated values)

[Merri]

I'm rather surprised that I didn't really find any CSV parsers from our CodeBank. You can find CSV saving code, but reading a CSV file seems to be ignored.

My function here attempts to read a CSV file properly. This means:

• Rows are separated by line change.
• Columns are separated by comma.
• Commas and line changes are allowed within quotes.
• Dual quotes within quotes = quote.
• Space characters are preserved as is required by RFC4180
• You can customize row, column and quote separators (this allows reading tab separated CSV files generated by Excel)

The procedure returns one dimensional string array, but it holds 2D data. This is why it also outputs the number of columns and rows.

Usage is as follows:

Dim strCSV() As String, lngRows As Long, lngCols As Long
    Dim strFile As String, lngA As Long

    ' read sample data to strFile
    Open "test.csv" For Input As #1
        strFile = Input(LOF(1), #1)
    Close #1

    ' parse CSV
    SplitCSV strFile, strCSV, lngCols, lngRows

    ' sample output
    For lngA = 0 To UBound(strCSV)
        Debug.Print "Row " & (lngA \ lngCols), "Column " & (lngA Mod lngCols), "Data: " & strCSV(lngA)
    Next lngA

You can also input the number of columns you want and the rest of the columns are ignored. This does not work for rows, all rows are always returned. The column feature is useful if you want to add new columns, you can simply directly work with the string array and have the new columns available.

Finally, the procedure below is optimized for speed. It could be better, but it does a very good job as it is. Using other solutions such as a VBScript regular expression will be a lot slower.

CODE IN NEXT POST