I Generalize the procedure to accept a (fully qualified) filename and to return the array to the calling program. In doing that, I decided to also dynamically calculate the number of fields. Some variables were also 'renamed' just suit the naming conventions I generally use. Still, the substansative changes may make this generically useful to Others.
Public Function basCSV2Array(fName As String) As Variant
Dim Fil As Integer Dim NRecs As Long Dim NFlds As Long Dim Idx As Long Dim J As Long 'indices Dim RawFile As String 'Holds the entire contents of the file Dim RawSplit() As String 'the file split up on a line per line basis Dim OneLine() As String 'used to hold the tokens for one line Dim RptAry() As String '2d array. Holds X lines & 0 to 4 elements per line
Fil = FreeFile 'get the next free file number
'This works for large files. I (Troy williams)tried it 'with a 50 meg file on a computer with 128 Mb of ram and it worked file. 'open the file and dump the contents into the rawfile variable Open fName For Binary As #Fil 'Open file RawFile = String$(LOF(Fil), 32) 'Create "empty" String of Length Get #Fil, 1, RawFile 'Fill "Empty Str with File Close #Fil 'Close File
'Get the Nunber of Records and Fields RawSplit = Split(RawFile, vbCrLf) 'Split the file up by lines NRecs = UBound(RawSplit) - 1 'Num Lines/Records in the file
OneLine = Split(RawSplit(0), ",") 'Split the first line NFlds = UBound(OneLine) 'Get the Number of Fields
ReDim RptAry(0 To NRecs, 0 To NFlds)
For Idx = 0 To NRecs OneLine = Split(RawSplit(Idx), ",") For Jdx = 0 To NFlds RptAry(Idx, Jdx) = OneLine(Jdx) Next Jdx Next Idx
basCSV2Array = RptAry
End Function Public Function basTstCSV2Array()
Dim Idx As Long Dim Jdx As Long Dim Kdx As Long Dim Ldx As Long Dim FilNme As String Dim MyArray As Variant
FilNme = "C:\My Documents\MyCsvFile.Txt"
MyArray = basCSV2Array(FilNme)
Idx = UBound(MyArray, 1) Jdx = UBound(MyArray, 2)
For Kdx = 0 To Idx For Ldx = 0 To Jdx Debug.Print MyArray(Kdx, Ldx); Next Ldx Debug.Print Next Kdx
This was the most easy to use and helpful to everyone. Although many programmers helped in ansering this question. To see all results for this answer see the Forum for Visual Basic 5 & 6 question "CSV string to an array" or see this link.