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:
- Switch to XLSX — easiest, at the cost of plain-text grep-ability;
- Encode type into the data: dates as ISO 8601 with a timezone, IDs as strings declared in the header;
- 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.