Skip to main content

Developer

Convert CSV to JSON Without Breaking Your Data

Why CSV to JSON conversions go wrong - quoted commas, line breaks inside cells, delimiters, and lost leading zeros - and how to check the output before it reaches your app.

By 9 min read

Article

CSV looks like the simplest data format there is: values separated by commas, one record per line. That simplicity is exactly why conversions fail. A spreadsheet export that opens perfectly in Excel can turn into JSON with shifted columns, split addresses, or ZIP codes that lost their leading zero.

This guide walks through the specific places CSV hides complexity, what a correct conversion has to handle, and a short checklist for verifying JSON output before you import it into an API, a database seed, or a front-end fixture.

Who this is for

Developers loading spreadsheet exports into an app, analysts handing data to an engineering team, and anyone who has received a CSV from a CRM, a bank, or a form tool and needs it as JSON. You do not need to know the CSV specification by heart; you need to know which parts of it bite.

CSV has a specification, and most problems come from ignoring it

The closest thing CSV has to a standard is RFC 4180. Its core rules are short: records are separated by line breaks, fields are separated by commas, and any field that contains a comma, a double quote, or a line break must be wrapped in double quotes. A double quote inside a quoted field is written as two double quotes.

That last pair of rules is where naive converters break. Splitting each line on commas works until a customer address reads "12 High Street, Apt 4". Splitting the file on newlines works until someone typed a two-line note into a comment field. A converter that does not track whether it is inside a quoted field will produce a row with the wrong number of columns, and every value after the break slides into the wrong key.

  • A comma inside quotes is data, not a separator: "Smith, Jane" is one field.
  • A line break inside quotes is data, not a new record.
  • Two double quotes inside a quoted field mean one literal quote: "He said ""hi""" becomes He said "hi".
  • Spaces around a comma are part of the value unless your tool trims them.

Delimiters are not always commas

Excel uses the list separator from your operating system’s regional settings. In much of Europe the decimal separator is a comma, so the list separator becomes a semicolon, and a file called export.csv is actually semicolon-separated. Database tools and log exporters often produce tab-separated values instead.

If you convert a semicolon file as if it were comma-separated, each row becomes a single giant field, and the JSON output has one key containing the whole header line. That failure is at least obvious. The quieter version is a comma-separated file whose values contain semicolons, converted with a semicolon delimiter: some rows split, others do not. Open the raw file in a text editor first and look at the header line before choosing a delimiter. ToolMint’s CSV to JSON tool lets you pick comma, semicolon, or tab explicitly for this reason rather than guessing.

Headers become keys, so check them

When the first row is a header, each header cell becomes a JSON key. Headers are written for humans, so they often contain spaces, trailing whitespace, duplicate names, or a byte order mark. A key of " Email" with a leading space will not match the email field your code expects, and the bug will look like missing data rather than a naming problem.

Duplicate headers are worse. Two columns named "Phone" collapse into one key in a JSON object, and one of the values silently disappears. Before converting, scan the header row for duplicates and rename them, for example to "Phone (home)" and "Phone (work)". If the file has no header row, turn the header option off so the first record is kept as data and you get arrays instead of objects.

Everything in CSV is text

CSV has no types. The value 007 is just three characters, and so is TRUE, and so is 2026-10-19. A careful converter keeps every value as a string and leaves type decisions to you, which is what ToolMint’s converter does. That can feel inconvenient when you wanted numbers, but it protects data that only looks numeric.

Postal codes, phone numbers, account numbers, and product SKUs are identifiers, not quantities. Converting 02134 to the number 2134 destroys it. Very long IDs are a second trap: JavaScript numbers lose precision beyond 2^53, so a 19-digit order ID converted to a number comes back with different final digits. Convert to numbers only the columns you will actually do arithmetic on, and do it in your own code where you can name them.

Spreadsheets change data before you ever export it

Many CSV problems are created upstream. Excel converts long numbers to scientific notation, strips leading zeros from anything it treats as a number, and turns some gene names and part numbers into dates. Once the file is saved, the original value is gone, and no converter can restore it.

If you control the export, format identifier columns as Text before saving, or export directly from the source system instead of round-tripping through a spreadsheet. If you received the file, spot-check a few known records against the source before trusting the whole set.

A verification checklist for the JSON output

Conversion is fast; finding a shifted column after it reached production is not. Spend two minutes checking the result before you use it.

  • Count records: the JSON array length should equal the CSV line count minus the header, unless some fields contain line breaks.
  • Check that every object has the same set of keys. A missing or extra key usually means an unquoted comma in that row.
  • Open the first, last, and one middle record and compare them field by field with the source.
  • Search for values that start or end with a quote character, which signals a quoting problem.
  • Confirm identifiers kept their leading zeros and full length.
  • Run the output through a JSON validator before committing it as a fixture.

Going the other way

Converting JSON back to CSV raises the opposite problem: JSON can nest, and CSV cannot. An object inside an object has no natural column. Good converters either flatten nested keys into names like address.city or serialise the nested value as a JSON string inside one cell. Decide which you want before you export, because a downstream spreadsheet user will see either dotted column names or JSON text in a cell.

Arrays of objects with different keys also need a decision. The CSV header must be the union of every key, and records missing a key get an empty cell. That is correct, but it can make a sparse dataset look broken to someone who did not expect it.

Privacy when the data is real

CSV exports are often the most sensitive files a team handles: customer lists, payroll, transactions. Uploading them to an online converter creates a copy on a server you do not control. ToolMint’s CSV and JSON converters parse the file in your browser, so the data is not sent anywhere for conversion. For regulated data, follow your organisation’s rules regardless of where the tool runs.

Takeaway

Most broken CSV conversions come from four things: quotes and line breaks inside fields, the wrong delimiter, messy headers, and treating identifiers as numbers. Look at the raw file first, choose the delimiter deliberately, keep values as strings until you have a reason not to, and verify the record count and a few rows before the JSON goes anywhere important.