TheSwamp
Code Red => VB(A) => Topic started by: havano on October 29, 2007, 11:24:31 AM
-
I want to read lines of an arbitrary txt file into a string array. But I don't want to use Redim Preserve and I don't want to have to open the file twice: once for counting the # of lines and then again, after declaring a string array using the line count for it's index, in order to read the text lines into this array. There should be a "cleaner" method.
My question: is there a standard function that returns the number of lines?
-
'' Read the entire file into a string variable via a binary get
'' and then use the split function to parse it into a string array.
'' There may be other ways to skin this cat in VBA but I challenge
'' them (invitation extended) to perform faster.
''
'' Minimalist code, observe --
Sub Test()
Dim streams() As String
'' please use a valid path in the following
streams = FileToArray("c:\docs\text.txt")
Debug.Print (UBound(streams) - LBound(streams)) + 1; "line(s) of text retrieved."
End Sub
Function FileToArray(filename As String) As String()
On Error GoTo errhandler
Dim handle As Integer, _
stream As String
handle = FreeFile
Open filename For Binary As #handle
stream = Space(LOF(handle))
Get #handle, , stream
Close handle
FileToArray = Split(stream, vbCrLf)
Exit Function
errhandler:
Close handle
Resume exitnow
exitnow:
End Function
-
Never saw Split before. Indeed, this solution requires minimal coding. Thanks!
-
Indeed, this solution requires minimal coding. Thanks!
It's not so much the minimal coding, though I do appreciate the brevity of it, but the performance is darned good -- even on very large files.
A quick test on a text file with 64,000 lines (each 128 chars long) of text was near instantaneous.
Like I said, I'd personally be interested in seeing a vba alternative that out performs it.
:)
-
Here is my 2 cents, partially stoled from MP :)
Option Explicit
Sub test()
Dim streams() As String
'' please use a valid path in the following
'' btw size of localldd.txt is 45.6 Mb
'' you can get this file from:
'' http://www.gomr.mms.gov/homepg/pubinfo/freeasci/pipeline/freepipe.html
streams = FileToArray("D:\VBA_TEST\localldd.txt")
Debug.Print (UBound(streams) - LBound(streams)) + 1; "line(s) of text retrieved."
End Sub
Function FileToArray(fName As String) As String()
Dim txt As String
Dim fs As Object
Dim fl As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set fl = fs.OpenTextFile(fName)
txt = fl.ReadAll
fl.Close
FileToArray = Split(txt, vbCr)
End Function
~'J'~
-
Spasiba. Dasvidanja Fatty!
-
Spasiba. Dasvidanja Fatty!
De nada
Cheers :)
~'J'~
-
Hey Guys
That is really cool!
I know there is an obvious need for this or the question would not have been asked but I am wondering how I can expound on this?
Thanks
Mark
-
Oh Fatty, also, why the +1?
Mark
-
Even I can answer that ;-)
Suppose, you declare an array MyArray (0 to 4). Then the LBound = 0 and the UBound = 4.
Obviously, 4 - 0 = 4. Yet, the array can hold 5 values (index range = 0 1 2 3 4).
Hence the "+ 1".
Glad I could be of some service :-)
The reason for my question was to achieve maximum speed. So I wanted minimal memory/values reallocation (Redim Preserve) and/or hard drive activity (opening/reading a file twice).