CSV vs XLSX

CSV: dead simple, surprisingly tricky

CSV (Comma-Separated Values) seems trivial — values separated by commas, rows separated by newlines. The actual specification (RFC 4180, 2005) is more nuanced:

name,age,city
"Smith, John",30,"New York, NY"
Jane,25,Chicago

Key rules:

1. Fields containing commas, quotes, or newlines must be quoted with double-quotes (")
2. Quotes inside fields are escaped by doubling ("")
3. Line endings can be CR, LF, or CRLF (RFC says CRLF; in practice all three appear)
4. Header row is optional but commonly present
5. All fields are technically strings — interpretation of numbers/dates is up to the consumer

The "surprisingly tricky" part comes from real-world CSV that doesn't follow RFC 4180:

• Encoding: UTF-8, Windows-1252, ISO-8859-1, UTF-16 with BOM — all common, all incompatible. Mojibake (café → café) plagues every data engineer.
• Delimiters: Some "CSV" files use semicolons (European Excel default), tabs (TSV), or pipes (|). Sniff before parsing.
• Numbers as text: Phone numbers like 0123456789 get re-formatted to 123456789 if a tool guesses they're integers.
• Dates: 01/02/2026 is January 2 in US, February 1 in EU. CSV doesn't tell you which.
• Trailing commas / inconsistent column counts: parsers vary in tolerance.

Practical advice: when generating CSV, follow RFC 4180 strictly (quote everything that might contain a delimiter, use UTF-8 with BOM if Excel will open it, use ISO 8601 dates YYYY-MM-DD). When consuming CSV, sniff the delimiter and use a robust parser (Python's csv module, PHP's fgetcsv).

XLSX: a full spreadsheet in a ZIP

XLSX is Office Open XML (OOXML), ISO/IEC 29500. The file is a ZIP archive containing XML files. Rename .xlsx → .zip, unzip, and you'll find:

• xl/workbook.xml — sheet list and global settings
• xl/sharedStrings.xml — deduplicated text values (huge optimization)
• xl/worksheets/sheet1.xml — one XML per sheet with cell coordinates and values
• xl/styles.xml — fonts, colors, formats
• xl/calcChain.xml — formula dependency graph
• xl/charts/chart1.xml — embedded charts

The "shared strings" optimization is interesting: text values are stored once and referenced by index. A sheet with 100,000 "USA" entries stores "USA" once, plus 100,000 small index references. This is why XLSX is often dramatically smaller than equivalent CSV — text repetition is essentially free.

File size: counterintuitive results

For 10,000 rows × 30 columns of mixed text+numbers:

XLSX is often smaller than uncompressed CSV thanks to ZIP compression and string deduplication. CSV only beats XLSX when gzipped, which adds processing cost.

When CSV trips you over

Real-world CSV "horror stories" that catch new data engineers:

1. Excel mangling: open a CSV with phone numbers, save → leading zeros disappear (because Excel thinks they're numbers).
2. Encoding wars: a file produced on Mac (UTF-8) opened on Windows Excel (Windows-1252) shows garbled accented characters.
3. Semicolon CSVs from Europe: a German colleague sends "CSV" but pandas expects commas → all columns merged into one.
4. Trailing whitespace: a column header "name " (note trailing space) doesn't match "name" in code.
5. Quoted vs unquoted numbers: "123" (quoted, string) vs 123 (unquoted, number) — pandas may auto-coerce inconsistently.
6. Embedded newlines: a quoted field containing \n breaks naive line-by-line parsing.

These are why production data pipelines often convert CSV inputs to a strict format (Parquet, JSON, database) early and process internally without re-touching CSV.

When XLSX trips you over

Real-world XLSX "horror stories":

1. Date serial numbers: Excel stores dates as days-since-epoch (Jan 1, 1900 with a known bug). Reading XLSX in Python without parsing → you get integers like 45000 instead of dates.
2. Numeric precision: Excel stores all numbers as doubles. Long integers (IDs, account numbers > 15 digits) lose precision silently.
3. Formulas that reference removed cells: copy a sheet to a new file → #REF! errors everywhere.
4. Locale-dependent functions: =SUMA() (Spanish locale) → #NAME? for English-locale Excel users.
5. Macros (XLSM): opening untrusted XLSM with macros enabled = malware risk.
6. Hidden sheets / cells: data stored in hidden sheets isn't visible in normal usage but exists in the file. Surprises auditors.

When to use neither: Parquet and Apache Arrow

For data engineering and analytics, Parquet has largely replaced CSV in modern stacks:

• Columnar storage: query "select age from 1M rows" reads only the age column, not all data
• Compression: typically 5-10× smaller than CSV
• Schema enforced: types preserved, no encoding ambiguity
• Metadata-rich: column statistics, partition info embedded
• Tool support: pandas, PySpark, DuckDB, Snowflake, BigQuery all read Parquet natively

If you control both producer and consumer, prefer Parquet over CSV for any non-trivial dataset. Use CSV when you need universal portability or the consumer is a human in Excel.

Converting between CSV and XLSX

Convert CSV to XLSX handles the typical conversion: detect encoding, sniff delimiter, parse with proper quoting, emit XLSX with appropriate type inference (numbers stay numbers, dates parsed if recognizable). Add formatting and formulas in Excel after the fact.

Convert XLSX to CSV extracts the active sheet (or one specified) as CSV. Multi-sheet workbooks need separate exports per sheet (CSV doesn't support sheets). Formulas are converted to their last-calculated values; formatting is dropped.