Choosing Spreadsheet Formats: Type Loss and Encoding Pitfalls in CSV, XLSX and Parquet

The answer first: no format is better, only different readers and limits

Type loss, encoding corruption and capacity truncation share one property: nothing fails at export and nothing fails at import; the problem surfaces months later when someone reads the data. Choosing a format answers two questions: who reads it, and whether it will hit a hard limit.

The first fork: who reads this 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, usable the moment it opens. CSV offers none of that.

Programs — an ETL job, a database import, another service. 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 in the documentation.

This fork settles later details: a CSV destined for a program can be safely flattened because nobody opens it by hand, while one destined for a human must account for Excel's type guessing.

Type loss: CSV stores strings and nothing else

The name says it — comma-separated values. There is no type system, no multi-table concept, no metadata; every field is a run of characters.

Source value What the CSV holds After Excel opens it
Order number 00123 5 characters 123, leading zeros gone
Date 2024-06-01 10 characters a date, re-rendered in your locale
Boolean true 4 characters a boolean
64-bit ID 19 digits scientific notation, precision lost

The last row is not a display issue: the data itself changed. Nineteen digits are cut to fifteen significant digits and cannot be recovered, and a round trip (export then import) compounds every loss.

That is exactly where Excel to text and Excel to JSON differ: JSON carries types, plain text does not. If types must survive, CSV is out. Three routes remain:

  1. Switch to XLSX — easiest, at the cost of plain-text grep-ability;
  2. Encode type into the data: dates as ISO 8601 with a timezone, IDs as strings declared in the header;
  3. Use JSON Lines — one JSON object per line, types intact and still streamable.

Encoding and BOM: mojibake means nobody declared anything

The same Excel saves different encodings on different systems: GBK on Chinese Windows, UTF-8 on English Windows, macOS and Linux. Add the unreliable detection of BOM-less UTF-8 when Excel opens it by double-click, and both directions fail:

  • Windows exports GBK without a BOM → Linux reads it as UTF-8 → mojibake;
  • A file exported as UTF-8 without a BOM → opened in Excel → mojibake again.

The common factor is the missing declaration. The robust answer is UTF-8 with BOM on export (leading bytes EF BB BF), and probe before decoding on the way in:

raw = open('data.csv', 'rb').read()
if raw.startswith(b'\xef\xbb\xbf'):
    text, encoding = raw[3:].decode('utf-8'), 'utf-8-sig'
else:
    text, encoding = raw.decode('gbk'), 'gbk'   # declared, not guessed

That comment is the point: do not guess. GBK and UTF-8 look alike on short samples, so the error rate is far from negligible. Delimiters vary by locale too — comma, semicolon, tab, with French Excel defaulting to semicolon — and RFC 4180 only recognises commas, so a semicolon file is read as a single column.

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, which is where the danger sits: pandas raises, while some export libraries truncate silently, writing only the first 1,048,575 rows. The file opens, the format is valid, one row is simply gone — a fault that can stay unnoticed for months.

Past the limit: move to a columnar format beyond a million rows (next section); if the sheet is merely too wide, split it or go long-format; if humans must read a modest volume, use XLSX with several sheets. When size comes from verbose date representations, shorten those before changing formats.

Verify by comparing the source and output row counts immediately after export, rather than trusting that the file opens.

When Parquet earns its place

Parquet pays off only once scale arrives:

  • Columnar storage: analytical queries typically touch two or three columns, and Parquet reads only those;
  • Compression: roughly a tenth of the CSV size for comparable data;
  • Types travel with the file, so CSV's type loss cannot happen.

The practical threshold is over a million rows or over 1GB per file. Below that, humans cannot cat the contents and the toolchain is more complex, so the cognitive cost outweighs the benefit. Arrow sits in between as an in-memory columnar format for zero-copy conversion.

Checklist and verification

  • [ ] Identify whether the reader is human or a program, then choose
  • [ ] Avoid CSV when types must survive, or encode the type into the data
  • [ ] Export UTF-8 with BOM and probe the BOM on read
  • [ ] Document encoding and delimiter instead of guessing
  • [ ] Compare row counts after export to catch silent truncation
  • [ ] Evaluate Parquet beyond a million rows

Run the same table with leading zeros and dates through Excel to text and Excel to JSON to see the difference: the text output holds 00123 as characters that any spreadsheet will still coerce to a number, while the JSON output keeps it a string and the zeros survive the round trip. Format attributes are tabulated in the spreadsheet formats reference.

Frequently Asked Questions

Why does 00123 become 123 after opening a CSV?

Because CSV stores strings, not types. The file literally contains the five characters 00123; Excel guessed wrong on open — seeing all digits it parsed a number and dropped the leading zeros. A family of related problems follows: the date 2024-06-01 may be parsed as a date and redisplayed in your locale format, TRUE becomes boolean, and integer IDs beyond 15 digits turn into scientific notation with real precision loss. There is exactly one cure: if types must survive, do not use CSV. Either switch to XLSX, whose cells are typed, or encode type into the data — dates as 2024-06-01T00:00:00Z, IDs as strings declared in the header.

Whose fault is the Chinese mojibake?

Both sides share responsibility, but the root cause is a missing encoding declaration. The chain: Excel on Chinese Windows saves CSV as GBK by default while Mac and Linux default to UTF-8; worse, Excel detects BOM-less UTF-8 unreliably on double-click. The typical failure is: system A on Windows exports GBK without BOM while system B on Linux reads it as UTF-8, producing mojibake — and the reverse also garbles when a BOM-less UTF-8 file is opened in Excel. The robust answer is to export UTF-8 with BOM, which both Excel and modern tooling read correctly, and to probe the first three bytes before choosing a decoder. If GBK legacy systems must be supported, state the encoding in the exchange documentation rather than guessing.

When is Parquet worth adopting?

Not yet, probably. If your data stays under a few tens of thousands of rows and people need to open and read it directly, CSV or XLSX is plenty, and Parquet's binary format actually makes content impossible to inspect. Parquet pays off at scale: columnar storage reads only the columns you use (analytical queries typically touch two or three, so the win is immediate), compression is an order of magnitude better, and type information travels with the file, so the type loss seen with CSV cannot happen. 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, the in-memory format, sits in between and suits zero-copy conversion workloads.

← Back to Blog