Search code examples
vbaexcelrowcounter

How to quickly count the number of lines in multiple text files?

I have a more than 100 text files and I have to count the lines for each of them. Column A lists the file name, located in the folder specified in E1. Several files have more than 1 million lines, causing the script to run a terrifyingly long time.

screenshot

Sub counter()
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim longtext As String
    Dim lines As Variant
    Dim GoToNum As Integer
    Dim Start As Integer
    GoToNum = 2
    Start = 3

    Do Until IsEmpty(Cells(Start, 1))
        GoToNum = GoToNum + 1
        Start = Start + 1
    Loop

    For i = 3 To GoToNum
        If Cells(i, 2).Value <= Cells(2, 5).Value Then
            ConOrg = Cells(1, 4).Value & "\" & Cells(i, 1).Value

            Set ts = fso.OpenTextFile(ConOrg, ForReading, False)
            longtext = ts.ReadAll

            ts.Close
            lines = Split(longtext, vbLf)
            Cells(i, 3) = UBound(lines) - LBound(lines) - 1

        End If
    Next i
End Sub

How can I get the number of the last row (from the text file) to avoid the counting line by line?

Solution

  • How to count the lines in a text file using VBA:

    The fastest method depends on the size of the file(s) you're processing:

    # of lines filesize Open statement FileSystemObject
    one GIANT file 1.7 million 40mb × 1 ❌ 25.2 sec ✔️ 2.1 sec
    many sᴍᴀʟʟ files 6 14b × 10,000 ✔️ 1.3 sec ❌ 18.9 sec
    better for sᴍᴀʟʟ files better for BIG files

    → Better for small Files:

    Function countLF(fName As String) As Long
  Dim st As String
  Open fName For Input As #1: st = Input(LOF(1), 1): Close #1
  countLF = Len(st) - Len(Replace(st, vbLf, "")) + 1
End Function

    Example Usage:

    Debug.Print countLF("c:\test.txt")

    → Better for LARGE Files:

    Function countLines(fName As String) As Long
  countLines=CreateObject("Scripting.FileSystemObject").OpenTextFile(fName, 8, True).Line
End Function

    Example Usage:

    Debug.Print countLines("c:\test.txt")

    More Benchmarking of other tested variations: (2500 tiny text files)
    Binary Access/Get (4.32s) Kill=1.17s . . . Open F `For Binary Access Read As #1:ReDim...Get #1,,bytes
    Line Input/LineInput (4.44s) Kill=1.11s . . . Open F For Input As #iFile...Line Input #1,st
    Early Bind/ReuseObj (5.25s) Del=1.12s . . . Set o=New Scripting.FileSystemObject':st=o.OpenTextFile(F).ReadAll()
    Early Bind/FreshObj (11.98s) Del=1.35s . . . Set o=New Scripting.FileSystemObject':st=o.OpenTextFile(F).ReadAll()
    LateBind/ReuseObj (6.25s) Del=1.47s . . . Set o=CreateObject("Scripting.FileSystemObject")
    LateBind/FreshObj (13.59s) Del=2.29s . . . With CreateObject("Scripting.FileSystemObject")