Converting Excel to Parquet for Faster Analytics Loads

A workbook that takes ninety seconds to open and eleven seconds to read in Python is a bad starting point for an analysis you will run forty times. Parquet fixes that: it stores each column with its type, compresses it, and lets a reader skip the columns it does not need. The same table that is 38 MB as an xlsx is often under 10 MB as Parquet, and it loads in a fraction of a second. This article is the Excel to Parquet recipe, and it sits beside the broader guide to converting Excel to CSV, JSON, XML and YAML, which covers the checks every conversion shares.

The catch is that Parquet is strict. A CSV accepts anything because it stores nothing but text. A Parquet column has one type, and a workbook column that holds the number 12, the text "n/a" and a date is not a column that has one. The conversion is where those problems surface, so the steps below spend most of their time on types.

Read the sheet with the types you want

Start by deciding what each column is. Pandas guesses from the cell contents, and its guess is the first place a conversion goes wrong.

import pandas as pd

df = pd.read_excel(
    "orders.xlsx",
    sheet_name="Orders",
    dtype={"order_no": "string", "postal_code": "string", "customer_id": "string"},
    parse_dates=["ordered_on"],
)
print(df.dtypes)

For a sheet with columns order_no, postal_code, customer_id, ordered_on, qty and unit_price, the output looks like this:

order_no                string[python]
postal_code             string[python]
customer_id             string[python]
ordered_on         datetime64[ns]
qty                         int64
unit_price                float64
dtype: object

The identifier columns are strings, so a postal code of 01234 keeps its leading zero. If the cell already holds the number 1234 the zero is gone before pandas sees it, and you need to fix it in the workbook or pad it from a known width (keeping leading zeros in Excel).

Quantities with blanks arrive as floats, because a plain int64 cannot hold a missing value. Use the nullable integer type when a blank is legitimate:

df["qty"] = df["qty"].astype("Int64")

Clean the headers once

Parquet column names can contain spaces, but the tools that read the file later will not thank you. Convert the headers to lowercase names with underscores and fail if two collapse into one.

import re

def key(h):
    s = re.sub(r"[^0-9a-zA-Z]+", "_", str(h).strip()).strip("_").lower()
    return s or "col"

df.columns = [key(c) for c in df.columns]
if df.columns.duplicated().any():
    raise ValueError("duplicate column names after cleaning")

Do this before the type step above if your sheet's headers are messy, since dtype is keyed on the names you pass. In practice you clean headers first on a quick read, then reread with the types.

Write the file with pandas

df.to_parquet("orders.parquet", engine="pyarrow", compression="zstd", index=False)

index=False matters, because otherwise pandas writes its row index as an extra column that nobody asked for. Zstandard compression is a good default: smaller than Snappy, fast to read. Snappy is the default in pyarrow and is fine when write speed matters more than size.

To confirm what was written, read the schema back without loading the data:

import pyarrow.parquet as pq

print(pq.read_schema("orders.parquet"))
print(pq.ParquetFile("orders.parquet").metadata.num_rows)

The schema shows string, timestamp[ns], int64 and double columns, and the row count is the number you compare with the sheet.

Convert with DuckDB instead

For a workbook too large to read comfortably into pandas, or when you prefer SQL, DuckDB reads the sheet and writes Parquet in one statement.

INSTALL excel;
LOAD excel;

COPY (
  SELECT
    CAST(order_no AS VARCHAR)    AS order_no,
    CAST(postal_code AS VARCHAR) AS postal_code,
    CAST(ordered_on AS DATE)     AS ordered_on,
    CAST(qty AS INTEGER)         AS qty,
    CAST(unit_price AS DECIMAL(12,2)) AS unit_price
  FROM read_xlsx('orders.xlsx', sheet = 'Orders')
) TO 'orders.parquet' (FORMAT parquet, COMPRESSION zstd);

The explicit casts are the point. A DECIMAL(12,2) price is exact, where the double that pandas produces is not, and money columns are the ones to store exactly. The excel extension and the read_xlsx function exist in recent DuckDB releases; on older versions use the spatial extension's st_read or convert the sheet to CSV first (loading Excel files with DuckDB).

One file per sheet

A workbook is a bundle of tables. Write one Parquet file per sheet, named after the sheet, and keep a small manifest of what you wrote.

import hashlib, json
from pathlib import Path

book = pd.ExcelFile("orders.xlsx")
manifest = []
for sheet in book.sheet_names:
    part = book.parse(sheet)
    part.columns = [key(c) for c in part.columns]
    out = Path(f"{key(sheet)}.parquet")
    part.to_parquet(out, index=False, compression="zstd")
    manifest.append({"sheet": sheet, "file": out.name, "rows": len(part),
                     "sha256": hashlib.sha256(out.read_bytes()).hexdigest()})
Path("manifest.json").write_text(json.dumps(manifest, indent=2))

This version lets pandas guess types, which is fine for a first look and not for a file other people load. Pass dtype per sheet when you ship it. A sheet named Scratch that someone left in the workbook will be converted too, so list the sheets you expect and fail on any you did not.

Handle the columns that will not convert

Three failures account for most broken writes.

A mixed column. A column of quantities with the text "TBC" in one cell is an object column, and the Parquet writer raises ArrowInvalid with the offending value. That error is helpful: it points at a real data problem. Decide whether "TBC" should be null, then convert with pd.to_numeric(col, errors="coerce") and count how many values turned into nulls, so that you can report them rather than hide them.

Dates stored as text. Parse them with an explicit format, pd.to_datetime(col, format="%d/%m/%Y"), and never let pandas infer day and month order from a file whose locale you do not know (importing Excel dates correctly).

Time zones. Excel dates and times have no zone. If the values are local times, say so in a column name or in the file's metadata, or convert them to UTC explicitly. Writing naive timestamps works, and the next person assumes UTC.

Check the result

Three checks catch almost everything:

1. The row count in the Parquet file equals the sheet's data rows.

2. A control total matches: the sum of a numeric column in the output equals the sum in Excel for the same rows.

3. The schema is what you intended, with no object columns turned into strings by accident.

check = pd.read_parquet("orders.parquet")
assert len(check) == len(df)
assert abs(check["unit_price"].sum() - df["unit_price"].sum()) < 0.005

A matching total on a double column is only as exact as floating point allows, which is why money belongs in a decimal type or in integer cents (validating row counts after an Excel import).

Loading the file afterward

The point of the exercise is the load. DuckDB queries the file in place with no import step:

SELECT postal_code, SUM(qty * unit_price) AS revenue
FROM 'orders.parquet'
GROUP BY postal_code
ORDER BY revenue DESC;

Pandas reads it back with pd.read_parquet, and takes only the columns you name with the columns argument, which is where the speed comes from. PostgreSQL does not read Parquet natively, so load it through DuckDB or pandas, or export from DuckDB to CSV for COPY. The format is described in the Apache Parquet documentation, and the pandas options are in the to_parquet reference.

When Parquet is the wrong target

Parquet is a poor fit when a person must open the result. Nobody double clicks a Parquet file in a finance team, so a report for a human reader should stay xlsx or CSV. It is also a poor fit for a small file read once: for 2,000 rows the conversion step costs more than it saves. And if the receiving system cannot read Parquet, which is true of many legacy loaders, you are converting twice. Pick the format that the reader supports, and use converting a huge Excel file to CSV or Parquet when the workbook is large enough that memory is the first problem.