Guide

Getting Started With Python Excel AutomationDeep dive

Excel vs CSV vs Parquet for Python Data Pipelines

Excel is a delivery format, not a storage format. Compare types, size and read speed, and use Parquet in the middle of the pipeline with .xlsx only at the end.

Excel is a superb delivery format and a poor interchange format. It is the only one of the three that a person can open, sort and annotate, and it is the slowest to parse, the largest on disk and the easiest to corrupt with a stray manual edit. The productive question is not which format is best but where each belongs in a pipeline. This guide, part of Choosing a Python Excel Library, draws that line.

Prerequisites

Bash

pip install pandas pyarrow openpyxl

pyarrow is what gives pandas its Parquet support; without it to_parquet raises an import error naming the missing engine.

What each format actually stores

The differences that matter are about types and structure, not size.

An .xlsx file is a zip of XML that stores a value, a type flag and a style index per cell, plus sheets, formulas, charts, merged ranges and everything else Excel understands. A CSV stores text and commas — no types, no sheets, no formatting, and an encoding you have to guess if nobody wrote it down. A Parquet file stores columns, each with a declared type, compressed and accompanied by statistics that let a reader skip whole blocks it does not need.

Python

import pandas as pd

frame = pd.DataFrame({
    "Account": ["00123", "00456"],      # leading zeros matter
    "Ordered": pd.to_datetime(["2026-01-04", "2026-02-11"]),
    "Revenue": [12400.5, 9800.25],
})

frame.to_excel("data.xlsx", index=False)
frame.to_csv("data.csv", index=False)
frame.to_parquet("data.parquet", index=False)

print(pd.read_csv("data.csv").dtypes)          # Account -> int64, zeros gone
print(pd.read_parquet("data.parquet").dtypes)  # types preserved exactly

That round trip is the whole argument in five lines. CSV silently destroys the account code and turns the date into a string; Parquet returns exactly what it was given; Excel keeps the value but depends on how the cell was formatted when it was written.

Speed and size on the same data

The ratios move with the data, but the ordering is consistent: Parquet is the fastest to read and the smallest on disk, CSV is fast to write and bulky, and .xlsx is the slowest and largest of the three because every cell carries structure a plain table does not need.

Python

import os
import time
import pandas as pd

frame = pd.read_excel("bench.xlsx")
for label, writer, reader, path in [
    ("xlsx", frame.to_excel, pd.read_excel, "out.xlsx"),
    ("csv", frame.to_csv, pd.read_csv, "out.csv"),
    ("parquet", frame.to_parquet, pd.read_parquet, "out.parquet"),
]:
    writer(path, index=False)
    start = time.perf_counter()
    reader(path)
    size = os.path.getsize(path) / 1_048_576
    print(f"{label:>8}: read {time.perf_counter() - start:5.2f}s   {size:6.1f} MB")

Where each one belongs

The pattern that survives contact with production is a three-stage split. Source data arrives in whatever the upstream system produces — often .xlsx, because a person exported it. The pipeline converts it once to Parquet and does all subsequent work against that. The final human-facing artefact is generated as .xlsx at the end, formatted for reading rather than for parsing.

Python

import pandas as pd
from pathlib import Path

def cached_frame(source: Path) -> pd.DataFrame:
    cache = source.with_suffix(".parquet")
    if not cache.exists() or cache.stat().st_mtime < source.stat().st_mtime:
        pd.read_excel(source, engine="calamine").to_parquet(cache, index=False)
    return pd.read_parquet(cache)

frame = cached_frame(Path("monthly-export.xlsx"))
print(frame.shape)

The modification-time check is what makes this safe to leave in a scheduled job: a refreshed source invalidates the cache automatically, and an unchanged one is never parsed twice. Convert Excel Files to Parquet with Python covers the conversion in more depth, including partitioned output.

When CSV is still the right answer

CSV keeps winning for reasons that have nothing to do with its technical merits. Every system reads it, including ones written before Parquet existed; it streams line by line, so a 40 GB file can be processed without a columnar reader; and it is diffable, which makes it the only one of the three that behaves sensibly in version control.

If you do use it as an interchange format, remove the guesswork on the way back in:

Python

import pandas as pd

frame = pd.read_csv(
    "export.csv",
    dtype={"Account": "string", "Postcode": "string"},   # keep leading zeros
    parse_dates=["Ordered"],
    encoding="utf-8-sig",                                # strip the BOM Excel writes
)

utf-8-sig deserves the callout: Excel writes a byte-order mark when it saves a CSV as UTF-8, and reading it as plain utf-8 leaves an invisible prefix on the first column name, which then fails every subsequent lookup by name. Convert Excel to CSV with Python covers the rest of the encoding and quoting decisions.

Handing a schema across the boundary

The reason a Parquet handoff is safe and a CSV handoff is not comes down to one thing: Parquet carries its schema and CSV does not. That difference shows up on the second run rather than the first, when an upstream column changes type and every downstream step quietly reinterprets it.

Python

import pandas as pd
import pyarrow.parquet as pq

pq.write_table(
    __import__("pyarrow").Table.from_pandas(frame, preserve_index=False),
    "orders.parquet",
    compression="zstd",
)

schema = pq.read_schema("orders.parquet")
print(schema)                                  # types, exactly as written

If the pipeline has to stay on CSV, write the schema next to it and assert against it on the way back in. Ten lines of contract prevents the class of bug where a report is wrong but nothing errored:

Python

import json
import pandas as pd

EXPECTED = {"Account": "string", "Ordered": "datetime64[ns]", "Revenue": "float64"}
json.dump(EXPECTED, open("orders.schema.json", "w"))

frame = pd.read_csv("orders.csv", dtype={"Account": "string"}, parse_dates=["Ordered"])
actual = {name: str(dtype) for name, dtype in frame.dtypes.items()}
missing = [c for c in EXPECTED if c not in actual]
wrong = {c: (EXPECTED[c], actual[c]) for c in EXPECTED if c in actual and actual[c] != EXPECTED[c]}
assert not missing and not wrong, f"schema drift — missing {missing}, wrong {wrong}"

The same reasoning drives the column checks in Validate Excel Columns Before Import with Pandas: the cheapest place to catch a type change is the moment the data crosses into your code.

What a person actually needs from the file

It is worth being honest about why .xlsx keeps winning despite everything above. A recipient can open it without installing anything, sort a column, filter it, add a note in the margin and send it back. No other format on this list does that, and a pipeline that delivers Parquet to a finance team has optimised the wrong end.

That is why the recommendation is a split rather than a replacement. The moment the data stops moving between programs and starts being read by a person, the format should change — and it should change into a workbook that is formatted for reading, with frozen headers, number formats and sensible column widths, as Write a Formatted Excel Report with XlsxWriter shows.

Common pitfalls

Symptom	Cause	Fix
Leading zeros gone after a CSV round trip	CSV has no types; pandas inferred `int64`	Pass `dtype={"col": "string"}` on read
`ImportError: Unable to find a usable engine` on `to_parquet`	No Parquet library installed	`pip install pyarrow`
First column name has a strange prefix	Excel wrote a UTF-8 BOM	Read with `encoding="utf-8-sig"`
Parquet file cannot be opened by a colleague	It is not meant to be opened by hand	Deliver `.xlsx`; keep Parquet inside the pipeline
Dates arrive as text from CSV	No type information in the file	`parse_dates=[...]`, or store Parquet instead
`.xlsx` write fails past a million rows	The format's own row limit is 1,048,576	Split across sheets or files, or deliver CSV/Parquet

Performance and scale

The row limit is the constraint people meet first and plan for last. A worksheet holds 1,048,576 rows and 16,384 columns; exceed either and the write fails or silently truncates depending on the library. CSV and Parquet have no such limit, which by itself decides the format for any dataset approaching that scale — see Working with Large Excel Files in Python for the strategies when the deliverable still has to be a spreadsheet.

Parquet also brings predicate and column pushdown, which no spreadsheet format can offer. A query that needs three columns of a fifty-column dataset reads three columns from disk:

Python

import pandas as pd

north = pd.read_parquet(
    "orders.parquet",
    columns=["Region", "Revenue", "Ordered"],
    filters=[("Region", "==", "North")],
)
print(north.shape)

That is not a faster parser doing the same work — it is less work. It is the reason a converted pipeline keeps getting faster as the dataset grows rather than slower.

Conclusion

Treat Excel as the format you deliver in, not the one you compute in. Convert an incoming workbook to Parquet once and every later step becomes faster, smaller and type-safe; keep CSV for the interfaces that demand universality, and read it defensively because it carries no types of its own. The .xlsx file then reappears exactly where it earns its cost: at the end, formatted for a person who is going to open it.

Frequently asked questions

Is CSV really lossy? Yes, in the ways that bite. It has no types, so a leading-zero account code comes back as an integer and a date comes back as whatever the reader guesses. It has no sheets, no formats and no formulas. What it does have is universal support, which is why it survives.

Does Parquet need a special reader? It needs pyarrow or fastparquet installed, both a single pip install. Nobody opens a Parquet file by double-clicking it, which is exactly the point: it is a machine-to-machine format, not a deliverable.

How much smaller is Parquet than Excel? Typically five to twenty times, because it stores columns rather than rows and compresses each one with a codec suited to its type. A repetitive string column — region names, SKUs — compresses especially well through dictionary encoding.

Can I keep using Excel as the delivery format but not the storage format? That is the recommended pattern. Store and move data as Parquet between automated steps, and generate the .xlsx only at the moment a person is going to open it. The spreadsheet becomes an output rather than a database.

What about Feather, ORC or Avro? Feather is Arrow's on-disk format and is faster to write than Parquet but compresses less, which makes it a good scratch format between steps of one job. ORC and Avro belong to the Hadoop lineage and rarely appear in a Python reporting pipeline unless the warehouse already uses them.

Up one level: Choosing a Python Excel Library — which library to use once the format is settled.
Convert Excel Files to Parquet with Python — the conversion step, including partitioning and schema control.
Convert Excel to CSV with Python — encodings, quoting and multi-sheet exports.
Working with Large Excel Files in Python — what to do when the deliverable must stay a spreadsheet.
Benchmark Python Excel Read and Write Speed — measure the difference on your own data.