Counting number of columns in CSV/Text file

[ExcelMonkey]

CSV/My text file has data in it which originates from a spreadsheeet. My
goal is count the number of columns of data which were pulled into the text
file. In a simple world I would just count the fields at the top of the text
file. But I have no control over the imported data and the first line will
not necessarily have the fields. I need to build a loop which loops though
the rows in my text file and then performs a field count per row and stores
the field count number. Then the Max of all field counts per row = number of
columns in original data.

How do I do this?

Thanks

EM

 

[Rick Rothstein \(MVP - VB\)]

Assuming none of your individual fields contain quote-encased text with
embedded commas (if they do, let me know as there is a work around for it),
this function should return the maximum number of columns (fields) in your
rows (records); just pass in the full path to your cvs file and use its
return value...

Function MaxColumns(PathFileName As String) As Long
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Records() As String
Dim Fields() As String
' Let VB generate the file channel number
FileNum = FreeFile
' Reads the whole file into memory all at once
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
For X = 0 To UBound(Records)
Fields = Split(Records(X), ",")
If UBound(Fields) > MaxColumns Then MaxColumns = UBound(Fields)
Next
' Since Split returns a zero-based array, the UBound function
' values we have been comparing and saving are one less than
' the count of fields in the array; so we add one to adjust for that
MaxColumns = MaxColumns + 1
End Function

Rick