CSV to JSON: A Beginner's Guide

If you work anywhere near spreadsheets and code, you'll eventually run into this exact situation: someone hands you a CSV export from Excel or Google Sheets, and you need it as JSON for an API, a script, or a database import. Or the reverse, you have JSON and someone wants a spreadsheet.

It sounds like it should be trivial, and mostly it is, until your data has a comma inside a value, or a name with an apostrophe, or a blank cell, and suddenly your "quick conversion" is producing broken output. Here's what's actually going on under the hood, so you can do this reliably.

What CSV and JSON actually are

CSV (comma-separated values) is a plain text format for tables: one line per row, values separated by commas, and usually a header row naming each column. It's what you get when you export a spreadsheet as plain text.

JSON (JavaScript Object Notation) represents structured data as nested objects and arrays, the format nearly every API, config file, and modern app expects. A table of people in CSV becomes, in JSON, an array where each row is one object with the column names as keys.

The part that trips people up: quoting

CSV's one-comma-separated-line-per-row rule breaks the moment a value itself contains a comma, like a name written "Smith, Jane" or an address with a comma in it. The CSV standard handles this by wrapping such values in double quotes: "Smith, Jane". A naive converter that just splits every line on commas will incorrectly treat that quoted comma as a new column, silently shifting every value after it one column to the right.

A second, sneakier issue: what if a value itself contains a double quote? The standard says you escape it by doubling it, "She said ""hello""" represents the text She said "hello". Converters that don't implement proper CSV parsing (rather than a simple string split) will mishandle this too.

The other trap: numbers that aren't really numbers

CSV has no concept of data types, everything is just text. This causes a specific, easy-to-miss bug: a ZIP code like 01234 or a product code like 007 has a meaningful leading zero. If your converter automatically detects and converts number-looking values into actual JSON numbers, that leading zero silently disappears, 01234 becomes 1234, and now your ZIP code is wrong.

The safe default is to keep everything as text unless you specifically know a column is safe to convert, good tools give you this as a toggle rather than an automatic, all-or-nothing decision.

Doing it correctly

1. Use a converter that implements proper CSV parsing (quote handling, escaped quotes, embedded commas and line breaks) rather than a naive comma-split.


2. If your data has any column with meaningful leading zeros (ZIP codes, ID numbers), leave number detection off, or check the output carefully if you turn it on.


3. Check your delimiter, spreadsheets in some regional locales export with semicolons instead of commas. If your output looks like one giant column, this is usually why.


4. Spot-check a row or two of the output against the original, especially any row that had a comma or quote inside a value, that's where naive tools break first.

I built FileForge Tools, 38 free file conversion and document tools that all run client-side, including a proper CSV to JSON converter that handles all of this correctly. No uploads, no accounts, no watermarks.