Fastest reading of large text files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have developed a macro that opens large text files (each has a list of
about 50,000 lines, almost all single numbers), reads them one at a time, and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it into the
next available column in the main sheet, close the text file, and repeat for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35 files).

My question: will I gain speed by using the command "Open FileName For Input
As #FileNum"? (I would try it myself; since there's a learning curve for me,
I'm hoping to get somebody's input first.)

Thanks.
 
Maybe this function can help you. It will put the text in a 1-D array.
You can specify the last row higher than the reality, the On Error Resume
Next
will take care of that. Once you have your data in the array you can do
whatever you
want with it and it will be much faster.

Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long) As Variant

Dim hFile As Long
Dim r As Long

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

For r = LBRow To UBRow
Input #hFile, arr(r)
Next r

Close #hFile

OpenTextFileToArray = arr

End Function


RBS
 
Look at this example:

Sub Test()

Dim i As Long
Dim arr(1 To 10)

OpenTextFileToArray "C:\TestFile2.txt", arr, 1, 10

For i = 1 To 10
MsgBox arr(i)
Next

End Sub


Just try it out on file. You may have to adjust it according to your target
file.


RBS
 
Thanks RB.

As I understand it, arr is an array of type Variant, which the function
OpenTextFileToArray is populating with the contents of the named text file.
But is it not an array in the PROGRAM? If so, how do I transfer this array
to a COLUMN in the WORKSHEET? That is my goal, to transfer a series of text
files into successive rows of a worksheet. I'm sorry if I did not say so
clearly.
 
OK, then you will have to alter it slightly.
Give the function a 2-D array and populate that.
So you will get something like this (aircode):

In the test sub:

Dim arr(1 to 10, 1 to 1)

OpenTextFileToArray "C:\TestFile2.txt", arr, 1, 10

range(cells(1), cells(10,1)) = arr


In the function:

For r = LBRow To UBRow
Input #hFile, arr(r,1)
Next r


RBS
 
Back
Top