Automation & Operations3 min read
How to Convert CSV to JSON and JSON to CSV for Free
Spreadsheets speak CSV and APIs speak JSON. Converting between them is easy until a value contains a comma. Here is how to do it without mangling anything.
KLYRO TeamPublished
CSV is what spreadsheets export. JSON is what APIs expect. Moving data between the two is one of the most common small jobs in any business, and it goes wrong more often than it should.
The reason is nearly always the same: a value that contains the character used to separate values.
What each format is good at
CSV is a table. One header row, then one row per record, values separated by commas. Every spreadsheet on earth opens it. It has no concept of nesting, and no types: everything is text.
JSON is a structure. It has real types, it nests, and it is what almost every web API sends and receives. It is also much harder to read by eye once there is more than a screenful.
Converting between them is straightforward when the JSON is a flat array of objects with the same keys. That is exactly what a table is.
How to convert
Pick a direction
CSV to JSON, or JSON to CSV. The panel on the left is what you paste; the right is the result.
Paste or open your data
You can open a file from your machine as well. Either way it is read by your browser and never uploaded.
Check the delimiter
Comma is the default. Exports from European spreadsheet settings often use a semicolon, and exports from databases often use a tab.
Copy or download the result
The converted output is ready to paste into whatever needed it.
The two settings that matter
Delimiter. If your CSV opens as a single column in a spreadsheet, the delimiter is wrong. Semicolon is common in exports from systems set to a European locale.
Keeping values as text. Turn this off and 00123 becomes 123, +44 20 7946 0000 becomes something unrecognisable, and a product code like 1E5 becomes 100000. Leaving values as text is almost always what you want, and it is why the converter defaults to it.
When JSON will not become a table
Not all JSON is table-shaped. If an object contains another object, or an array of its own, there is no single obvious column for it. The converter says so and names the field, rather than flattening it into something that looks fine and is not.
The fix is usually to decide what you actually want: the nested field as a JSON string in one column, or one row per nested item. Both are reasonable, and only you know which.
Common mistakes
- Splitting a CSV on commas in a spreadsheet formula or a quick script. It works until a customer has a comma in their address.
- Letting a converter guess types, then wondering where the leading zeros in postcodes went.
- Converting a file with a byte order mark at the start and getting a first column name with an invisible character in it.
- Assuming every row has the same number of columns. Exports from hand-edited spreadsheets often do not.
- Uploading customer data to a random online converter. Check where the conversion actually happens before you paste anything real.
Questions
- Is my data uploaded?
- No. The conversion runs entirely in your browser, which is the reason this tool is safe to use with real customer data.
- How large a file can it handle?
- It is bounded by your browser's memory rather than by an upload limit. Everyday exports of a few thousand rows are no problem; a very large file is better handled outside a browser.
- Does it handle Excel files?
- Not directly. Save or export as CSV first — every spreadsheet application can do that — and the converter takes it from there.