Convert CSV to JSON without breaking quotes or zeros

To convert CSV to JSON safely, use a converter that follows the CSV standard for quotes and line breaks, detects the separator, and keeps every value as text unless you ask for numbers. Most damage to data does not come from the conversion. It comes from opening the CSV in a spreadsheet first, which strips leading zeros and rounds long numbers before the converter ever sees them. This article shows what a correct conversion looks like, the five things that go wrong, and how to test a round trip on your own file.

What a correct conversion looks like

CSV looks simple, one record per line with commas between values, but a value is allowed to contain a comma, a quote or even a line break, as long as it is wrapped in quotes. A quote inside a quoted value is written twice. Here is a small file with all of these, plus an id with leading zeros and a very long number:

id,name,zip,note,balance
007,"Nguyen, An",01234,"He said ""hi""",12345678901234567890
008,Binh,70000,"line one
line two",1.50

Converted by the CSV and JSON converter with its defaults, it becomes:

[
  {
    "id": "007",
    "name": "Nguyen, An",
    "zip": "01234",
    "note": "He said \"hi\"",
    "balance": "12345678901234567890"
  },
  {
    "id": "008",
    "name": "Binh",
    "zip": "70000",
    "note": "line one\nline two",
    "balance": "1.50"
  }
]

The comma inside "Nguyen, An" did not split the value, the doubled quotes became one quote, the line break inside the cell became \n, and nothing that looked like a number was touched: 007 and 01234 kept their zeros, and the 20-digit balance is intact.

The five things that go wrong

1. Leading zeros disappear

Postcodes, phone numbers, product codes and ids such as 007 are text that happens to be made of digits. A converter that guesses "this is a number" turns 01234 into 1234. The converter here keeps every value as text by default. If you tick "Convert numbers, true/false and empty cells", plain numbers such as 70000 become JSON numbers, but a value with a leading zero stays text.

2. Long numbers lose their last digits

JavaScript, and therefore most JSON tooling, stores numbers as 64-bit floating point values. Whole numbers are exact only up to 9,007,199,254,740,991, a 16-digit number. Beyond that they are rounded: in JavaScript, 12345678901234567890 becomes 12345678901234567000, and 9007199254740993 becomes 9007199254740992. An order number, a card number or an id from a database can be silently altered the moment a program reads it as a number. That is why the converter keeps an integer with more than 15 digits as text even when conversion is on, and why ids with that many digits are better written as strings in JSON in the first place.

3. Excel damaged the file before you converted it

Opening a CSV in Excel by double-clicking converts what looks like a number. 01234 becomes 1234. Excel keeps only 15 significant digits, so a longer number gets zeros where its last digits were, and it is often shown as 1.23457E+19. Codes that look like dates, for instance 1-2, can turn into dates. If you then save the file, the damage is permanent. To avoid it, convert the CSV straight from the source, or import it instead of opening it: in Excel use Data, then From Text/CSV, and set the column to Text; in Google Sheets use File, then Import, and untick "Convert text to numbers, dates, and formulas".

4. The separator is not a comma

In many European and Vietnamese settings the decimal mark is a comma, so spreadsheets save with a semicolon between values. If a converter assumes commas, every record lands in one column. Leave Separator on "Detect automatically": the tool splits the first rows with the comma, semicolon, tab and pipe, ignoring separators inside quotes, and picks the one that gives the same number of columns on most rows. A semicolon file such as name;price with the value 1,50 converts correctly, with 1,50 as one value. You can also choose the separator yourself.

5. Accents turn into garbage

A CSV is only text, and Excel decides how to read it. A file in UTF-8 without a byte order mark often shows Vietnamese or other accented letters wrongly. When you convert JSON to CSV for Excel, tick "Add a byte order mark" so Excel recognises UTF-8. When you read a CSV that has one, the converter removes it for you.

Converting step by step

1. Open the CSV to JSON tab. Paste the CSV, or drop a file or press "Open a file".
2. Leave Separator on automatic and "First row is the header" ticked to get an array of objects named by column. Choose "Array of arrays" if there is no header.
3. Keep "Convert numbers, true/false and empty cells" off unless your program needs real numbers. When it is on, an empty cell becomes null, and so does a cell that contains the word NULL, and true and false become booleans.
4. Check the line under the result, which says how many rows and columns it found, and any warning. An opening quote that is never closed makes the rest of the text one field, and the tool names the line.
5. Copy the JSON or download it. To check that it is valid JSON, paste it into the JSON formatter.

JSON back to CSV

Paste an array of objects. The columns are every key that appears in any record, in the order they first appear, and a record that lacks a key gets an empty cell. Nested objects are flattened one documented way: the keys are joined with dots, and array items are numbered. This input:

[
  {"id": 1, "customer": {"name": "An", "city": "Ha Noi, VN"}, "items": ["pen", "ink"]},
  {"id": 2, "customer": {"name": "Binh"}, "items": ["pad"]}
]

gives the columns id, customer.name, customer.city, items.0 and items.1, and the second row has empty cells where it has no value. "Ha Noi, VN" is wrapped in quotes in the CSV because it contains a comma. If you would rather have one column holding each array as JSON text, choose "Keep as JSON in one cell".

Test the round trip on your own file

Before you trust a conversion on a large file, convert it to JSON, press "Use the result as input", convert it back to CSV and compare the two CSVs with the diff checker. For plain data like the example above the text comes back identical. If something differs, the highlighted line shows exactly which value changed.

A warning about formulas

If a cell starts with =, +, - or @, Excel and other spreadsheets treat it as a formula when you open the file. That is a known risk when the data came from users or an untrusted source. Check such data before you open it in a spreadsheet.

A short checklist

Convert from the original CSV, not from a spreadsheet's save of it. Keep values as text unless a program needs numbers. Let the separator be detected, add a byte order mark when the CSV is going to Excel, and test a round trip before you convert something large.