Toolium

CSV to JSON: The Shape Question and the ZIP-Code Trap

Hemanth Gedda6 min read

The CSV-to-JSON conversion is the daily bread of anyone who works in the gap between spreadsheets and APIs. Someone exports a list from Excel; you need it as JSON to feed into a script, seed a database, or stuff into an API call. The mechanical part is easy. The interesting decisions are around shape (array of objects vs object keyed by ID), type handling (when does "123" stay a string?), and what to do when the CSV is malformed.

The shape question

JSON can represent a CSV table in a few different shapes:

  • Array of objects, headers as keys. [{name: "Alice", age: 30}, {name: "Bob", age: 27}]. This is what 95% of APIs and consuming code expect. The Toolium CSV to JSON tool produces this by default.
  • Array of arrays, no header object. [["name","age"],["Alice",30],["Bob",27]]. Useful when you want to preserve column order or when the schema is dynamic.
  • Object keyed by ID. {"u1": {name: "Alice"}, "u2": {name: "Bob"}}. Better for lookup-by-key code patterns. Not produced by default; transform the array-of-objects output if you need this.

The right shape depends on what consumes the JSON. APIs almost always want array-of-objects. Code that does dictionary lookups might want object-keyed-by-ID. There is no universal answer; pick what your destination expects.

Type inference, and where it gets weird

CSV stores everything as text. JSON has typed values (string, number, boolean, null). The conversion has to decide for each value: leave it as a string, or parse it into a typed value?

The Toolium tool's rules:

  • If a value parses as a valid number, it becomes a JSON number. "30" becomes 30, "3.14" becomes 3.14, "1e10" becomes 10000000000.
  • If a value is "true" or "false" (case-insensitive), it becomes a JSON boolean.
  • Otherwise, it stays as a string.
  • Empty cells become empty strings, not null. (If you want null instead, do a find-replace on the output.)

The trap: leading zeros. ZIP code "02134" parses as the number 2134, losing the leading zero. Same with phone numbers, account numbers, and any string that looks numeric but is meant to be text. The fix: in the source CSV, wrap these values in quotes. The parser sees the quotes and keeps the value as a string.

Same issue with floats: "1.0" parses as the number 1, dropping the trailing ".0." If you need to preserve "1.0" exactly, the value has to be a string in the source.

Duplicate column names

JSON object keys must be unique. If your CSV has two columns both named "Address" (one billing, one shipping), the resulting JSON object can only hold one of them - the second one overwrites the first.

The Toolium tool does not currently warn about this; it silently overwrites. The fix is to rename one of the columns in the source CSV before converting, or to switch to the array-of-arrays output shape where each row is just positional.

Quoted fields and embedded commas

CSV's escaping rules are simple but important. A value that contains a comma must be wrapped in double quotes. A value that contains a double quote must escape it as two consecutive double quotes. So the value He said "hello" in a column called "quote" appears in the CSV as:

quote
"He said ""hello"""

The Toolium parser handles this correctly. If you see output where commas inside cells are getting treated as separators, the source CSV is malformed.

JSON to CSV (the reverse direction)

The tool works both ways. Paste a JSON array of objects, get a CSV. The keys of the first object become the column headers. If later objects have different keys, you get partial rows - missing keys become empty cells. This is sometimes desired (you wanted a union of all keys) and sometimes not (you wanted a strict schema). The tool does the union behavior.

Nested objects in JSON do not have a clean CSV representation. The tool flattens them with dot notation: {user: {name: "Alice"}} becomes a CSV column called "user.name" with value "Alice". For more elaborate nesting, this gets ugly fast; consider pre-flattening the JSON in code before converting.

Arrays inside JSON values get serialized as JSON strings inside cells: a "tags" array of ["red", "blue"] becomes the cell value [\"red\", \"blue\"]. Round-tripping this back through CSV-to-JSON would not restore the array structure; the cell would just be a string.

The "is this CSV valid?" question

The parser is RFC 4180-compliant, which means it follows the formal CSV specification. Most CSV in the wild is sloppier than the spec - inconsistent quoting, mismatched column counts per row, BOM at the start of the file. The Toolium tool handles the common departures gracefully:

  • BOM at the start: stripped before parsing.
  • Mixed line endings (LF and CRLF): both work.
  • Trailing empty lines: ignored.
  • Rows with different column counts: not fatal, but the resulting JSON objects have only the columns that appeared.

Things that do break parsing: unmatched quotes (where the parser cannot tell where a quoted field ends), and binary data in cells. For binary data, the right move is to base64-encode it first.

Privacy and the absent server

Everything happens in your browser. Paste sensitive data, get the conversion, walk away. Nothing is uploaded. The conversion runs in JavaScript on your CPU, which is fast for files up to about 100,000 rows and noticeably slow past that.

Try the tool mentioned in this article

Open tool