Convert Excel to CSV with Python

CSV is the format every tool can read and no tool can misinterpret — provided the conversion is done carefully. Done carelessly it turns 00412 into 412, 15/01/2026 into 2026-01-15 00:00:00, and an accented name into mojibake. This guide, part of Working with Large Excel Files in Python, covers the conversion and each of those traps.

Prerequisites

pip install pandas openpyxl

The one-liner, and why it is not enough

import pandas as pd

pd.read_excel("orders.xlsx", sheet_name="Orders").to_csv("orders.csv", index=False)

That is a valid conversion and it is fine for a sheet of plain numbers. On real data it silently changes three things: identifier columns lose leading zeros, dates gain a midnight timestamp, and any column containing one stray text value arrives as object and is written with nan where blanks were.

A conversion that preserves the data

Read raw, decide each column's treatment explicitly, then write:

import pandas as pd

TEXT_COLUMNS = ["Code", "SKU"]          # identifiers must stay text
DATE_COLUMNS = ["Order_Date"]

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=object)

for column in TEXT_COLUMNS:
    if column in df.columns:
        df[column] = df[column].astype("string")

for column in DATE_COLUMNS:
    if column in df.columns:
        df[column] = pd.to_datetime(df[column], errors="coerce").dt.strftime("%Y-%m-%d")

df.to_csv(
    "orders.csv",
    index=False,
    encoding="utf-8",
    na_rep="",                 # blanks stay blank, not "nan"
    lineterminator="\n",
)
print(open("orders.csv").readline().strip())

dtype=object stops the inference that damages identifiers, and strftime writes a date rather than a timestamp — CSV has no date type, so the string you write is the format. na_rep="" matters more than it looks: the default writes an empty field anyway for NaN, but an object column containing the literal string "nan" will otherwise travel through unchanged, and downstream readers treat the two differently.

Every sheet to its own file

A workbook is many tables; CSV holds one. Name the outputs after the sheets and sanitise the filenames:

import re
from pathlib import Path

import pandas as pd

def sheets_to_csv(path, outdir="csv", encoding="utf-8"):
    Path(outdir).mkdir(parents=True, exist_ok=True)
    book = pd.ExcelFile(path)
    written = []
    for sheet in book.sheet_names:
        frame = book.parse(sheet, dtype=object)
        if frame.empty:
            print(f"skipping empty sheet {sheet!r}")
            continue
        safe = re.sub(r"[^A-Za-z0-9._-]+", "_", sheet).strip("_") or "sheet"
        target = Path(outdir) / f"{safe}.csv"
        frame.to_csv(target, index=False, encoding=encoding, na_rep="")
        written.append(target)
    return written

for target in sheets_to_csv("workbook.xlsx"):
    print("wrote", target)

Sanitising is not optional on Windows, where a sheet named Q1/Q2 produces a path that cannot be created. Skipping empty sheets avoids a directory full of files containing nothing but a header — usually the leftovers of a template.

Encodings, separators and Excel round trips

CSV has no metadata, so the reader has to guess these, and a mismatch is the classic cause of a file that "looks corrupted":

df.to_csv("for_excel.csv", index=False, encoding="utf-8-sig", sep=";", lineterminator="\r\n")

utf-8-sig is the single most useful trick here: without the byte-order mark, Excel on Windows opens a UTF-8 file as if it were the legacy code page and every accented character breaks. The cost is three extra bytes that most other readers ignore.

Streaming a file too large to load

When the sheet does not fit in memory, convert without a DataFrame. openpyxl streams rows in, the csv module streams them out, and peak memory is one row:

import csv

from openpyxl import load_workbook

def stream_to_csv(xlsx_path, sheet, csv_path, encoding="utf-8"):
    wb = load_workbook(xlsx_path, read_only=True, data_only=True)
    rows_written = 0
    try:
        ws = wb[sheet]
        with open(csv_path, "w", newline="", encoding=encoding) as handle:
            writer = csv.writer(handle)
            for row in ws.iter_rows(values_only=True):
                if all(v is None for v in row):
                    continue
                writer.writerow(["" if v is None else v for v in row])
                rows_written += 1
    finally:
        wb.close()
    return rows_written

print(stream_to_csv("large.xlsx", "Orders", "large.csv"), "row(s) written")

newline="" on the open call is required, not stylistic: without it the csv module's line endings are translated a second time by the text layer, and every row in the output is followed by a blank one. Dates come through as datetime objects and are written in ISO form by str(), which is the behaviour you want.

Converting only part of a workbook

Most conversions do not need the whole sheet. Narrowing at the read is cheaper than filtering afterwards, and it keeps the output honest about what it contains:

import pandas as pd

df = pd.read_excel(
    "orders.xlsx",
    sheet_name="Orders",
    usecols=["Order_Date", "Region", "Quantity", "Unit_Price"],
    dtype=object,
)
df["Order_Date"] = pd.to_datetime(df["Order_Date"], errors="coerce")
recent = df[df["Order_Date"] >= pd.Timestamp("2026-01-01")].copy()
recent["Order_Date"] = recent["Order_Date"].dt.strftime("%Y-%m-%d")

recent.to_csv("orders_2026.csv", index=False, na_rep="")
print(f"{len(recent):,} of {len(df):,} row(s) exported")

Name the output for what it holds — orders_2026.csv rather than orders.csv — because a filtered extract that looks like a full export is the kind of thing someone reconciles against a total six months later and cannot explain.

Verify the conversion

A conversion nobody checked is a conversion that silently dropped a column. Compare shapes and spot-check the fragile columns:

import pandas as pd

source = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=object)
result = pd.read_csv("orders.csv", dtype=object, keep_default_na=False)

assert list(source.columns) == list(result.columns), "column mismatch"
assert len(source) == len(result), f"row count {len(source)} -> {len(result)}"

for column in ["Code", "SKU"]:
    if column in source.columns:
        before = source[column].astype(str).str.strip().tolist()
        after = result[column].astype(str).str.strip().tolist()
        assert before == after, f"{column} changed during conversion"

print("conversion verified")

keep_default_na=False on the read-back is what makes the comparison honest — otherwise pandas turns empty fields into NaN and the assertion fails for a reason that has nothing to do with the conversion.

CSV, or something better?

CSV is universal and lossy. Where both ends of the pipe are yours, a typed format is a straight upgrade:

The practical rule: CSV crosses organisational boundaries, Parquet stays inside them. A daily job that reads the same export ten times should convert once to Parquet and read that, keeping a CSV only for whoever outside the team asked for one.

Common pitfalls and gotchas

Performance and scale notes

The pandas route costs a full parse plus a frame; the streaming route costs a parse and nothing else, and comfortably handles files that cannot be loaded. Either way, CSV output is roughly a third to a half the size of the equivalent .xlsx and reads an order of magnitude faster afterwards, which is the real reason to convert: a pipeline that reads the same export daily should convert once and read CSV — or better, Parquet, which keeps the types CSV throws away — rather than parsing XML every run.

Convert once, read many times

A pipeline that reads the same workbook several times pays the parsing cost each time. Converting once to CSV or Parquet and pointing every later step at the converted file turns a repeated expense into a single one — and on a large export that change alone often halves the total runtime.

Conclusion

Converting to CSV is a one-liner that quietly damages identifiers, dates and blanks, and a five-line function that does not. Read with dtype=object, cast identifiers to text, format dates as ISO strings, set na_rep="", and choose the encoding for whoever opens the file. For workbooks too large to load, stream rows from openpyxl straight into csv.writer, and verify the result by comparing shapes and the fragile columns.

Frequently asked questions

Why did my product codes lose their leading zeros? pandas inferred the column as numeric on the way in. Read it as a string with dtype=str, or read the whole sheet with dtype=object, and the text is written out intact.

Which encoding should I write?utf-8 for anything machine-read. Use utf-8-sig when the file will be opened in Excel on Windows, because the byte-order mark stops Excel misreading accented characters.

How do I convert a file too large to load? Stream it — iterate rows with openpyxl in read-only mode and write them with the csv module, so neither the sheet nor the output is ever fully in memory.

What happens to formulas? CSV holds values only. Read with data_only=True to write the cached results, and check for None values first or the column will be empty.

Related

Up to the parent guide:

• Working with Large Excel Files in Python — why a format change is often the real fix.

Related guides:

• Speed Up openpyxl with read_only Mode — the streaming reader behind the large-file converter.
• Check Excel Data Types with pandas — deciding each column's treatment before writing.
• Convert an Excel File to PDF with Python — the other direction, for human readers.
• How to Read Excel with pandas, Step by Step — the read side in detail.