Converting CSV to JSON Without Breaking Your Data: 7 Gotchas

Converting CSV to JSON looks like a five-line job: split on newlines, split on commas, zip each row with the header. That version works on the example in the tutorial and then corrupts the first real export you give it. Usually it does so silently, which is worse than failing.

Here are the seven problems that turn up most often in real exports, what each one does to your data, and how to handle it. Every example can be pasted into the CSV to JSON Converter to see the behaviour for yourself.

1. Leading zeros disappear

A US ZIP code like 02139, a UK phone number, an employee number 007: all of them look like numbers, and all of them are really text. A converter that “helpfully” turns anything numeric into a JSON number produces 2139, and the zero is gone for good.

The usual fix is to turn type detection off entirely, which leaves you with "42" for every genuine number. A better rule works per column. If any cell in a column has a leading zero, keep the whole column as text. Otherwise one ZIP comes out as "02139" and the next as 10001, and every piece of code reading the JSON has to cope with two types for one field.

2. Long IDs get rounded

JavaScript numbers are 64-bit floats, which store integers exactly only up to 2^53 − 1, or 9,007,199,254,740,991. Above that, digits are lost. Run Number("9007199254740993") in a browser console and you get 9007199254740992. Nothing errors. The ID has simply changed.

Order numbers, Twitter/X snowflake IDs and bank references are regularly longer than that. If one of them is written into JSON as a number, every consumer that parses it with JSON.parse sees the wrong value. Integers longer than 15 digits should stay strings, and a converter should do that for you rather than leave you to find out in production.

3. The delimiter is not a comma

In Germany, France, Italy, Spain and much of the rest of Europe, the comma is the decimal separator. Excel in those locales saves “CSV” with semicolons between fields, so 3,5 can stay a number. Feed that file to a comma-only parser and every row becomes one long field.

Detecting the delimiter is not hard, but it has to be done properly. Count each candidate — comma, semicolon, tab, pipe — on the first few lines, ignoring anything inside quotes, and pick the one that appears the same number of times on every line. A comma inside "Smith, John" must not count.

4. Quotes, commas and line breaks inside a field

RFC 4180, the closest thing CSV has to a standard, allows a field to be wrapped in double quotes. Inside the quotes, commas and even line breaks are part of the value, and a literal quote is written twice:

id,comment
1,"Arrived late, but ""fine"" overall"
2,"Line one
line two"

That is two records, not three. Any parser built on split('\n') and split(',') gets both of them wrong. A notes or address column from a real CRM export will contain all of these cases.

5. The invisible byte-order mark

Excel’s “CSV UTF-8” option starts the file with a byte-order mark, the invisible character U+FEFF. A naive parser keeps it as part of the first header, so your first key becomes "id" instead of "id". It looks identical when printed, and record.id is still undefined. Strip it before parsing.

6. Ragged rows

Hand-edited spreadsheets often have a row with a missing trailing cell, or one with an extra value typed into a column that has no header. Some tools drop those rows. Others shift the values into the wrong keys. Both lose data without saying so.

The safe behaviour is to fill missing cells as empty and give extra cells a generated header like column_4. Then tell the user which line it happened on, so they can fix the source instead of guessing.

7. Empty is not the same as null

An empty cell can mean “unknown”, “not applicable” or “an empty string”, and different consumers want different things. APIs usually expect null. Some schemas want the key left out entirely. A few need "". Pick one on purpose and apply it consistently. If a column name happens to be the text null, check it too.

Choosing the output shape

An array of objects is the default, but it is not always the best fit:

• Array of objects — what REST APIs, fixtures and MongoDB imports expect.
• Arrays with a header row — compact, good for tables and charts.
• One array per column — the shape most chart libraries take for a series.
• JSON Lines — one object per line with no surrounding array. BigQuery and most log pipelines want this, because a huge file can be streamed line by line.

And if your headers look like address.city and address.zip, you probably want them nested into an address object rather than left as dotted keys.

Do it without uploading the file

The CSV you most need to convert is usually the one you should least upload: a customer list, a payroll export, a CRM dump. The converter here reads the file with your browser’s File API and never sends it anywhere, for the reasons set out in browser-based developer tools. After converting, a diff checker is a quick way to compare two exports. If the JSON came from an API rather than a spreadsheet, extracting URLs from API responses covers the next step.