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.
CSV is not one format
RFC 4180 describes CSV, but almost nobody follows it exactly. Delimiters vary by locale: much of continental Europe uses semicolons because the comma is the decimal separator, so a "CSV" from a German Excel is 1,5;2,5 — which an American parser reads as four columns. Quoting rules vary (some tools quote everything, some nothing, some only fields containing the delimiter), line endings vary (CRLF vs LF), and a quoted field may legally contain newlines, breaking every "split on newline" script ever written.
- Sniff the delimiter from the first few lines rather than assuming a comma; good tools offer a preview precisely so you can catch this.
- Treat a field as text until proven otherwise — the parser should never decide that
1e5is a number orTRUEis a boolean unless you asked. - Preserve the header row as data, not as column *types*; many exports put units or notes in a second header row.
A round-trip checklist before you trust a pipeline
Every conversion between these formats should be tested in both directions with a deliberately nasty sample file before it touches real data. Build one fixture that contains: a leading-zero ID, a 16-digit number, a negative decimal with a thousands separator, a date in an ambiguous format, an accented name, a field with an embedded comma, quote and newline, an empty field, and a field containing the literal word null. Convert it, convert it back, and diff against the original. If anything changed, you have found the bug now rather than after a quarter of corrupted reports.
This takes ten minutes and has saved teams from months of silent data rot. The trap is that conversion *looks* lossless when the sample is clean — the damage only shows on the edge cases real data is full of.
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.