How to fetch text file into array in VBA

Question

I'm trying to fetch the tab delimited text file into arrays, I already know how to read that text file into spreadsheet, the following is my code which works perfectly:

While Not EOF(iFile)
        Line Input #iFile, LineText
            Dim arr
            arr = Split(CStr(LineText), vbTab)
            For j = 1 To UBound(arr)
                Worksheets("TxtRead").Cells(i, j).Value = arr(j - 1)
            Next

            i = i + 1
    Wend
    Close #iFile

So instead of fetching values to the spreadsheet, I would like to write them to a two-dimentional array, how would I do that? I have a code below, but it doesn't work:

Dim MemoryArray()
    While Not EOF(iFile)
        Line Input #iFile, LineText
            Dim arr
            arr = Split(CStr(LineText), vbTab)
            For j = 1 To UBound(arr)
                Worksheets("TxtRead").Cells(i, j).Value = arr(j - 1)
                MemoryArray(i - 1, j - 1) = arr(j - 1)
            Next

            i = i + 1
    Wend
    Close #iFile

Thanks for any inputs and thoughts!

The tricky part is knowing how to size your 2-D array, and the fact that you can only use Redim Preserve on the second dimension of a 2-D array. Also how do you handle cases where different lines have different numbers of elements. If you know up-front that all lines will have the same number of items then that helps. How large (# of rows/columns) are your files going to be? — Tim Williams
– Tim Williams, Commented Oct 24, 2016 at 23:06
Thanks for replying! The elements on columns would be same across all rows, and the file wont' be large, like hundreds of rows x around 10 columns. I did try the ReDim Preserve on the 2nd dimension from suggestions on other threads, but still cannot get it to work, spend hours on it and not able to figure it out. Please help me add it to my codes so I can see where I did wrong. Thanks! — walkens
– walkens, Commented Oct 25, 2016 at 1:46

Tim Williams · Accepted Answer · 2016-10-25 05:49:30Z

2

Sub Tester()

    Dim arr

    arr = FileToArray("D:\Stuff\test.txt")

    Debug.Print arr(1, 1), arr(10, 10) 'print some values

End Sub



Function FileToArray(fpath) As Variant

    Dim txt As String, arr, d, r, c, rv(), u

    'read in the entire file
    With CreateObject("scripting.filesystemobject").opentextfile(fpath)
        txt = .readall()
        .Close
    End With

    arr = Split(txt, vbCrLf) 'split lines to an array

    u = UBound(Split(arr(0), vbTab)) 'assume all lines have same # of fields
    ReDim rv(1 To UBound(arr) + 1, 1 To u + 1) 'size the output array

    'fill the output array
    For r = 0 To UBound(arr)
        d = Split(arr(r), vbTab)
        For c = 0 To u
            rv(r + 1, c + 1) = d(c)
        Next c
    Next r

    FileToArray = rv

End Function

answered Oct 25, 2016 at 5:49

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

walkens Over a year ago

Thanks! I tested it out but the line rv(r+1, c+1) = d(c) is giving me error "Subscript out of range". I also got the similar error when I was using my own script. Please advise.

Tim Williams Over a year ago

That will happen if the text file has a trailing new line, or if one of the rows has fewer than expected fields. This sort of thing is why it's easier just to let Excel handle the parsing by opening the file as a workbook.

walkens Over a year ago

Thanks for the explanation Tim, this is where I previously don't understand. I checked the txt file, it does have trailing tabs for all rows except the header row. So how do we deal with this situation in the code? Again, Thanks and appreciate your time!

Tim Williams Over a year ago

How would you expect it to behave in that case? This is why parsing text files is not an easy thing to do in a "predictable" way... (and that's not even considering quoted content which might contain your field separator, etc)

Collectives™ on Stack Overflow

How to fetch text file into array in VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related