CSV and JSON Conversion: Where Data Quietly Goes Wrong
CSV looks simple until a comma appears inside a value. JSON looks tidy until an array contains nested objects. Moving between them is not just a syntax change; it is a decision about structure.
If that decision is wrong, the output can be valid and still be useless.
CSV is a table with escaping rules
A CSV row is not safely parsed by splitting on every comma. A field can contain a comma when it is quoted. Quotes inside quoted fields need escaping. Newlines can even live inside a field.
Malformed quoting is dangerous because a parser may shift every value into the wrong column. A good converter should reject ambiguous input rather than confidently inventing a table.
Duplicate headers are not harmless
Consider a CSV with two columns both called price. Turning the row into a JSON object with ordinary key/value pairs creates a collision: one value can overwrite the other.
During Kaazo's regression work, duplicate-header handling became a specific test because silent data loss is worse than a visible error. If headings repeat, rename them or use a conversion strategy that preserves each column distinctly.
JSON can be deeper than a spreadsheet
A flat object maps comfortably to columns. Nested objects and arrays do not. You need a flattening rule: dotted keys, JSON strings inside cells, repeated rows, separate tables—or something else dictated by the destination.
There is no universal “correct” flattening because the right answer depends on how the data will be used later.
Numbers, IDs and dates deserve caution
A value that looks numeric may really be an identifier. Leading zeros matter in postal codes, product codes and account references. Spreadsheet software can also reinterpret dates or large numbers after export.
Before converting a production dataset, test a few awkward records: an empty field, a quoted comma, Unicode text, a long ID, a date and a row with missing values.
A safer conversion routine
- Validate the source first. Fix malformed CSV or JSON before converting.
- Check headers and key names for duplicates.
- Convert a small sample.
- Open the result in the system that will actually use it.
- Compare row counts and a few edge-case records.
Use CSV ↔ JSON for ordinary transformations, CSV Cleaner when the table itself is messy, and JSON Formatter when readability—not conversion—is the problem.