How to Convert CSV to JSON Without Losing Data Along the Way

A classic of integration life: the backend needs JSON, but the accounting system exports CSV. Or the other way around — an analyst asks for "a spreadsheet in Excel" and all you have is an array of objects. The job takes a minute if you know the two or three format traps where data usually gets "lost."

What CSV is — and why it's sneaky

CSV is a table in plain text: each line is a record, and the values within a line are separated by commas. It looks primitive, and the primitiveness is exactly what causes problems: the format has no single standard, so "the same CSV" looks different in different programs.

• Delimiter. Excel in many locales defaults to a semicolon, because the comma is already busy as the decimal separator. English-language systems expect a comma. This is the most common reason a file "won't parse."
• Quotes. If a value contains a comma or a line break, it gets wrapped in quotes. Parsers without quote support slice such lines in half.
• Leading zeros. Excel happily turns SKU "0042" into the number 42 — which no longer matches the SKU in the other system.
• Dates. Excel may silently turn "03.09" into a date in the current year.

Converting to JSON in a minute

Copy the contents of the file and paste it into the CSV to JSON converter. Choose the delimiter (comma or semicolon) and mark the first row as headers: it becomes the object keys. What comes out is a tidy array of objects that you can immediately check in the JSON formatter — we have a separate article on reading and validating JSON.

CSV:
sku;name;price
0042;Chair;3500

JSON:
[ { "sku": "0042", "name": "Chair", "price": "3500" } ]

The way back: JSON to CSV

When JSON needs to be shown to a human as a table, the JSON to CSV converter saves the day: it flattens an array of objects into columns. One caveat — if the objects are uneven (some have a field, others don't), the column will simply stay empty for some rows, and that's normal.