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

Question

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.

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?

Possible duplicate of [EXCEL-VBA better way to find last row](https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row) — Samuel Hulla, Jun 11 '18 at 12:33
@Black Cornail: No, this question is about getting the line count in a text file. — FunThomas, Jun 11 '18 at 12:37

score 12 · Accepted Answer · edited Apr 15 '23 at 02:29

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")}

In the table above, shouldnt the Better for Big and Better for Small labels be switched? — psycoperl, Apr 11 '23 at 23:42
@psycoperl it seems so when looking at the ✔ and the text after, so I've made the edit. — DecimalTurn, Apr 15 '23 at 02:30

score 3 · Answer 2 · answered Jun 11 '18 at 12:36

Give this function a try. It uses the FileSystemObject. Should be faster than read the whole file and split it into single lines. Inspired from Hey, Scripting guy

Function countLines(fName As String) As Long

    Const ForReading = 1
    Dim objFSO  As Object, objTextFile As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile(fName, ForReading)
    objTextFile.ReadAll
    countLines = objTextFile.Line
End Function

score 2 · Answer 3 · answered Jun 11 '18 at 14:42

An alternative approach would be to use Power Query (Get & Transform Data):

let
    Source = Folder.Files("C:\Users\me\MyFolder"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".txt"),
    #"Added Row Count" = Table.AddColumn(#"Filtered Rows", "Rows In File", each Table.RowCount(Table.FromColumns({Lines.FromBinary([Content])})), Int64.Type),
    #"Removed Columns" = Table.SelectColumns(#"Added Row Count",{"Name", "Rows In File"})
in
    #"Removed Columns"

This works pretty fast.

score -1 · Answer 4 · answered Jun 11 '18 at 12:39

-1

I dont think you can read last line with just a method.

Do While fso.AtEndOfStream <> True
    fso.SkipLine
Loop

lines = fso.Line-1

something like this would not be faster?

answered Jun 11 '18 at 12:39

Radek Piekný

145
6

1

That's still reading through every line one at a time, as opposed to [opening the entire file at once](https://stackoverflow.com/a/50798424/8112776). – ashleedawg Jun 11 '18 at 13:24

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

4 Answers4

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

→ Better for small Files:

→ Better for LARGE Files:

→ Better for ^_small Files: