Read Number of Rows in Text File

[idbr]

Hi there,

I need to open a sequence of text files and get the row count for each file. I'm happy enough working with FileSystemObject to be able to find and loop through the files, I just don't know how I can get the row count for each.

Any helpers?

Thanks, Iain

 

[Chance1234]

Sub test()
    
    Dim sFile, sinput As String
    Dim lRow As Long
    Dim lFNum As Long
    
    lFNum = FreeFile
    sFile = "F:\tektips\test.txt"

    'Open the file
    Open sFile For Input As lFNum
    
    'Loop through the file until the end
    Do While Not EOF(lFNum)
     Line Input #lFNum, sinput
        lRow = lRow + 1
    Loop
   
    
    Close lFNum
     
    MsgBox lRow
End Sub

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[PHV]

A starting point:
Set inp = fspenTextfile("\path\to\text.txt", 1)
RowCount = UBound(Split(inp.ReadAll, vbCrLf)) - 1
inp.Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

PHV (& idbr),

I thought using your approach was the way to go, as well. But my benchmark testing shows that Chance1234's direct approach is approximately 20 times faster on the same moderate-size text file (~300K). And in fact, the method appeared to lock up on a large text file (~7.5 meg) while Chance1234's code completed in approximately one second on my system.

Hope this helps.
Mike

 

[MichaelRed]

see thread222-535247

The post re basGrabFile illustrates one easy and fast approach. The KEY element is to get the entire file in "Memory" in the single statement. This does not lock up on large (up to ~~ 75 MBytes anyway) and return the row (e.g. line == record) count nearly instantly.




MichaelRed

 

[rmikesmith]

Kudos to MichaelRed. Using the same benchmarking as before, his function is approximately 3x faster than that proposed by Chance1234.

The KEY element is to get the entire file in "Memory" in the single statement

Yes, that is what the (FSO) TextStream.ReadAll is supposed to do but is very slow (100x slower than MichaelRed's function) and appears to lock up on very large files (although this may simply reflect the extended processing time).


I'll add this function to my toolbox.

Regards,
Mike