Why convert between JSON and CSV?
JSON and CSV occupy opposite ends of the data-portability spectrum. JSON is hierarchical, self-describing, and the native format of every REST API and JavaScript runtime. CSV is flat, universal, and readable by every spreadsheet, database import wizard, and analytics tool on the planet.
The conversion need arises in a handful of predictable situations:
- API response → spreadsheet: You downloaded data from a REST endpoint and want to share it with a non-technical stakeholder who lives in Excel or Google Sheets.
- Database export → API payload: A legacy system exports CSV but the new service expects JSON.
- Data wrangling: You want to apply formulas, pivot tables, or visualizations to data that arrived as JSON.
How JSON→CSV flattening works
The tricky part of JSON→CSV is nested objects. CSV is inherently flat — every row has the same fixed columns. The converter handles nesting with dot notation: {"meta": {"city": "Mumbai"}} becomes a column called meta.city.
Arrays inside objects are serialized as a JSON string in a single cell. This preserves the data without losing it, though deeply-nested structures can produce wide, unwieldy CSV files. For those cases, restructure the JSON before converting or accept that CSV isn't the right output format.
How CSV→JSON works
The first row of a CSV file is treated as the column header. Every subsequent row maps headers to values, producing an array of objects. Values are coerced to their natural types:
"true"and"false"become booleans.- Integer strings like
"42"become numbers. "null"becomesnull.- Strings that look like JSON arrays or objects are parsed as their native types.
- Everything else stays as a string.
Type coercion is optional in some tools but on by default here, because the vast majority of CSV exports use bare unquoted numbers and booleans — keeping them as strings would require extra parsing downstream.
Delimiter choice matters
The comma is the default CSV delimiter, but it creates problems when values contain commas — phone numbers in Indian format, addresses, currency values. The standard fix is RFC 4180 quoting: wrap the value in double quotes. But some tools skip the quoting and just change the delimiter to a semicolon or tab.
- Comma (,): Universal default. Watch out for values with commas.
- Semicolon (;): Common in European locales where the comma is the decimal separator.
- Tab (\t): Produces TSV files. Tabs rarely appear in data, making quoting unnecessary in practice.
- Pipe (|): Used in legacy data pipelines and banking exports.
Match the delimiter to whatever the receiving tool expects. When in doubt, Tab is the safest choice.
Column order and missing values
When converting JSON arrays where different objects have different keys, the converter takes the union of all keys across all objects as the column set. Missing values are written as empty cells. This is the standard behavior and ensures no data is silently dropped.
Size considerations
JSON with indentation can be 30–40% larger than the equivalent CSV. If you're working with large datasets, CSV is the more compact choice for tabular data. For nested data, JSON is the only format that preserves the structure without information loss.