Programmatic workbook exports

Export Every XLSX Worksheet to CSV with a Manifest

Convert every worksheet in multiple XLSX workbooks into separate CSV files, using collision-safe output names and a manifest that preserves source identities.

By Uddhav Gopani, FounderPublished 27 September 2026Updated 29 September 2026

Export Every XLSX Worksheet to CSV with a Manifest

To export every worksheet from multiple XLSX workbooks, create one CSV per worksheet and keep a manifest linking each output to its original workbook and sheet. CSV has no worksheet tabs, so combining all tabs into one file without a mapping loses their identities. The example below uses numeric output names to avoid filename collisions.

Choose an all-worksheet export deliberately

This workflow scans the immediate workbooks folder for XLSX files and visits every worksheet in workbook order, including hidden worksheets. Use a reviewed sheet whitelist instead if hidden or unrelated sheets should be excluded. Chart-only sheets are not part of the worksheet iteration.

Each worksheet must contain a rectangular table with nonblank, unique text headers in its first row. A cover sheet, blank template tab or merged summary may fail this contract. That failure indicates that the workbook needs a more specific selection or export design.

Write separate CSV files and an identity manifest

Install openpyxl and place the script beside the workbooks folder. It validates all selected worksheet tables, then creates a new csv-sheets directory. The manifest records workbook names, worksheet names, output filenames, data-row counts and column counts.

import csv
from datetime import date, datetime, time
from pathlib import Path
from openpyxl import load_workbook

def read_table(ws):
    if ws.max_row * ws.max_column > 200_000:
        raise ValueError(f"{ws.title}: range exceeds this example's limit")
    if ws.merged_cells.ranges:
        raise ValueError(f"{ws.title}: resolve merged cells first")
    rows = []
    for row in ws.iter_rows():
        values = []
        for cell in row:
            if cell.data_type in {"f", "e"}:
                raise ValueError(f"{ws.title}!{cell.coordinate}: formula or error")
            value = cell.value
            if value is None:
                value = ""
            elif isinstance(value, bool):
                value = "TRUE" if value else "FALSE"
            elif isinstance(value, (datetime, date, time)):
                value = value.isoformat()
            values.append(value)
        rows.append(values)
    header = rows[0] if rows else []
    if not header or any(not isinstance(x, str) or not x.strip() for x in header):
        raise ValueError(f"{ws.title}: first row needs nonblank text headers")
    if len(set(header)) != len(header):
        raise ValueError(f"{ws.title}: duplicate header")
    return rows

def write_csv(path, rows):
    with path.open("x", encoding="utf-8", newline="") as target:
        csv.writer(target, lineterminator="\r\n").writerows(rows)

source_dir = Path("workbooks")
output_dir = Path("csv-sheets")
files = sorted(p for p in source_dir.iterdir()
               if p.is_file() and p.suffix.lower() == ".xlsx"
               and not p.name.startswith("~$"))
if not files:
    raise ValueError("No .xlsx workbooks found")
exports = []
manifest = [["workbook", "sheet", "csv_file", "data_rows", "columns"]]
for bi, source in enumerate(files, 1):
    book = load_workbook(source, data_only=False, keep_links=False)
    try:
        for si, sheet in enumerate(book.worksheets, 1):
            rows = read_table(sheet)
            name = f"book{bi:03d}-sheet{si:03d}.csv"
            exports.append((name, rows))
            manifest.append([source.name, sheet.title, name,
                             len(rows) - 1, len(rows[0])])
    finally:
        book.close()
output_dir.mkdir()
for name, rows in exports:
    write_csv(output_dir / name, rows)
write_csv(output_dir / "manifest.csv", manifest)

Use the manifest instead of guessing filenames

An output such as book002-sheet003.csv refers to the third worksheet in the second selected workbook for that run. The manifest supplies the actual source names. This avoids embedding worksheet punctuation or repeated names directly into filesystem paths.

The numeric names are stable only for a fixed ordered input set and worksheet order. Adding a workbook earlier in the sorted sequence can change later numbers. Treat each output folder and its manifest as one run, and use the recorded source identities when comparing runs.

Interpret the recorded dimensions

The data_rows value excludes the first header row but includes blank rows inside the worksheet’s used rectangle. Columns records the exported rectangle width. These are structural counts, not a count of nonempty business records.

Check a two-workbook fixture in which the first workbook has Data and Notes sheets and the second has one Data sheet. The expected output is three table CSVs plus the manifest. The manifest should retain both distinct Data sheet identities rather than merging them by name.

Review the value and formula policy

The export function preserves ordinary scalar values through CSV serialization, converts recognized date/time values to ISO text and writes booleans as TRUE or FALSE. Blank cells become empty fields. Quoting preserves embedded commas and line breaks as part of a field.

Formulas, error cells and merged ranges are rejected. The script does not calculate formulas, infer which merged value belongs in each row or reproduce Excel’s visual number formatting. A numeric value displayed with leading zeros differs from an identifier actually stored as text.

Test failure cases before a large run

Add a formula to a later worksheet and confirm that validation stops before csv-sheets is created. Test a worksheet with duplicate headers and another with a merged title row. These checks prevent an apparently complete batch from silently exporting an unsuitable table.

Then run a second valid fixture with changed values and a different sheet count. Parse each CSV with csv.reader and compare field values and dimensions with its manifest row. Counting physical lines is insufficient because quoted text can contain embedded line breaks.

Keep the output and scale limitations explicit

The destination folder must be new; the script does not merge into or overwrite an existing export. Table validation happens before output creation, but an operating-system failure during the write phase can still leave partial files. Verify the manifest and expected output set before handing the batch to another process.

The program retains all validated tables in memory and applies a per-sheet rectangle limit. For very large batches, use a designed streaming or staged workflow with an equally clear manifest and failure policy. The original XLSX files are read and closed without being saved.

See the openpyxl reader options and Python CSV reference for the APIs used in this example.