如何统计多个TXT文件行数并记录到Excel？求最优实现方案

Hey there! Let's tackle your problem of counting rows (records) in multiple TXT files and exporting those results to Excel—no need to stress about complex VBA if you don't want to. Here are a few solid, easy-to-implement methods that work great:

Method 1: Power Query (No-Code, Recommended for Beginners) #

Power Query is built into most modern Excel versions and lets you automate this task without writing any code:

• Open a blank Excel workbook, go to the Data tab.
• Click Get Data > From File > From Folder, then select the folder where your TXT files are stored.
• In the resulting window, go to the Add Column tab and click Custom Column.
• Paste this formula into the custom column editor (it counts every line in each TXT file):

  List.Count(Text.Split([Content], "#(lf)"))
  

  Note: If you want to exclude empty rows, use List.Count(List.RemoveNulls(Text.Split([Content], "#(lf)"))) instead.
• Delete all columns except Name (the filename) and your new custom count column.
• Click Close & Load—the results will populate directly into your Excel sheet, with filenames in one column and row counts in the next.

Method 2: Simplified VBA Script (For Those Comfortable With Basic Code) #

If you're open to a tiny bit of VBA, this script is straightforward and handles the task automatically:

1. Open Excel, press Alt + F11 to open the VBA Editor.
2. Right-click your workbook name in the left pane > Insert > Module.
3. Paste this code into the module:

   Sub CountTxtRows()
       Dim filePicker As FileDialog
       Dim selectedFiles As Variant
       Dim i As Integer
       Dim filePath As String
       Dim rowCount As Long
       Dim fileSystem As Object
       Dim txtFile As Object
       
       ' Let user select multiple TXT files
       Set filePicker = Application.FileDialog(msoFileDialogFilePicker)
       With filePicker
           .Filters.Add "Text Files", "*.txt"
           .AllowMultiSelect = True
           If .Show <> -1 Then Exit Sub ' Exit if user cancels
           selectedFiles = .SelectedItems
       End With
       
       ' Set up header in Excel
       Range("A1").Value = "文件名"
       Range("B1").Value = "行数"
       
       Set fileSystem = CreateObject("Scripting.FileSystemObject")
       
       ' Loop through each selected file
       For i = LBound(selectedFiles) To UBound(selectedFiles)
           filePath = selectedFiles(i)
           Set txtFile = fileSystem.OpenTextFile(filePath, 1) ' Open in read-only mode
           
           ' Count each line
           rowCount = 0
           Do While Not txtFile.AtEndOfStream
               txtFile.ReadLine
               rowCount = rowCount + 1
           Loop
           txtFile.Close
           
           ' Write results to Excel
           Cells(i + 1, 1).Value = fileSystem.GetFileName(filePath)
           Cells(i + 1, 2).Value = rowCount
       Next i
       
       ' Clean up
       Set txtFile = Nothing
       Set fileSystem = Nothing
       Set filePicker = Nothing
       
       MsgBox "统计完成！"
   End Sub
   

4. Go back to Excel, press Alt + F8, select CountTxtRows, and click Run. Select your TXT files when prompted, and the script will fill column A with filenames and column B with row counts.

Method 3: CMD Command + Excel Import (Quick & Lightweight) #

If you prefer using command line tools, this is a fast option:

• Open Command Prompt, navigate to your TXT folder using cd C:\Path\To\Your\Txt\Files.
• Run this command to generate a list of filenames and their row counts:

  for %f in (*.txt) do @echo %f, find /c /v "" "%f"
  

• Copy the output from CMD into a Notepad file, save it as row_counts.csv.
• Open Excel, go to Data > From Text/CSV, select the CSV file, and load it into your sheet.

Pick the method that fits your comfort level—Power Query is the most user-friendly if you avoid code, while the VBA script gives you more control. All of these will get the job done efficiently!

内容的提问来源于stack exchange，提问作者Tiago Nijo