Converting CSV to JSON without corrupting your data

CSV to JSON is a simple conversion with five predictable failure modes: leading zeros stripped from identifiers, commas inside quoted fields splitting rows, large numbers losing precision, encodings misread, and empty cells becoming the wrong empty value.

The straightforward case

name,age,city
Ada,36,London
Grace,45,New York

[
  { "name": "Ada", "age": 36, "city": "London" },
  { "name": "Grace", "age": 45, "city": "New York" }
]

The header row becomes the keys, each data row becomes an object, and values that look numeric become numbers. That last step is where most of the trouble starts.

1. Leading zeros disappear

The most damaging failure, because it is silent and it hits identifiers — the values you most need to be exact.

id,postcode
007,01234

// Converted with automatic type inference:
[{ "id": 7, "postcode": 1234 }]

// Both are now wrong, and nothing warned you.

CSV has no types — every field is text. Inference is a guess, and a good one for a column of measurements, a bad one for a column of employee numbers. If a column is an identifier, force it to stay a string.

2. Commas inside quoted fields

name,address,city
Ada,"12 Elm Street, Flat 4",London

That row has three fields, not four. A parser that splits on commas produces four, shifting every subsequent value into the wrong column. Splitting on commas is the single most common way a hand-rolled CSV parser corrupts data, and it fails on the first address in the file.

A quoted field may also contain newlines, so a "line" of CSV is not a line of text. Any parser that reads the file line by line is already wrong. Use a real CSV parser — Papa Parse in JavaScript, the csv module in Python — rather than writing one.

3. Large numbers lose precision

transaction_id
9007199254740993

// As JSON:
[{ "transaction_id": 9007199254740992 }]
//                                  ↑ the last digit changed

JSON numbers are IEEE 754 doubles, giving 53 bits of integer precision. Anything above 9,007,199,254,740,991 may be rounded. Transaction IDs, Twitter snowflake IDs and 64-bit database keys all exceed it. Keep them as strings.

4. Encoding mismatches

A CSV exported from Excel on Windows is frequently Windows-1252 or UTF-16, not UTF-8. Read as UTF-8, café becomes café. Excel also writes a byte-order mark at the start of UTF-8 files, which appears as an invisible character on the first header — so the first column becomes name and every lookup for name fails.

Detect the encoding from the byte-order mark where there is one, and strip it when there is. A tool that gets the first column name subtly wrong is worse than one that fails outright.

5. Empty cells are ambiguous

name,nickname,age
Ada,,36

// Which is right?
{ "name": "Ada", "nickname": "",   "age": 36 }   // empty string
{ "name": "Ada", "nickname": null, "age": 36 }   // null
{ "name": "Ada",                   "age": 36 }   // key omitted

CSV cannot distinguish "empty", "null" and "absent" — they are all nothing between two commas. Whichever a converter picks will be wrong for someone, so the important thing is that it picks consistently and says which.

A checklist before you convert

1. Open the CSV and look at it. Ragged rows and unterminated quotes are visible immediately in a table view and invisible in a text editor.
2. Identify every column that is an identifier rather than a quantity — IDs, postcodes, phone numbers, product codes — and keep them as strings.
3. Check the encoding. If accented characters look wrong in a viewer, they will be wrong in the output.
4. Convert, then check the row count matches. A drop means quoted fields were split.
5. Spot-check the last row. Errors accumulate, so the end of the file is where a shifted column shows most clearly.

Doing it in code

import Papa from 'papaparse';

const result = Papa.parse(csvText, {
  header: true,
  skipEmptyLines: true,
  // Off by default here on purpose: infer types only for columns
  // where inference is safe.
  dynamicTyping: (column) => column !== 'id' && column !== 'postcode',
});

if (result.errors.length > 0) {
  console.error(result.errors);   // do not ignore these
}

import csv, json

with open('data.csv', newline='', encoding='utf-8-sig') as f:
    rows = list(csv.DictReader(f))   # every value stays a string

print(json.dumps(rows, indent=2))

Convert CSV to JSONRuns in your browser, and reports what it had to transform.