CSV and JSON conversion pitfalls: delimiters, encodings, nesting and Excel

A CRM export has to reach a JSON API, or an API dump has to land in a spreadsheet, and something is wrong at the far end: the postcodes have lost their zeros, a customer called Irène is now Irène, one row has turned into two, and a nested address has become a column called address.geo.lat. None of this is random. Every CSV↔JSON failure comes from one of five places — the delimiter, the quoting, the encoding, the type guessing, or the mismatch between a tree and a table — and each has a check that takes under a minute. This guide goes through all five, states exactly what the CSV to JSON, JSON to CSV and JSON to NDJSON converters on this site do at each point (taken from the parsers they actually run, not from their marketing copy), and ends with a checklist to run before you trust a converted file.

CSV has a standard that almost nobody follows

RFC 4180, published in 2005, is the closest thing CSV has to a specification, and it is short: records end with CRLF; fields are separated by commas; a field that contains a comma, a double quote or a line break must be wrapped in double quotes; a double quote inside a quoted field is written twice (""); every record has the same number of fields; spaces are part of the field, not padding; and a header row is optional, signalled only by the MIME parameter text/csv; header=present that no file ever carries. So a single record can legitimately span several lines:

id,name,note
1,"Doe, John","said ""hello""
then left"

That is one header and one record over three physical lines, which is why wc -l never tells you how many rows a CSV has. The real world then departs from the RFC in every direction. Unix tools write LF, not CRLF. Excel on a machine whose locale uses a comma as the decimal separator — German, French, Spanish, Dutch, Brazilian Windows — writes semicolons between fields, because it uses the Windows List separator (Region → Additional settings) and a comma there would be ambiguous. Database exports are often tab-separated. Some tools quote every field, others none. Nothing inside the file declares which dialect it is, so a parser has to guess. The CSV to JSON converter guesses by parsing the first ten rows with comma, tab, pipe and semicolon in turn and keeping the delimiter that gives the most consistent field count; semicolon and tab files therefore work without editing, and a file with a single column fails with //Invalid CSV: Unable to auto-detect delimiting character; defaulted to ',' because no delimiter produces two fields.

What Excel does to a CSV before you ever see it

Excel does not open a CSV; it imports one with defaults you did not choose, and on Save it writes back what it decided. The damage is predictable:

• Leading zeros vanish. 02134 becomes 2134: Boston ZIP codes, phone numbers, product codes and account numbers that start with 0. Wrapping the value in quotes in the file does not protect it — Excel applies the same guess to quoted fields.
• Long numbers are rounded to 15 significant digits. A 16-digit card number 4111111111111111 is stored as 4111111111111110 and displayed as 4.11111E+15. Order IDs, IMEIs and 64-bit database keys suffer the same fate, and saving writes the rounded value back into the file.
• Anything date-shaped becomes a date. 1/2 becomes 1 February or 2 January depending on locale, 3-4 becomes a date, and the gene symbols SEPT2 and MARCH1 were converted to 2-Sep and 1-Mar so often that the HGNC renamed them SEPTIN2 and MARCHF1 in 2020.
• Cells starting with =, +, - or @ are evaluated as formulas. This is the CSV-injection class of bug; a JSON string "=1+1" written to CSV arrives in Excel as the number 2.
• Encoding depends on which Save As option was used. CSV (Comma delimited) writes the Windows ANSI code page — Windows-1252 on Western systems — with no byte-order mark; CSV UTF-8 (Comma delimited) writes UTF-8 with the BOM bytes EF BB BF. Double-clicking a UTF-8 file that has no BOM makes Excel assume ANSI, which is how Irène becomes Irène.

Two Excel-only tricks are worth knowing. A first line of exactly sep=; tells Excel which delimiter to use; every other program treats that line as data, and the CSV to JSON converter reads it as a two-column header and then rejects the first real row with //Invalid CSV: Too many fields: expected 2 fields but parsed 3 — delete the line before converting. And the import path that avoids all of the above is Data → From Text/CSV: set File Origin to 65001: Unicode (UTF-8), click Transform Data, change the type of any identifier column to Text, and only then Load. The legacy Text Import Wizard has the same choice on step 3 (Column data format: Text). Google Sheets offers it as one checkbox on File → Import: untick Convert text to numbers, dates and formulas.

Encodings: BOMs, Windows-1252 and UTF-16

A CSV is bytes, and nothing in it says which character encoding produced them. Four encodings account for nearly every export you will meet, and each misreads in a recognisable way:

To find out which you have, run file -I export.csv on macOS (file -i on Linux); it answers charset=utf-8, charset=iso-8859-1 or charset=utf-16le. xxd -l 4 export.csvshows the first bytes so you can compare them with the table. The converter’s Load File button reads the file as UTF-8 (the browser’s FileReader.readAsText default), so a Windows-1252 file shows � wherever there was an accent — convert it with iconv first, or open it in an editor that detects the encoding (VS Code shows it in the status bar and offers Reopen with Encoding) and paste from there, since the clipboard carries Unicode. A UTF-8 BOM is harmless here: the parser strips it before reading the header.

CSV is all strings: how the converter decides on types

Every CSV cell is text. Turning it into JSON means deciding, cell by cell, whether 007is the number 7 or the string “007”, and no parser can know which you meant. The CSV to JSON converter runs PapaParse with dynamic typing on, the first row as the header, and blank lines skipped. Its exact decisions:

The header row becomes the keys exactly as written, so a header of id, name gives a key of " name" with the space. Duplicate headers are renamed name, name_1. Rows with more or fewer fields than the header abort the whole conversion with a Too many fields or Too few fields message rather than producing a half-right file. The output is a two-space-indented array of objects, and Download saves it as output.json. There is no switch to keep a column as text, so treat the table above as the contract: if a column carries leading zeros or timestamps whose offset matters, either pad and reformat afterwards (String(zip).padStart(5, '0')when you know the width) or parse in code with typing off — PapaParse’s dynamicTyping: { zip: false }, pandas’ read_csv(path, dtype=str), or Python’s csv.DictReader, which never types anything.

Nested JSON has no flat shape

A JSON object can hold objects and arrays; a CSV cell holds one string. Going from tree to table therefore means choosing among three strategies, and the right one depends on who opens the file:

• Flatten objects into dotted columns — address.city, address.geo.lat. Right for one-level nesting that a spreadsheet user will filter on.
• Keep arrays as JSON text in the cell — ["Drama","Mystery"]. Right when the array is a tag list nobody will pivot on, and it round-trips.
• Explode arrays into rows — one row per film-and-genre pair. Right for analysis, wrong for a contact list (the person appears four times).

The JSON to CSV converter does the first two. It parses the input with JSON5, so trailing commas, comments and single-quoted strings are accepted, and it takes either an array of objects or a single object. Nested objects become dotted headers; a key that itself contains a dot is written a\.b so it cannot be confused with nesting. Arrays of any kind are stringified into the cell and, because that text contains quotes and commas, wrapped and escaped per RFC 4180 — the sample data on the page shows this on its genre and actor columns:

director,genre,year,title
"Frankenheimer, John","[""Drama"",""Mystery"",""Thriller"",""Crime""]",1962,The Manchurian Candidate

The header is the union of every key seen in any object, in first-seen order, which handles API responses where optional fields appear on some records only. Two consequences of the library defaults need care. A key that is missing from a record is written as the literal word undefined, and a JSON null is written as null; only an empty string gives an empty cell. Normalise before converting if the file is for a spreadsheet — jq 'map(.zip //= "")' fills a missing or null zip — or search the output for ,undefined afterwards. And values are quoted only when they contain a comma, a quote or a line break, so a string "00123" is written bare as 00123, which Excel will strip on open; the protection has to happen at import time, as described above, not in the file. The output uses LF line endings and no BOM, and Download names it output.csv.

One trap sits before the converter even runs: JSON numbers are parsed into JavaScript doubles, so an ID such as 1234567890123456789 becomes 1234567890123456800 the moment it is read, in both this tool and the NDJSON one. Any 64-bit identifier — Snowflake IDs, Twitter status IDs, many database primary keys — must already be a string in the source JSON, which is why well-designed APIs emit them that way. For the third strategy, exploding, use jq: jq -r '.[] | .title as $t | .genre[] | [$t, .] | @csv' films.json writes one properly quoted line per title-and-genre pair.

NDJSON: one object per line

A conventional JSON export is one array, and a consumer has to read it to the closing bracket before it can use a single record. Newline-delimited JSON — NDJSON, also called JSON Lines with the extension .jsonl — drops the array and the commas and puts one complete object on each line, with no line breaks inside an object. That makes the file streamable and makes ordinary Unix tools work on it: wc -l counts records exactly, grep filters them, split -l 100000 chunks a huge export, and head gives a valid sample. BigQuery load jobs require it (bq load --source_format=NEWLINE_DELIMITED_JSON dataset.table data.ndjson), Elasticsearch’s _bulk endpoint wants it with Content-Type: application/x-ndjson, and most log shippers emit nothing else.

The JSON to NDJSON converter takes an array and writes each object element compacted onto its own line, ending with a newline; elements that are not objects — bare strings, numbers, null — are dropped, so check that the output line count matches the array length. A single top-level object becomes a single line. Nested objects and arrays inside a record are kept intact, since NDJSON only forbids line breaks, not structure; JSON5 extras such as NaN come out as null. Download saves output.ndjson with the application/x-ndjson type. The reverse direction needs a different tool, because two objects on consecutive lines are not valid JSON — pasting NDJSON into a JSON converter fails with //Invalid JSON. On the command line, jq -c '.[]' data.json > data.ndjson and jq -s '.' data.ndjson > data.json go each way.

How to tell which case you are in

The converters fail loudly rather than guess, and each message points at one cause:

• //Invalid CSV: Too many fields: expected 6 fields but parsed 7 — a value contains the delimiter and is not quoted (a note field with a comma, a name written Doe, John without quotes), or a row has a trailing comma. Find it with awk -F, '{print NF}' export.csv | sort | uniq -c, remembering that quoted line breaks confuse awk too.
• //Invalid CSV: Too few fields: expected 3 fields but parsed 2 — a truncated last line, or a file that mixes delimiters.
• //Invalid CSV: Quoted field unterminated — a field opened with a quote and never closed, usually because a value containing a quote was written without doubling it and the parser is now reading the rest of the file as one cell.
• //Invalid CSV: Trailing quote on quoted field is malformed — text after a closing quote, such as "ab"c; the quote inside the value should have been "".
• //Invalid CSV: Unable to auto-detect delimiting character — one column, or a sep= line, or a file so ragged no delimiter gives two fields consistently.
• //Invalid JSON on the JSON side — NDJSON pasted instead of an array, a truncated download, or a Python repr with None and True instead of null and true.

Silent failures are the ones to hunt for: é and ’ mean UTF-8 read as Windows-1252; � means the reverse; ÿþ at the top of the file means UTF-16; a first key of id means a BOM survived; a row count from wc -l that disagrees with the record count means quoted line breaks; and a column of IDs ending in 00 means they went through a double somewhere.

A reliability checklist

1. Count records, not lines. Compare the array length in the JSON with the number of data rows your source system reports; on NDJSON, wc -l is exact.
2. Spot-check the ugliest row. Find a record whose text contains a comma, a double quote and a line break, and confirm all three survived in one field.
3. Check every identifier column. Leading zeros, 16-digit numbers and anything longer than 15 digits: are they still strings, and still exact?
4. Round-trip a sample. CSV → JSON → CSV should give back the same cells except for typing; anything else has been rewritten, and the diff shows you what.
5. Keep the raw export unchanged and work on copies. These exports usually contain customer records, which is also the reason the converters run entirely in the browser rather than on a server.