CSV, JSON and XLSX: The Conversion Pitfalls That Corrupt Your Data
Leading zeros vanish, dates mutate, encodings break, nested objects flatten lossily. A field guide to the classic data-conversion traps and how to avoid each one.
Three formats, three world-views
CSV, JSON and XLSX look interchangeable — they all hold tables — but they encode fundamentally different assumptions. CSV is untyped text: everything is a string until a consumer guesses otherwise. JSON is typed but schemaless: numbers, booleans, nulls and arbitrary nesting. XLSX is typed *and* formatted: a cell has a value, a type, a number format and locale behavior. Every conversion between them is a translation between world-views, and translations lose things.
The Excel number-coercion trap
Open a CSV in Excel and it will helpfully "fix" your data: 00420 becomes 420, the phone number 8004561000 may render as 8.0046E+9, product code MAR1 becomes March 1st, and a 16-digit card number gets its last digit zeroed because Excel floats only carry 15 significant digits. The file on disk was fine — the act of opening it destroyed the data.
- Import CSVs via a tool that treats columns as text unless told otherwise, never by double-clicking.
- For IDs and codes, force text type at import time; once coerced, the original digits are unrecoverable.
- Round-trip test: convert a sample back and diff it against the original before trusting a pipeline.
Encoding: the accented-character graveyard
CSV has no encoding declaration. A file written as UTF-8 and read as Windows-1252 turns é into é; the reverse turns é into �. Excel historically wrote CSVs in the system legacy code page and only reads UTF-8 reliably when a BOM is present — which is why "add a UTF-8 BOM" remains the standard fix for spreadsheets full of mojibake.
JSON sidesteps this: the spec mandates UTF-8. This alone is a reason to prefer JSON for any data that contains names, addresses, or non-English text.
Flattening nested JSON is lossy by definition
A JSON object with nested arrays cannot become one flat table without a decision: do child rows repeat parent fields, join into delimited strings, or split into a second sheet? Each answer serves different consumers, and none round-trips perfectly. When you must flatten, prefer explicit path-style headers (address.city, items[0].sku) so the structure is at least recoverable.
Dates: the format with a thousand faces
A bare 03/04/2026 is March 4th in New York and April 3rd in London. XLSX stores dates as serial numbers since 1900 (with a deliberate leap-year bug preserved for compatibility), CSV stores whatever string the writer chose, and JSON has no date type at all. The only safe interchange format is ISO 8601 (2026-03-04 or full timestamps with offsets). Normalize to ISO at every boundary and the whole class of bugs disappears.
When you need to move real files between these formats quickly, ConvertAll converts CSV, JSON, XLSX, YAML, TOML and TSV in the browser, and its previews make coercion problems visible before you commit.