CODEWITHSUNDEEP
excelvba
lhp2290
lhp2290

Reputation: 3

How to read multiple text files (with multiple lines of data) into a two dimensional array?

DESCRIPTION:

I have 10 text files. And I want to read them into a two dimensional array. Each file looks like this and the number of lines vary. There are 5 lines in this text file but other text files may have more or less than 5 lines.

No. Location(ft) Mix Near Far Comp. Height(ft) B(in) D(in)  Angle(degrees)  
1   (0.8127,8.66)   35 MPa  true    true    true    9.17    10  36  0   
2   (0.8333,60.67)  35 MPa  true    true    true    9.17    10  36  0   
3   (0.8333,80.42)  35 MPa  true    true    true    9.17    10  36  0   
4   (14.19,26.22)   35 MPa  true    true    true    9.17    10  24  0

The first dimension of the array will contain each of line the text file. The second dimension of the array will be each text file.

So something like this Redim TotalArray(1 to 1000, 1 to 10)

1000 is definitely more than the number of lines that I have. 10 is for the 10 text files

FINAL PURPOSE:

The last step would be further split the columns of each text file. In other words, I may need a three dimensional array? However, I am testing the codes for the two dimensional array first. I just state my final purpose in case my approach will be futile and you guys can suggest something better.

My codes are as follows

Sub GetFile()

'Loop through all files in a folder

Dim fileName As String
fileName = Dir("C:\*.txt")

Dim arNames() As String
Dim myCount As Integer

myCount = -1

Do Until fileName = ""
    myCount = myCount + 1
    ReDim Preserve arNames(myCount)
    arNames(myCount) = fileName
    fileName = Dir
Loop

' Finish reading file names into arNames.
' This part of the code is successful

Dim TextArray()

Dim x As Double
Dim k As Integer
Dim UBound_arNames As Integer
UBound_arNames = UBound(arNames())

ReDim TotalArray(0 To 1000, 0 To UBound_arNames)


For k = 0 To 2


Open "C:\" & arNames(k) For Input As #1

' Open file.

Do While Not EOF(1)             ' Loop until end of file.

ReDim Preserve TextArray(x)  ' Preserve the Array

Line Input #1, TextArray(x) ' Read line into variable.

TotalArray(x, k) = TextArray(x)

' The bug is the above line. TextArray(x) works fine but it cannot be 
' written to TotalArray(x, k). I need the second dimension k to make
' the second dimension contains the number of text files that I have
' I know the bug is here because MsgBox TextArray(0) works 
' but MsgBox TotalArray(0,0) or any other cell prints nothing


x = x + 1                   ' increment array count

Loop

Close #1                        ' Close file.

MsgBox TextArray(0)
MsgBox TextArray(1)

Next k

End Sub

Please help me

Upvotes: 0

Views: 84

Answers (1)

teylyn
teylyn

Reputation: 35915

You may be overthinking this and you may be using the wrong tool. This can easily be done with Power Query. Put all files into one folder. Get Power Query to read and append all files. Doesn't matter how many there are in the folder.

In Power Query you can just click through this with ribbon commands. You won't need to write code (although you can get creative with M if you want to). Data > Get Data > From File > From Folder. Take it from there.

When the data in the files changes, or when there are more or fewer files in the folder, just refresh the query.

Upvotes: 2

Related Questions