Python将TXT转换为XLSX时的分块与索引问题

Hey there, sorry to hear you're hitting this row limit snag when converting your text script to XLSX—total pain, right? Standard XLSX sheets cap out at 1,048,576 rows, so your chunking or write logic is probably misaligned with this hard limit. Let's walk through how to fix this step by step.

First, let's unpack your error: the traceback points directly to df_chunk.to_excel(), which means either your chunk size is larger than the XLSX row limit, or there's an issue with how you're reusing the ExcelWriter object when adding new sheets.

Fix 1: Proper Chunking with Pandas Read/Write #

If your TXT file uses a standard delimiter (like tabs, commas, or spaces), pandas' built-in chunking tools will make this easy. Here's a revised script that respects the row limit:

import pandas as pd
from openpyxl import load_workbook

# Configure your paths and chunk size (leave a small buffer below the 1M+ row limit)
CHUNK_SIZE = 1000000  # Safe under the 1,048,576 row cap
INPUT_TXT = "/Users/rbarrett/Git/Cleanup/yourPeople3/your_input.txt"
OUTPUT_XLSX = "/Users/rbarrett/Git/Cleanup/yourPeople3/output.xlsx"

first_write = True
for sheet_num, chunk in enumerate(pd.read_csv(INPUT_TXT, sep="\t", chunksize=CHUNK_SIZE), start=1):
    # Use openpyxl engine for append mode support
    with pd.ExcelWriter(
        OUTPUT_XLSX,
        engine="openpyxl",
        mode="a" if not first_write else "w"
    ) as writer:
        # Write each chunk to a new sheet, skip index to avoid extra columns
        chunk.to_excel(writer, sheet_name=f"Sheet{sheet_num}", index=False)
        first_write = False

Key Notes for This Approach: #

• Chunk Size: We set CHUNK_SIZE to 1,000,000 to leave room for headers and avoid hitting the exact limit (some edge cases with hidden rows or formatting can eat into that cap).
• ExcelWriter Mode: We use mode="w" for the first chunk to create the file, then switch to mode="a" to append new sheets without overwriting existing ones.
• Index Handling: Your index=False is correct—this prevents pandas from writing its default DataFrame index as an extra column in Excel, which is probably what you want for a text script conversion.

Fix 2: Custom Chunking for Non-Standard TXT Files #

If your text script isn't delimited (e.g., it's raw line-by-line text with custom formatting), you'll need to build a custom chunk reader. Here's how to do that:

import pandas as pd

CHUNK_SIZE = 1000000
INPUT_TXT = "/Users/rbarrett/Git/Cleanup/yourPeople3/your_input.txt"
OUTPUT_XLSX = "/Users/rbarrett/Git/Cleanup/yourPeople3/output.xlsx"

def read_txt_in_chunks(file_path, chunk_size):
    """Yield chunks of lines from the TXT file as DataFrames"""
    chunk = []
    with open(file_path, "r", encoding="utf-8") as f:
        for line_num, line in enumerate(f, start=1):
            cleaned_line = line.strip()
            if cleaned_line:  # Skip empty lines
                # Adjust this split/parse logic to match your text's structure
                chunk.append([cleaned_line])
            # Yield the chunk when we hit the size limit
            if line_num % chunk_size == 0:
                yield pd.DataFrame(chunk, columns=["Script Line"])
                chunk = []
        # Yield any remaining lines after the loop ends
        if chunk:
            yield pd.DataFrame(chunk, columns=["Script Line"])

first_write = True
for sheet_num, chunk_df in enumerate(read_txt_in_chunks(INPUT_TXT, CHUNK_SIZE), start=1):
    with pd.ExcelWriter(
        OUTPUT_XLSX,
        engine="openpyxl",
        mode="a" if not first_write else "w"
    ) as writer:
        chunk_df.to_excel(writer, sheet_name=f"Sheet{sheet_num}", index=False)
        first_write = False

Debugging Tips #

• Print chunk sizes to verify they're under the limit: add print(f"Sheet {sheet_num} has {len(chunk_df)} rows") inside the loop.
• If you still get errors, check if your TXT file includes duplicate headers in each chunk—you'll need to skip those in subsequent chunks (use skiprows=1 in pd.read_csv() for non-first chunks, or adjust the custom reader to skip the header line after the first chunk).

备注：内容来源于stack exchange，提问作者R. Barrett