Public Function IsGoodParseCSV() As Boolean
' verify correct ParseCSV returns, 20020603, rev 20020930
' returns True if all tests are passed
' replace "ParseCSV01" with the name of your function to test
Dim fFailed As Boolean
Dim sText As String
Dim asValues() As String
Dim cntValues As Long
With New CParseCSVMike
' Margherita: pretty standard CSV -------------------------------------------
cntValues = ParseCSV01("a,b,c", asValues())
If cntValues <> 3 Or Joint(asValues) <> "a,b,c" Then Stop: fFailed = True
cntValues = ParseCSV01("abc", asValues())
If cntValues <> 1 Or Joint(asValues) <> "abc" Then Stop: fFailed = True
cntValues = ParseCSV01("aa,bb;cc", asValues())
If cntValues <> 2 Or Joint(asValues) <> "aa,bb;cc" Then Stop: fFailed = True
cntValues = ParseCSV01("a b,c", asValues())
If cntValues <> 2 Or Joint(asValues) <> "a b,c" Then Stop: fFailed = True
cntValues = ParseCSV01(" a , b , c ", asValues())
If cntValues <> 3 Or Joint(asValues) <> "a,b,c" Then Stop: fFailed = True
cntValues = ParseCSV01(",b,", asValues())
If cntValues <> 3 Or Joint(asValues) <> ",b," Then Stop: fFailed = True
cntValues = ParseCSV01(",", asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> " + " Then Stop: fFailed = True
cntValues = ParseCSV01(",,", asValues())
If cntValues <> 3 Or Joint(asValues) <> ",," Then Stop: fFailed = True
' setting delimiter
cntValues = ParseCSV01("a;b,c", asValues(), ";")
If cntValues <> 2 Or Joint(asValues, "|") <> "a|b,c" Then Stop: fFailed = True
' unicode
cntValues = ParseCSV01("a€b,c", asValues(), "€")
If cntValues <> 2 Or Joint(asValues, "|") <> "a|b,c" Then Stop: fFailed = True
' empty
cntValues = ParseCSV01("a,b,c", asValues(), "")
If cntValues <> 1 Or Joint(asValues) <> "a,b,c" Then Stop: fFailed = True
' setting textqualifier
cntValues = ParseCSV01("*a*,b,c", asValues(), , "*")
If cntValues <> 3 Or Joint(asValues) <> "a,b,c" Then Stop: fFailed = True
' unicode
cntValues = ParseCSV01("€a€,b,c", asValues(), , "€")
If cntValues <> 3 Or Joint(asValues) <> "a,b,c" Then Stop: fFailed = True
' empty
cntValues = ParseCSV01("a,b,c", asValues(), , "")
If cntValues <> 1 Or Joint(asValues) <> "a,b,c" Then Stop: fFailed = True
' unicode string that contains critical ASCII codes in the lower byte
sText = "a" & ChrW$(&H12C) & "b"
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 1 Or Joint(asValues) <> sText Then Stop: fFailed = True
sText = """a"",b" '= "a",b
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a + b" Then Stop: fFailed = True
' thanks to Jean-Roch Roy for crashing a previous attempt with this one
sText = """a""," '= "a",
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a + " Then Stop: fFailed = True
sText = """"",b" '= "",b
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> " + b" Then Stop: fFailed = True
sText = """ "",b" '= " ",b
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> " + b" Then Stop: fFailed = True
sText = """ a , b "", c " '= " a , b ", c
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> " a , b + c" Then Stop: fFailed = True
sText = """a,b""" '= "a,b"
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 1 Or Joint(asValues, " + ") <> "a,b" Then Stop: fFailed = True
sText = """a,b"",c" '= "a,b",c
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a,b + c" Then Stop: fFailed = True
' Calzone: quotes within values ---------------------------------------------
sText = "a""b,c" '= a"b,c --> a"b + c
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a""b + c" Then Stop: fFailed = True
sText = "a""""b,c" '= a""b,c --> a""b + c
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a""""b + c" Then Stop: fFailed = True
' Calzone Infierno: quotes within quotes ------------------------------------
''' 20020629 cannot do yet!
' sText = """a""""b"",c" '= "a""b",c --> a"b + c
' cntValues = ParseCSV01(sText, asValues())
' If cntValues <> 2 Or Joint(asValues, " + ") <> "a""b + c" Then Stop: fFailed = True
'
' sText = Star2Quote("a,*B: **Hi, I'm B***,c")
' cntValues = ParseCSV01(sText, asValues())
' If cntValues <> 2 Or Joint(asValues, " + ") <> Star2Quote("a + B: *Hi, I'm B* + c") Then Stop: fFailed = True
' Diavolo: ill-formed CSV ---------------------------------------------------
' unclosed quotes: there are at least 2 valid strategies to handle this case
' If your function goes by one of them it's good enough for this test.
' - Strategy 1: a quoted field without closing quotes ends at string-end
' (not necessarily line-end if the string has more than one line).
' This is how Excel does it.
' - Strategy 2: if there's no closing quote, then it wasn't an opening quote
' in the first. Instead we have a non-quoted value starting with a quote
' and ending at the next comma
' - Note that PHP's fgetcsv() will stop processing the file if it cannot
' find a closing quote. We will not follow PHP in this behavior.
sText = """a" '= "a
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 1 Or Joint(asValues, " + ") <> "a" Then
' failed 1, check strategy 2
If cntValues <> 1 Or Joint(asValues, " + ") <> """a" Then Stop: fFailed = True
End If
'
sText = """a,b" '= "a,b
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 1 Or Joint(asValues, " + ") <> "a,b" Then
' failed 1, check strategy 2
If cntValues <> 2 Or Joint(asValues, " + ") <> """a + b" Then Stop: fFailed = True
End If
'
sText = "a,""b,c" '= a,"b,c
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a + b,c" Then
' failed 1, check strategy 2
If cntValues <> 3 Or Joint(asValues, " + ") <> "a + ""b + c" Then Stop: fFailed = True
End If
' unopened quotes: keep ending quotes that have no starting quote
sText = "a,b""""" '= a,b""
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Or Joint(asValues, " + ") <> "a + b""""" Then Stop: fFailed = True
' Expression is zero length: return single-element array containing a zero-length string
cntValues = ParseCSV01("", asValues())
If cntValues <> 1 Or Joint(asValues, " + ") <> "" Then Stop: fFailed = True
' Quattro Stagioni: ill-formed CSV without a cure ---------------------------
' Where PHP and Excel return different results, the situation is obviously
' tough. VBspeed is forbearing and does not look at the results too hard.
' PHP 4.0.6: ""a,b -> ,b
' Excel 97: ""a,b -> a,b
' Also ok: ""a,b -> "a,b
sText = """""a,b" '= ""a,b
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Then
' this is definitely wrong
Stop: fFailed = True
Else
If Joint(asValues, " + ") <> """a + b" Then
' Stop: fFailed = True
End If
End If
' PHP 4.0.6: "a"b,c -> a,c
' Excel 97: "a"b,c -> ab,c
' Also ok: "a"b,c -> a"b,c
sText = """a""b,c" '= "a"b,c
cntValues = ParseCSV01(sText, asValues())
If cntValues <> 2 Then
' this is definitely wrong
Stop: fFailed = True
Else
If cntValues <> 2 Or Joint(asValues, " + ") <> "a""b + c" Then
' Stop: fFailed = True
End If
End If
End With
' well done
IsGoodParseCSV = Not fFailed
End Function
Public Property Get Star2Quote(ByVal sStarred As String) As String
' replace * by "
Dim i As Long
For i = 1 To Len(sStarred)
If Mid$(sStarred, i, 1) = "*" Then Mid$(sStarred, i, 1) = """"
Next
Star2Quote = sStarred
End Property
Public Property Get Joint(sArr() As String, Optional Delimiter As String = ",") As String
' special Join for VBspeed checking functions
Dim i As Long
' is array bound
If Not Val(Not sArr) Then
For i = LBound(sArr) To UBound(sArr) - 1
Joint = Joint & (sArr(i) & Delimiter) ' add smaller parts first
Next
Joint = Joint & sArr(UBound(sArr))
End If
End Property
Back to ParseCSV