CSV string to an array 4

[visualJay]

Trying to convert a CSV file to a 2 dimensional array
the line looks something like this

123456, 08/15/2001, 2:39:40 AM, 2:41:29 AM, 49 secs
123654, 08/15/2001, 2:49:40 AM, 2:51:29 AM, 49 secs


I open the file and do line input,
then I want the line to be put into a two dimensional array
the number of lines is unknown so I place a counter
Looks something like this

cnt = 0
do until (EOF(1))
line input #1
for x = 0 to 4
Shold(cnt,x) = (This should the section of the string)
next x
cnt = cnt + 1
doevents
loop

Shold(0,0) through Shold(0,4) for line 1
Shold(1,0) through Shold(1,4) for line 2 and so on....

everything I try does not seem to work
Any help would be greatful.

 

[MichaelRed]

Foolish!

Try Spllit.

At first glance, I thought that only a reference to the function would be sufficient. On second thought, there are several "issues" with even this fragment, so I am posting a 'more-or-less' complete soloution.

The (UDT?) shold is not clear to me, so this simple procedure truncated it to an array of strings which are returned to the calling procedure.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jjames]

I would use a collection...

private colData As Collection

Private Sub Command1_Click()
Dim aData As Variant
Dim sData As String
Dim iRow As Long
Dim iCol As Long

Set colData = New Collection

'Open file for input as 1 here

Do Until (EOF(1))
Line Input #1, sData
aData = Split(sData, ",&quot
colData.Add aData 'Add array to collection
Loop

Close #1

Debug.Print colData.Count

For iRow = 1 To colData.Count

For iCol = 0 To UBound(colData.Item(iRow))
Debug.Print Trim$(colData.Item(iRow)(iCol))
Next

Debug.Print " " 'a line

Next

End Sub

 

[scorpio66]

Dim arData As Variant
Dim sData As String
Dim i As Long
Dim j As Long
Dim f As Long
    
f = FreeFile

Open "c:\chaz.dat" For Input As #1
ReDim arData(0)

Do Until (EOF(f))
  Line Input #1, sData
  arData(UBound(arData)) = Split(sData, ",")
  ReDim Preserve arData(UBound(arData) + 1)
Loop
Close #1

For i = 0 To UBound(arData) - 1
  For j = 0 To UBound(arData(i))
    Debug.Print arData(i)(j)
  Next
Next

Chaz

 

[rvBasic]

I answered an earlier (or later?) thread by the same author related to this one by pointing out that the FileSystemObject in combination with the Split function could offer a solution to this particular problem, Here's the code that goes along:
Const c_strFileName As String = "E:\thread121572\thread121572.csv"
Const c_strComma As String = ","

Public Sub Main()

Dim astrTempNames() As String
Dim objFso As FileSystemObject
Dim objTextStream As TextStream

'Split entire file into a one-dimensional array
Set objFso = New FileSystemObject
Set objTextStream = objFspenTextFile(c_strFileName, ForReading)
astrTempNames = Split(objTextStream.ReadAll, vbCrLf)

Set objTextStream = Nothing
Set objFso = Nothing

'We use the First row to determine the number of elements in a Row
' All rows are supposed to have the same number of elements
Dim astrTempRow() As String
astrTempRow = Split(astrTempNames(0), c_strComma)

Dim astrNames() As String
ReDim astrNames(UBound(astrTempNames), UBound(astrTempRow))

'Because we splitted already the first row, we can as well
' insert it already into the Definitive Array
InsertRowIntoArray astrTempRow, astrNames, 0

'Insert all other rows
Dim lngRowIndex As Long
For lngRowIndex = 1 To UBound(astrTempNames)
astrTempRow = Split(astrTempNames(lngRowIndex), c_strComma)
InsertRowIntoArray astrTempRow, astrNames, lngRowIndex
Next lngRowIndex

'Clean Up
Erase astrTempRow
Erase astrTempNames

End Sub

Private Sub InsertRowIntoArray(astrRow() As String, astrArray() As String, _
lngRowNumber As Long)

Dim lngRowIndex As Long
For lngRowIndex = 0 To UBound(astrRow)
astrArray(lngRowNumber, lngRowIndex) = astrRow(lngRowIndex)
Next lngRowIndex

End Sub


To add the FileSystemObject to your project go to the Project Meny -> References -> MS Scripting runtime _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[fenris]

Try this....

dim fil as integer
dim fName as string
dim upper as long, i as long,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 reportArray() as string ' the 2d array that holds X lines and 0 to 4 elements per line

fil = FreeFile 'get the next free file number
fName = "file.txt"

'open the file and dump the contents into the rawfile variable

Open fName For Binary As #fFile
rawFile = String$(LOF(fFile), 32)
Get #fFile, 1, rawFile
Close #fFile
'A Note on the above method, it works for large files. I tried it with a 50 meg file on a computer with 128 Mb of ram and it worked file.

rawSplit = Split(rawFile, vbCrLf) 'split the file up by lines

upper = ubound(rawSplit) 'the number of lines in the file
redim reportArray(0 to upper, 0 to 4)


for i = 0 to upper
oneline = split(rawsplit(i),",&quot
for j = 0 to 4
reportArray(i,j) = oneline(j)
next j
next i


Good Luck, Hope this helps.... Troy Williams B.Eng.
fenris@hotmail.com

 

[visualJay]

Thank you all for your help.

 

[MichaelRed]

Troy,

I am impressed. I like it SO MUCH that you not only get the STAR, I 'Generalized) 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. I think this would be a good FAQ, but the credit should go to YOU, not me. I would urge you to post it under your name.

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

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[fenris]

Michael,
Thank you very much for the kind words, but I am a strong believer in GNU and open source. If you want to post a faq be my guest.

I can't take complete credit for parts of the code. Specifically the portion that reads the file in, in one big chunk. That was from a faq posted by Alt 255.

To be honest with you this is how software development was meant to be! Professionals collaberating together to help out newbies and further our art!

Troy Williams B.Eng.
fenris@hotmail.com

 

[visualJay]

Fenris, I have posted it in FAQ under File Conversions and I acknowledged the help from all sources and the easyness of the code shown.

Question for all?

Can we step the ease of use up? By turning the code submited by MichaelRed into a module or dll so the user passes the file name and the seperater(coma,tab,and so on) and then it returns the array back to the calling program for use.

Can this be done?

 

[MichaelRed]

Troy,

Simce the CODE would be (IS) posted, I think this is at least "Open Source", if not under GNU license. IN fact since it is simply posted w/o out the GNU license, it is even MORE open than GNU. I, personally, do not see any reason you could not post to the FAQ. After all, you post HERE. Posting to the FAQ only make the item -perhaps- more open and accessible.


visualJay,

Adding the delimiter d' jour is trivial. Modified code (tested only in a trivial sense) is posted below. IMHO, the "improvement" DOES increase the generality of the procedure - at the expense of clarity, so it is -at best- a mixed blessing.

Public Function basCSV2Array(fName As String, _
                             Optional RecSep As String = vbCrLf, _
                             Optional FldSep 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, RecSep)   'Split the file up by lines
    NRecs = UBound(RawSplit) - 1            'Num Lines/Records in the file

    OneLine = Split(RawSplit(0), FldSep)        '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), FldSep)
       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

End Function

Making it a "Module" is completly un-necessary. It is a self-contained procedure (Function) which would normally be included in a module. I would normally not think the single porcedure worth the effort of making a .dll, and "Compilling" the procedure would then remove the source code from 'users". They, in turn, would need documentation on HOW to use the function(s) within the .dll, which would need to be generated and distributed, maintained ...

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over