|Title||Read a CSV file into a ragged array of Variants|
|Keywords||CSV, comma-separated value, file, data, array, ragged array, irregular array|
|Categories||Files and Directories, Tips and Tricks|
Grab the whole file into a string. Then use Split to break it into lines. For each line, use Split again to break it into fields.
This program stores each row as a Variant array holding the row's values so each Variant, and hence each row, can hold a different number of items.
Private Sub cmdGo_Click()
Dim file_name As String
Dim fnum As Integer
Dim whole_file As String
Dim lines As Variant
Dim num_rows As Long
Dim the_array() As Variant
Dim R As Long
Dim C As Long
file_name = App.Path
If Right$(file_name, 1) <> "\" Then file_name = _
file_name & "\"
file_name = file_name & "test.csv"
' Load the file.
fnum = FreeFile
Open file_name For Input As fnum
whole_file = Input$(LOF(fnum), #fnum)
' Break the file into lines.
lines = Split(whole_file, vbCrLf)
' Dimension the array.
num_rows = UBound(lines)
' Copy the data into the array.
For R = 0 To num_rows
the_array(R) = Split(lines(R), ",")
' Prove we have the data loaded.
For R = 0 To UBound(the_array)
For C = 0 To UBound(the_array(R))
Debug.Print the_array(R)(C) & "|";