Programmatic workbook exports

Batch Export the Same Worksheet from Multiple Excel Files

Export the Data worksheet from each XLSX workbook into separate CSV files, checking the whole batch before creating a new output folder.

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

Batch Export the Same Worksheet from Multiple Excel Files

To convert several Excel files to CSV as a batch, decide whether you want one worksheet from each workbook or every worksheet. This example exports the worksheet named Data from each XLSX file in a folder. It keeps the workbooks separate and verifies the batch before creating its output directory.

Use one table contract across the batch

Create a workbooks folder containing the XLSX files you intend to process. Each must contain a Data worksheet with nonblank, unique text headers in its first row. Temporary Excel lock files beginning with ~$ are skipped. Other extensions are outside this example’s input set.

The output names use each workbook’s base name plus .csv. Two workbook names that would collide without regard to letter case cause an error. The batch does not append tables together, so different workbooks can have different header sets unless your downstream process requires a shared schema.

Validate first, then create separate files

Install openpyxl, save this script beside the workbooks folder and use a new csv-output destination. The script reads the selected sheet from every input before creating that destination. A missing sheet, invalid header, formula or merged cell therefore stops validation before output creation.

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-output")
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")
names = [p.stem + ".csv" for p in files]
if len({name.casefold() for name in names}) != len(names):
    raise ValueError("Output names would collide")
exports = []
for source, name in zip(files, names):
    book = load_workbook(source, data_only=False, keep_links=False)
    try:
        exports.append((name, read_table(book["Data"])))
    finally:
        book.close()
output_dir.mkdir()  # Refuse an existing output directory.
for name, rows in exports:
    write_csv(output_dir / name, rows)

Confirm exactly which files and sheets were selected

The file scan is non-recursive and selects files whose extension is .xlsx without regard to case. It does not convert legacy .xls, macro-enabled .xlsm or files hidden in subfolders. Convert those through an appropriate, separately verified workflow if they are part of your intended batch.

The Data sheet choice is explicit. Renaming one workbook’s sheet to Sales will cause that workbook to fail this script rather than silently exporting its first or active sheet. If your workbooks use different names, create a reviewed mapping from file to sheet instead of relying on whichever tab happens to be active.

Preserve values without claiming workbook fidelity

The common export function writes blank cells as empty fields, booleans as TRUE or FALSE and recognized date/time values in ISO form. Commas, quotes and embedded line breaks are handled by csv.writer. It does not copy colors, number formats, charts or formulas into a CSV workbook equivalent.

Formulas and Excel error cells are rejected. That policy prevents a batch from mixing formula text, stale cached results and current values without explanation. If the destination requires calculated values, establish and test a calculation policy before adapting the script.

Check the batch as a set

For a first fixture, put north.xlsx and south.xlsx in the input folder, each with a Data sheet and different rows. Confirm that north.csv contains only the northern table and south.csv only the southern table. Verify identifiers with leading zeros, Unicode names and a field containing a comma.

Then test a missing Data sheet and an existing csv-output folder. Neither case should silently reuse an old CSV. Compare input workbook bytes before and after a run if you need an explicit record that the source files were not changed.

Treat a failed write differently from failed validation

All table validation completes before the destination folder is created, but the subsequent file writes are not a multi-file transaction. A disk or permission error during writing can leave a partial new folder. Compare the expected file list with the files actually written before treating the batch as complete.

Keep the inputs and choose another new destination for a retry after resolving the error. The script refuses an existing output directory to reduce the chance that old and new exports will be mistaken for one complete run.

Adjust the design for larger jobs

Each worksheet has a two-hundred-thousand-cell rectangle limit, and validated tables are retained in memory until writing begins. Many workbooks can therefore require substantial memory even when each is individually small. Start with representative samples before selecting a large folder.

The openpyxl tutorial explains workbook and worksheet access, while Python’s CSV documentation describes the writer used here. Adapt the selection and validation rules to the actual batch contract, then repeat the same structural checks.