Common Data Structure Pitfalls When Converting CSV to JSON
By Marcus Vane · September 6, 2026 · Data
CSV is flat, untyped and barely standardised. JSON is nested and typed. Every conversion has to invent answers to questions the CSV never asked.
Converting a spreadsheet export into JSON looks like a one-line job until the first file arrives with a comma inside an address, a leading zero in a postcode, or a date that Excel helpfully reinterpreted. CSV has no schema and no types, so every conversion is a series of guesses.
The delimiter is not always a comma
Exports from systems configured for European locales frequently use semicolons, because the comma is the decimal separator there. Tab separated files are common from databases. A parser that assumes a comma will read a semicolon-delimited file as a single enormous column and produce technically valid, entirely useless JSON.
Quoting and escaping
A field containing the delimiter must be quoted, and a quotation mark inside a quoted field is escaped by doubling it. Naive splitting on commas breaks immediately on "Smith, John" and mangles every subsequent column on that row. Fields can also contain literal newlines inside quotes, which means a CSV row is not the same as a text line and line-by-line processing is unsafe.
Type inference is where data dies
- Leading zeros vanish when a postcode or product code is inferred as a number.
- Long identifiers lose precision once they exceed the range of exact integer representation.
- Values like "TRUE", "yes" and "1" may or may not become booleans depending on the tool.
- Dates are the worst case, because ambiguous formats can be interpreted with the day and month swapped, producing plausible but wrong values for the first twelve days of every month.
- Currency values with symbols or thousands separators become strings, breaking arithmetic downstream.
The safest default is to keep everything as strings and cast deliberately according to a known schema, rather than letting a converter guess column by column based on the first few rows.
Empty is not the same as missing
A blank CSV cell could mean an empty string, an unknown value or a deliberate null, and the file cannot tell you which. Decide the convention explicitly, because a downstream system that distinguishes null from empty string will behave differently for each.
Converting carefully
- Inspect the raw file in a plain text editor before parsing, to confirm delimiter, quoting and encoding.
- Check the header row for duplicates and for names that will collide once normalised.
- Convert with all values as strings first, then apply an explicit schema.
- Compare row counts before and after, which catches embedded newline problems immediately.
- Spot check the widest and strangest rows rather than the first ten.
- Define how nested structure is represented, whether by dotted column names or a grouping key.
Wrapping up
Treat CSV as untyped text and impose your own schema. Every problem in this article comes from letting a tool guess what the data meant.
Convert between CSV and JSON with control over structure.
SwiftMultiTool — free online tools for everyone. Home · Blog · About · FAQ · Contact