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("ab,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