Programmatic workbook exports

Convert an Excel Worksheet to CSV with Explicit Data Rules

Export a named XLSX worksheet to UTF-8 CSV with Python, explicit value handling and checks for formulas, merged cells and headers before creating output.

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

Convert an Excel Worksheet to CSV with Explicit Data Rules

To convert an Excel worksheet to CSV programmatically, choose the worksheet, define how its cell values become text and use a CSV writer to quote fields correctly. The Python example below exports a small rectangular table from an XLSX file. It deliberately rejects formulas, error cells and merged cells rather than silently inventing their values.

Define the export contract

The source is input.xlsx, the worksheet is Data and the destination is output.csv. The first row must contain nonblank, unique text headers. The example exports the worksheet’s used rectangle, including blank cells and blank rows within that range, up to its stated cell limit.

CSV retains tabular values, not workbook formatting, charts, validation rules or multiple worksheets. A text identifier such as 00123 stays text in the written file; a numeric cell displayed with a custom zero-padding format exports its numeric value instead. Decide whether those are the values your receiving system expects.

Run a values-only export with checks

Install openpyxl in your Python environment, place the workbook beside the script and adjust the three settings at the end. The script opens the workbook without saving it and creates a new output file only after table validation succeeds.

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 = Path("input.xlsx")
target = Path("output.csv")
sheet_name = "Data"
if source.suffix.lower() != ".xlsx":
    raise ValueError("This example accepts .xlsx only")
book = load_workbook(source, data_only=False, keep_links=False)
try:
    rows = read_table(book[sheet_name])
finally:
    book.close()
write_csv(target, rows)

Understand the value conversions

Blank cells become empty CSV fields. Booleans become TRUE or FALSE. Date, datetime and time values recognized by openpyxl use their ISO representations. Other ordinary scalar values pass to Python’s CSV writer, which converts them to text and handles commas, quotes and line breaks inside fields.

A CSV field does not retain a separate type tag. An empty cell and a deliberately empty string can become indistinguishable, and a later spreadsheet import may infer numbers or dates from text. Import identifier columns as text when leading zeros must remain part of the identifier.

Choose a formula policy explicitly

Openpyxl reads formulas but does not calculate them. Its data_only option can expose cached values saved by a spreadsheet application; those caches may be absent or outdated. This example instead reads formulas as formulas and stops if it encounters one.

If calculated values are required, recalculate and review a separate workbook in a suitable spreadsheet application, then use a documented export process for those values. Merely switching data_only on does not prove that the exported numbers reflect the latest inputs.

Verify the saved CSV structurally

For a table containing id, name and note headers, test a row with the identifier 00123, a name containing a comma and a note containing a line break. Reopen the CSV with csv.reader and confirm that the row still has three fields and that each value is intact.

Do not count physical lines to determine the number of CSV records: a quoted field may contain a line break. Also test a second workbook with different values so the verification cannot accidentally inspect an earlier output.

Interpret failures before changing the script

A missing Data sheet means the worksheet setting does not match the workbook. A blank or duplicate header may indicate an incorrect table range or an extra styled column. Resolve that source structure instead of deleting the check solely to make the script finish.

The destination uses exclusive creation and will not overwrite an existing output.csv. Choose a new destination or deliberately manage an older export. Validation occurs before writing, although an operating-system write failure can still leave an incomplete new file that should be discarded and regenerated.

Keep the example’s scale visible

This example loads and validates the selected table in memory and limits its rectangle to two hundred thousand cells. It is intended as an understandable workflow for modest tables, not a streaming solution for arbitrary workbook sizes. The original XLSX is never saved over.

Consult the openpyxl loading options and Python CSV documentation when adapting the format or parser settings.