CSV to JSON: nested arrays, typed fields, and mapping real-world data

Why "just convert my CSV to JSON" isn't enough

The naive CSV-to-JSON output looks like this:

[
  {"name": "Ada", "age": "37", "verified": "true"},
  {"name": "Grace", "age": "42", "verified": "false"}
]

Every API you'd feed this to will reject it or misbehave. Ages are strings, not numbers. Booleans are strings, not booleans. Nested fields (like an address) are flattened into columns you now need to un-flatten.

Reddit threads on r/webdev, r/learnprogramming, and r/n8n hit this constantly — the conversion "worked" but the destination rejected the data.

The typed output you probably want

[
  {"name": "Ada", "age": 37, "verified": true},
  {"name": "Grace", "age": 42, "verified": false}
]

Three transformations happened:

1. Numeric strings became numbers. "37" → 37.
2. Boolean-like strings became booleans. "true" → true.
3. Everything else stayed a string.

CSV to JSON does this automatically with sensible defaults, and lets you override per column when the guess is wrong (e.g., zip codes that look like numbers but should stay strings to preserve leading zeros).

Nested structures from column names

APIs often want nested JSON:

{"name": "Ada", "address": {"city": "London", "country": "UK"}}

Your CSV can't natively express nesting, but a common convention uses dot-separated column names:

name,address.city,address.country
Ada,London,UK

The converter reads dots and produces the nested structure. Works for arbitrary depth (address.geo.lat, address.geo.lng).

For arrays of primitives, some tools support bracketed columns (tags[0], tags[1]) or comma-separated cell content ("red,blue,green"). CSV to JSON supports both — pick whichever your source data naturally uses.

The zip code problem

The single most common data-corruption bug in CSV-to-JSON: leading zeros disappear.

• CSV: 01234
• Naive JSON: 1234

Zip codes, product SKUs, phone numbers, ID fields — all of them can start with zero, and all of them break if converted to numbers. The fix is to specify these columns as strings explicitly. In CSV to JSON, toggle "keep as string" for the affected columns.

Related problem: very long numbers (>15 digits, like credit card IDs) exceed JavaScript's safe integer range and get rounded. Same fix — keep as string.

Date handling

Dates are the trickiest column type. CSV has no date format standard; JSON has ISO 8601 ("2027-01-15T10:30:00Z").

Common source formats:

• 2027-01-15 (ISO) → converts cleanly to "2027-01-15".
• 15/01/2027 (European) → ambiguous. Is 03/04/2027 March 4 or April 3?
• Jan 15, 2027 → needs locale-aware parsing.
• Excel serial numbers (44576) → the number of days since 1900-01-01. Not a date until you convert.

For anything ambiguous, specify the source date format explicitly. The safest habit: standardize source data to ISO 8601 in Excel before exporting to CSV.

Character encoding

Non-ASCII characters (accents, non-English scripts, emoji) look wrong if the CSV's encoding doesn't match what's opening it. Modern default is UTF-8; older Excel exports on Windows are often Windows-1252.

If you see garbled characters in the JSON output — café instead of café — the source CSV was mislabeled as UTF-8 when it's actually Latin-1. Re-export from source as UTF-8 explicitly.

The delimiter question

Not all "CSV" files use commas. European locales often use semicolons because commas are decimal separators. Some tools use tabs (TSV). Some use pipes.

Most modern converters auto-detect. If yours doesn't and produces one giant column, specify the delimiter manually.

Common workflows

• API accepts JSON, data lives in Excel: Excel to CSV → CSV to JSON. Two steps because most Excel exports need cleanup between.
• API returned JSON, need it in Excel for analysis: JSON to CSV → CSV to Excel.
• Just need JSON for a config file: CSV to JSON directly. Small datasets don't need the round trip.

Common failure modes on the receiving end

Even a well-formed JSON often fails at the destination API. Order of investigation:

1. Root structure. Some APIs want [{...}, {...}]; others want {"records": [{...}]}. Wrap or unwrap as needed.
2. Field names. APIs often use camelCase; CSV headers often use snake_case or Space Separated. Rename headers in the source, or use a converter that normalizes.
3. Required fields missing. Empty CSV cells become "" or null. If the API requires a field, either fill defaults in source or filter out incomplete rows.
4. Enums. "Active" and "active" are different strings. If the API expects lowercase enums, normalize before export.

The privacy piece

Customer lists, employee records, transaction logs, medical data — the CSV files you'd want to convert to JSON are almost always sensitive. Every "free CSV to JSON online" tool uploads the file. CSV to JSON on DashConvert runs the conversion in your browser; the file stays on your machine, and the output stays on your machine too. Works on files with millions of rows on any modern laptop.