寻求Python代码替代VBA提取大量大文本文件指定行

Hey Jon, totally feel your pain—VBA works great for smaller jobs, but it quickly hits limits when you're dealing with tons of large text files. Python is perfect for this scenario because it handles big files efficiently (no loading the entire file into memory!) and makes batch processing a breeze. Let's walk through some solid solutions:

Basic Script: Extract Specific Lines to New Files #

This script will loop through all text files in your source folder, pull out the lines you specify, and save each set of extracted lines to a new file in a target folder.

import os

def extract_specific_lines(source_dir, target_dir, line_numbers):
    # Create target folder if it doesn't exist
    os.makedirs(target_dir, exist_ok=True)
    
    # Loop through every file in the source directory
    for filename in os.listdir(source_dir):
        file_path = os.path.join(source_dir, filename)
        # Skip folders, only process .txt files (adjust extension if needed)
        if os.path.isfile(file_path) and filename.endswith('.txt'):
            target_file_path = os.path.join(target_dir, f"extracted_{filename}")
            
            # Open source file for reading, target for writing
            with open(file_path, 'r', encoding='utf-8') as source_file, open(target_file_path, 'w', encoding='utf-8') as target_file:
                for line_index, line_content in enumerate(source_file, start=1):
                    # Save the line if it's in our target list
                    if line_index in line_numbers:
                        target_file.write(line_content)
                    # Optional: Stop reading early once we pass the last needed line
                    if line_index > max(line_numbers):
                        break

# How to use it
source_folder = r"C:\Your\Path\To\Source\Files"
target_folder = r"C:\Your\Path\To\Save\Extracted\Files"
lines_you_want = {2, 5, 10}  # Replace with your target line numbers
extract_specific_lines(source_folder, target_folder, lines_you_want)

Key Benefits: #

• Memory-efficient: Reads files line by line, so even 1GB+ text files won't crash your system.
• Batch-friendly: Automatically processes all files in the source folder.
• Fast: Skips reading the rest of the file once it passes the highest line number you need.

Option: Extract Lines Directly to Excel #

If you want to compile all extracted lines into a single Excel sheet (with each file's data in a row), use this script with pandas—it's clean and straightforward.

First, install the required packages if you haven't:

pip install pandas openpyxl

Then run this code:

import os
import pandas as pd

def extract_lines_to_excel(source_dir, excel_output_path, line_numbers):
    collected_data = []
    
    for filename in os.listdir(source_dir):
        file_path = os.path.join(source_dir, filename)
        if os.path.isfile(file_path) and filename.endswith('.txt'):
            # Store filename and extracted lines in a dictionary
            file_results = {"Filename": filename}
            with open(file_path, 'r', encoding='utf-8') as file:
                for line_index, line_content in enumerate(file, start=1):
                    if line_index in line_numbers:
                        file_results[f"Line {line_index}"] = line_content.strip()
                    if line_index > max(line_numbers):
                        break
            collected_data.append(file_results)
    
    # Convert to a DataFrame and save to Excel
    df = pd.DataFrame(collected_data)
    df.to_excel(excel_output_path, index=False, engine='openpyxl')

# Example usage
source_folder = r"C:\Your\Source\Folder"
excel_file = r"C:\Your\Output\Extracted_Data.xlsx"
lines_you_want = {2, 5, 10}
extract_lines_to_excel(source_folder, excel_file, lines_you_want)

Speed Boost: Multithreading for Large Batches #

If you have hundreds or thousands of files, multithreading will cut down processing time significantly (since file reading is an IO-intensive task that benefits from parallel processing).

import os
from concurrent.futures import ThreadPoolExecutor

def process_single_file(file_details):
    filename, source_dir, target_dir, line_numbers = file_details
    file_path = os.path.join(source_dir, filename)
    if os.path.isfile(file_path) and filename.endswith('.txt'):
        target_file_path = os.path.join(target_dir, f"extracted_{filename}")
        with open(file_path, 'r', encoding='utf-8') as source_file, open(target_file_path, 'w', encoding='utf-8') as target_file:
            for line_index, line_content in enumerate(source_file, start=1):
                if line_index in line_numbers:
                    target_file.write(line_content)
                if line_index > max(line_numbers):
                    break

def extract_with_multithreading(source_dir, target_dir, line_numbers, max_workers=4):
    os.makedirs(target_dir, exist_ok=True)
    # Prepare file details for each worker
    file_tasks = [(f, source_dir, target_dir, line_numbers) for f in os.listdir(source_dir)]
    
    # Run tasks in parallel
    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        executor.map(process_single_file, file_tasks)

# Example usage
source_folder = r"C:\Your\Source\Folder"
target_folder = r"C:\Your\Target\Folder"
lines_you_want = {2, 5, 10}
extract_with_multithreading(source_folder, target_folder, lines_you_want)

Pro Tips: #

• Adjust max_workers based on your CPU (4-8 is a safe starting point).
• If your files use a non-UTF-8 encoding (like GBK or Latin-1), update the encoding parameter in open() to match.
• For even more speed with extremely large files, look into mmap (memory-mapped files), but the above scripts work for most cases.

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