Type preservation is the first thing to settle when choosing a spreadsheet format, and it is where CSV fails most visibly. CSV stores strings, so Excel's type guessing on open turns 00123 into 123, may re-render 2024-06-01 in your locale format, and converts a 19-digit integer ID into scientific notation with real precision loss. None of these raise an error at export or import time; they surface months later when someone reads the data.
The second issue is encoding. There is no shared default: Excel on Chinese Windows saves GBK, while macOS, Linux and English Windows save UTF-8, and BOM-less UTF-8 is detected unreliably on double-click. The failure runs in both directions and shares one root cause—nothing declares the encoding. The fix is to export UTF-8 with BOM, which both Excel and modern tooling read correctly, and to probe the first three bytes (EF BB BF) before decoding on the way in. Crucially, do not guess: GBK and UTF-8 look alike on short samples, so state the encoding in the exchange documentation instead of trying to detect it.
Capacity is the third constraint. Excel caps a sheet at 1,048,576 rows by 16,384 columns and 32,767 characters per cell. What happens past that depends entirely on the tool: pandas raises an error, but some export libraries truncate silently, writing only the first 1,048,575 rows with no indication. The file opens, the format is valid, and one row is simply gone.
The switch to Parquet pays off once scale arrives. Columnar storage reads only the columns a query touches—an analytical query usually needs two or three, so the speedup is immediate—compression is roughly an order of magnitude better, and types travel with the file so CSV's type loss cannot occur. A practical threshold is more than a million rows or over 1GB per file; below that, the cognitive cost and toolchain complexity exceed the benefit. Arrow sits in between as an in-memory columnar format for zero-copy conversion.
A useful mindshift: compress the bulk, not the wrapper. In a scanned PDF about 95% of the size is images, so compression works on images. The same logic applies to tables—if a 20MB CSV spends 8MB on verbose date formats, shortening those formats saves more than any wrapper-level optimisation.
Choosing by audience, not by format
The first fork is not which format is better but who reads the data.
Humans — an operator opening it, a salesperson checking a figure, a support agent looking up an order. Use XLSX: typed cells, formulas, multiple sheets, and it opens ready to work. CSV cannot offer any of that.
Programs — a downstream ETL job, a database import, another service consuming it. Prefer Parquet or Arrow at scale, XLSX at moderate volume.
Intermediate only — system A exports, system B imports. CSV remains the most universal exchange format, but you must state encoding and delimiter explicitly rather than relying on defaults.
This fork settles many later details. A CSV destined for a program can be safely flattened because nobody will open it by hand; a CSV destined for a human has to account for Excel's type guessing.
Capacity limits and silent truncation
Excel's limits are hard:
- 1,048,576 rows by 16,384 columns per sheet
- 32,767 characters per cell
What happens past them depends on the tool, and that is where the danger sits. pandas raises an error. Some export libraries truncate silently, writing only the first 1,048,575 rows with no warning at all. The file opens, the format is valid, and one row is simply gone — a fault that may go unnoticed for months.
How to choose past the limit:
- Over a million rows: move to Parquet
- Table too wide: split into multiple sheets, or go long-format with one field per row
- Needs to stay human-readable and small: XLSX with separate sheets
On size, the same principle applies as with scanned PDFs, where roughly 95% of the bytes are images: compress the bulk, not the wrapper. If a 20MB CSV spends 8MB on verbose date representations, shortening those formats saves more than any container-level tweak.
Reading back safely
Detection and decoding belong at the entry point, not scattered across call sites:
raw = open('data.csv', 'rb').read()
if raw.startswith(b''):
text, encoding = raw[3:].decode('utf-8'), 'utf-8-sig'
else:
text, encoding = raw.decode('gbk'), 'gbk' # declared, not guessed
Note the comment on the last line: do not guess. GBK and UTF-8 look alike on short samples, so the error rate is far from negligible. Stating the encoding in the exchange documentation beats any autodetection.
Delimiters vary by locale as well — comma, semicolon, tab — and French Excel defaults to semicolon. RFC 4180 specifies commas, so a semicolon-delimited file is read as a single column by a strict parser.
A checklist worth running before you export: confirm who reads the data, avoid CSV when types must survive, export with UTF-8 BOM, probe the BOM on read, document encoding and delimiter instead of guessing, verify the exported row count matches the source to catch silent truncation, and evaluate Parquet once a file passes a million rows or 1GB.