CSV to JSON and JSON to CSV: Handling Commas, Quotes and Leading Zeros Correctly

A colleague hands you a spreadsheet with a thousand rows, and you need to feed it into an app that accepts only JSON. Or an API returns JSON and your manager wants a table to open in a spreadsheet program. The job looks easy, but quick conversions go wrong in specific, well-known places. Sahel's CSV and JSON converter converts both ways and follows the CSV rules as written, so your data is not damaged on the way.

What CSV actually is

CSV stands for "comma-separated values", a plain text file: each line is a row, each row is fields separated by a delimiter, most often a comma. The first line is usually the column headings. Every spreadsheet program opens it, which explains its popularity, but its simplicity is only apparent: the format has precise rules for fields that contain a comma, a quote or a new line.

What JSON is and how a table becomes it

JSON is a format that represents data as objects and arrays, and it is the language applications and servers speak to each other. When you convert a table to JSON, each row normally becomes an object whose keys are the column headings. A table with name and age columns becomes an array of objects, each with two properties.

The option "First row is the header" controls this. When on, the first line is treated as keys. When off, there are no names, so every row in the file is data and the result is an array of arrays.

The rule most conversions break: quoting

What if a field itself contains a comma, such as the address "Riyadh, Saudi Arabia"? Treating it as two fields would distort the row. CSV's answer is to wrap the field in quotes: "Riyadh, Saudi Arabia". And what if the field contains a quote mark? It is written twice: "She said ""hello""". A quoted field can also span several lines, so a new line inside the quotes is part of the field, not the start of a new row.

The tool understands all these rules when reading CSV and applies them when writing it: converting JSON to CSV, any value containing a comma, quote or new line is quoted automatically, so the file opens correctly in any spreadsheet program.

If you split CSV on commas, you will corrupt the first row of data that contains a comma. Use a parser that understands quoting.

Choosing the delimiter

The comma is the default, but not the only one. In many countries that use the comma as the decimal mark, as in some European regional settings, spreadsheet programs export with a semicolon. Some files use a tab, in which case it is called TSV, or a pipe. The tool lets you pick the delimiter from the list: comma, semicolon, tab or pipe. If the output looks like one long column, try another delimiter; the cause is usually that the file uses something other than you assumed.

Numbers and leading zeros

This is a dangerous silent error. A postcode like 00123 or a phone number starting with zero, if converted to a number, becomes 123 and the zero is lost. A long account number may turn into scientific notation in some programs. So the tool has an option: "Numbers, true/false and blanks become real values". Turn it on when you want actual numbers in the JSON, and off when you want every value to stay as the text it was written, which is the safest choice for identifiers, codes and phone numbers.

From JSON to CSV

JSON that is an array of objects converts to a table easily, with the keys as columns. The trouble appears when the objects differ in structure, or contain nested objects. A table is flat and cannot hold nesting, so the tool writes nested data into the cell as compact JSON text, which loses nothing and lets you convert it back. The tool also warns you when JSON is not valid and points to the position of the error: an unexpected character, an invalid escape, or text that ended too soon.

How to use it

1. Choose the direction: CSV to JSON or JSON to CSV.
2. Paste your data, and choose the delimiter if it is not a comma.
3. Set whether the first row is a header, and whether values become numbers.
4. Choose the JSON indentation or minified, then copy or download the result.

A realistic scenario

A small online shop exports its product list from a spreadsheet to CSV. It includes a description column containing commas and new lines, and a barcode column starting with zeros. The owner pastes the file into the tool, turns off value conversion so the zeros stay, and gets clean JSON that the shop's API accepts without damaging the descriptions or barcodes. Had he used a plain split on commas, the columns would have shifted in every row with a long description.

Frequently asked questions

How does CSV handle a comma inside a field?

The field is wrapped in quotes. The tool recognises this and writes it back the same way when converting.

What if the delimiter is a semicolon?

Pick the delimiter from the list: comma, semicolon, tab or pipe.

Do numbers stay numbers?

You can turn off value conversion so leading zeros are not lost.

Why was the JSON I pasted not accepted?

Usually a trailing comma, a single quote mark or an unquoted key. The tool shows where the error is so you can fix it.

Is my data uploaded to a server?

No. All the conversion happens in your browser, so you can use sensitive data.