' Reads in a Comma Seporated Values (CSV) file
' and puts it all into collections with nice labels.
' Parameters: vstrListFilePath - the path and file name of the CSV file.
' This function uses the Split function to carve up CSV file
' and load it all into collections.
' In the CSV file, the first row is for field names (headers)
' and only the right most column can contain commas.
' That's usually used for comments.
' --- EXAMPLE ---
' Set cData = GetCSVCollection(Command)
' For Each cFields In cData

' Debug.Print cFields("Header")

' Next
Dim arrRecs() As String ' <-- array of records or lines
Dim arrFieldNames() As String ' <-- field names are in the first line
Dim arrVals() As String ' <-- array of values in one line goes to colVals
Dim colRecs As New VBA.Collection ' <-- where all this data ends up
Dim colVals As VBA.Collection ' <-- values from one line with field names as keys
Dim lngValUBound As Long ' <-- max index of vals in rec
Dim lngFieldNamesUBound As Long
Dim i As Long ' <-- row loop index
Dim j As Long ' <-- column loop index

arrRecs = Split(GetFileContents(vstrListFilePath), vbCrLf)
arrFieldNames = Split(arrRecs(0), ",")
lngFieldNamesUBound = UBound(arrFieldNames)

For i = 1 To UBound(arrRecs)
arrVals = Split(arrRecs(i), ",")
' --- This handles commas in the last column. ---
lngValUBound = UBound(arrVals)
If lngValUBound > lngFieldNamesUBound Then

For j = lngFieldNamesUBound + 1 To lngValUBound

arrVals(lngFieldNamesUBound) = arrVals(lngFieldNamesUBound) + "," + arrVals(j)

Next
lngValUBound = lngFieldNamesUBound

End If

If lngValUBound >= 0 Then

Set colVals = New Collection
For j = 0 To lngValUBound

colVals.Add Trim(arrVals(j)), Trim(arrFieldNames(j))

Next
For j = lngValUBound + 1 To lngFieldNamesUBound

colVals.Add "", Trim(arrFieldNames(j))

Next
colRecs.Add colVals

End If

Next
Set GetCSVCollection = colRecs