@Jeff T Again, that code breaks in this instance.. because the data has CR in the middle of a line, surrounded by quotes
eeee;ddd;"ggg CR ggggggg"; CRLF
Norman's parser (that Jason linked to) will fix this, but needs to be modified slightly to change the field delimiter to a semicolon.
Another option is to make use of the split function to isolate the quoted material. This is the code that I use for reading CSV files (modified to use a semicolon):
Function ImportCSV(csvText As TextInputStream) as DataRecord()
'Parses semicolon delimited TextInputStream into an array of "record" arrays.
'Return type from this routine is an array of type DataRecord.
'DataRecord is a class containing nothing but a string array property: dataField()
'So, an array of DataRecord is essentially a general two dimensional array
'that can have a variable number of rows and columns.
dim outData() as DataRecord
dim delimField As string = ";" 'For CSV change this to "," or for TAB delimited to chr(9)
dim delimQuote As string = chr(34)
dim rawInput,FieldData As String
While not csvText.EOF
'Read a line of text
rawInput = csvText.ReadLine
'Read more if pending line has embedded line endings
While (max(1,CountFields(rawInput,delimQuote)) mod 2=0) 'While quote parity is odd...
if csvText.EOF then 'Big trouble!
MsgBox "Encountered EOF while processing quoted text. Closing quote is missing."
'Could handle this by returning outData as is,
'or add a closing quote to the last record.
return nil 'which means bad file data regardless.
rawInput = rawInput + EndOfLine + csvText.ReadLine
' ********** Start new record
dim currentRecordNo As Integer = UBound(outData)
dim Qgroup() As String = split(rawInput,delimQuote) 'Odd numbered elements are quoted text
dim nQgroups As Integer = UBound(Qgroup)
for i as integer = 0 to nQgroups step 2 'Skip over quoted text ...for now
dim field() As string = split(Qgroup(i),delimField)
if UBound(field)<0 Then field.Append("") 'fix inconsistency in how Split() handles null string
dim nFields As Integer = UBound(field)
for j as Integer = 0 to nFields
if j<>0 then
'********** Save field data for current field in current record
FieldData = FieldData+field(j)
if j=nFields and i<nQgroups then 'This is where we include the quoted text
'********** Save field data for last field in record
Function UnQuote(s As String, Qchar As String) as string
' Remove enclosing quotes (if any) from CSV field and unEscape embedded quotes
' Called by ImportCSV and ImportCSVdb
dim temp As String = s
if left(s,1)=Qchar and right(s,1)=Qchar then
dataField() As String
I've fed it a lot of messy CSV text, and haven't managed to break it so far. This example puts the data into a 'DataRecord' object which is essentially a variable size two dimensional string array. To handle the field data differently you need to alter the code following the 3 comment lines that begin with ' **********.