Excel — The Type-Confusion Tax · Data Engineering Quest

Excel is the most-shipped data format in the world after CSV, and the cost of touching it always shows up in three places: type confusion, hidden state, and round-trip drift.

Type confusion

Excel auto-detects types when you open or paste a file. Once detected, the type is stored in the cell — meaning that opening a CSV in Excel and saving it again can permanently change values. The classic offenders:

Leading zeros gone (00123 ZIP code becomes 123).
Long IDs in scientific notation (1234567890123456 becomes 1.23457E+15, losing the last digits).
Gene names parsed as dates (MARCH1 becomes 1-Mar).
Strings that look like fractions parsed as fractions (1/2 becomes 0.5).

Hidden state

An Excel file isn't just cell values. It's also formulas, conditional formatting, named ranges, hidden columns, autofilters, comments, change tracking, embedded objects, and macros. Reading an XLSX with openpyxl or pandas ignores most of that — which is fine, but means the file you read is not the file the human sees.

Round-trip drift

Even if you read and write the same file with no edits, formula evaluation order, locale-specific separators, and floating-point representation can cause tiny diffs that ruin git commits and trip up downstream consumers.

The rule: read Excel only at the boundary of a pipeline (when a human supplies one), and convert to Parquet immediately. Never make Excel a stage in your pipeline; only an input or an output.

Code

Read Excel safely, then convert to Parquet and forget the XLSX exists·python

import pandas as pd

df = pd.read_excel(
    'sales_q1.xlsx',
    sheet_name='Q1',
    dtype=str,
    keep_default_na=False,
    engine='openpyxl',          # explicit; the default depends on the file extension
)

# Cast deliberately
df['amount_usd'] = pd.to_numeric(df['amount_usd'].str.replace(',', ''), errors='raise')
df['order_date'] = pd.to_datetime(df['order_date'], errors='raise')

# Convert to Parquet — and use the Parquet from now on
df.to_parquet('sales_q1.parquet', index=False, compression='zstd')

Excel — The Type-Confusion Tax

Type confusion

Hidden state

Round-trip drift

Code

External links

Exercise

Progress

Comments 0