Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

CSV to JSON in Python: Encoding, Types, and Serialization Pitfalls

CSV has no types, so every conversion is a guess. Four settings decide whether the output is faithful: the encoding, the newline mode, the dialect, and how NaN is serialized.

~4 min readPython
#Python#CSV#JSON#Data Processing
3D illustration of CSV row slabs folding into JSON brace objects with a BOM tag pulled away

Introduction & Quick Summary

Symptom. A CSV that opens correctly in Excel becomes mangled after a round trip through Python: the first header arrives as \ufeffid, an ID column loses its leading zeros, a phone number turns into 1.23457e+12, N/A becomes NaN, an address with a comma splits into two fields, and json.dump writes {"price": NaN} which is invalid JSON that browsers reject with Unexpected token 'N'.

Cause. CSV has no types and no encoding declaration, so every value is a string until something guesses. The four failures are: a UTF-8 BOM that Excel writes and utf-8 keeps as a character; delimiter and quote handling that depends on the file's dialect (, vs ;, " vs ' vs doubled quotes); naive type inference that strips leading zeros, converts long integers past 2^53 to floats, and maps true/NA/null to booleans and nulls; and newlines inside quoted fields, which break any parser that opens the file without newline=''.

Fix. Read with encoding='utf-8-sig', newline='', and an explicit csv.DictReader. Keep every value a string and convert on purpose, with a schema. Serialize with allow_nan=False so a NaN fails at write time instead of producing a file that JSON consumers reject.

Core Mechanics, Signatures & Reproducible Setup

The csv module implements RFC 4180 quoting: a field may contain the delimiter, quotes, and line breaks when wrapped in double quotes, and a literal quote is doubled. csv.reader needs newline='' because universal-newline translation inside the file object will otherwise split a quoted \r\n into two records. DictReader maps the first row to keys without deduplicating them, so two columns named value collapse silently.

A reproducible fixture that triggers every failure:

python
import csv, json, io raw = ( "\ufeffid,name,price,active,notes\r\n" "007,Widget,1.50,TRUE,\"First floor, aisle 3\"\r\n" "012,Gadget,2.75,FALSE,\"Line one\nline two\"\r\n" ) with open("/tmp/items.csv", "w", encoding="utf-8", newline="") as f: f.write(raw)
python
# Naive version — every bug at once with open("/tmp/items.csv") as f: rows = list(csv.DictReader(f)) print(rows[0]) # {'\ufeffid': '007', 'name': 'Widget', 'price': '1.50', 'active': 'TRUE', 'notes': 'First floor, aisle 3'}
python
# Correct reader with open("/tmp/items.csv", encoding="utf-8-sig", newline="") as f: reader = csv.DictReader(f) rows = [dict(r) for r in reader] print(rows[0]["id"], "|", rows[1]["notes"]) # 007 | Line one\nline two

The \ufeff disappears with utf-8-sig, the comma inside quotes stays in the field, and the embedded newline survives as a single value rather than a broken record.

Key Comparison Table

DecisionNaive choiceSafe choiceFailure it prevents
Encodingutf-8utf-8-sig\ufeffid key, KeyError
Open modeopen(path)open(path, newline='')Split records on quoted newlines
Readersplit(',')csv.reader / DictReaderBroken quoted commas and quotes
Numbersint(v) / float(v)int(v) after validationLost leading zeros, 1e+12
Booleansv == 'true'Explicit set of accepted valuesTRUE/1/yes silently false
EmptyNone'' or None by policyMixed null spellings in JSON
JSON dumpdefaultallow_nan=FalseNaN in JSON (invalid)
Dialect parameterWhenExample
delimiterNon-comma CSV; from European Excel
quotecharSingle-quote quoting'
doublequote=False + escapecharBackslash-escaped quotes\"
skipinitialspace, valueLeading spaces in fields
field_size_limitHuge text cellsRaise above 131072

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. Sniffing the dialect without guessing wrong

python
import csv with open("/tmp/items.csv", encoding="utf-8-sig", newline="") as f: sample = f.read(4096) f.seek(0) try: dialect = csv.Sniffer().sniff(sample, delimiters=",;\t|") except csv.Error: dialect = csv.excel # single-column files defeat the sniffer has_header = csv.Sniffer().has_header(sample) reader = csv.DictReader(f, dialect=dialect)

Sniffer predicts, and predictions are wrong on single-column files and on files whose first rows are uniform. The practical rule: sniff to log a warning, but let a caller-supplied dialect win. has_header is a heuristic on the first two rows; a file whose first data row is all text looks like a header.

2. Keeping identifiers as strings

python
# Broken: 007 -> 7, 19-digit id -> 1.234567890123457e+18 row["id"] = int(row["id"]) # Fixed: validate the shape, keep the string import re ID_RE = re.compile(r"^\d{3,19}$") def parse_id(v: str) -> str: if not ID_RE.match(v): raise ValueError(f"bad id: {v!r}") return v

JSON has one number type: a 19-digit ID cannot round-trip through a JavaScript Number. Emit IDs as strings and let the consumer decide. The same applies to phone numbers, postal codes, account numbers, and any code with a leading zero.

3. Typed conversion with a schema

python
from dataclasses import dataclass from decimal import Decimal, InvalidOperation TRUE = {"true", "t", "yes", "y", "1"} FALSE = {"false", "f", "no", "n", "0"} @dataclass class Item: id: str name: str price: Decimal active: bool notes: str @classmethod def from_row(cls, row: dict[str, str]) -> "Item": raw_price = row["price"].strip().replace(",", "") if not raw_price: raise ValueError("price required") try: price = Decimal(raw_price) except InvalidOperation as exc: raise ValueError(f"bad price: {raw_price!r}") from exc flag = row["active"].strip().lower() if flag not in TRUE | FALSE: raise ValueError(f"bad boolean: {flag!r}") return cls(row["id"], row["name"], price, flag in TRUE, row["notes"])

Decimal avoids the 0.1 + 0.2 class of error and serializes as a string. Recording the failing row number with the error is what makes a 50,000-row import debuggable: wrap from_row in a loop that reports line N.

4. Writing JSON that is actually valid

python
import json from decimal import Decimal def default(o): if isinstance(o, Decimal): return str(o) # or float(o) if the consumer needs a number raise TypeError(f"not serializable: {type(o)}") payload = [Item.from_row(r) for r in rows] json.dump([p.__dict__ for p in payload], open("/tmp/items.json", "w"), default=default, ensure_ascii=False, allow_nan=False, indent=2)

allow_nan=False turns a float NaN into a ValueError at write time. Without it Python happily writes NaN/Infinity, and JSON.parse in a browser throws Unexpected token 'N' — a failure that surfaces in the consuming application, days later.

5. Ragged rows and duplicate headers

python
reader = csv.DictReader(f) reader.fieldnames # ['id', 'name', 'price'] for line_no, row in enumerate(reader, start=2): if None in row: # extra columns -> key None raise ValueError(f"line {line_no}: too many columns: {row[None]}") missing = [k for k, v in row.items() if v is None] if missing: raise ValueError(f"line {line_no}: too few columns, missing {missing}")

DictReader puts extra values under the key None and missing ones as None values. Neither raises. Duplicate header names keep only the last value, which is silent data loss — check len(set(reader.fieldnames)) != len(reader.fieldnames) and reject the file.

6. Large files and memory

python
import csv, json def stream_rows(path): with open(path, encoding="utf-8-sig", newline="") as f: for row in csv.DictReader(f): yield row with open("/tmp/out.jsonl", "w", encoding="utf-8") as out: for row in stream_rows("/tmp/big.csv"): out.write(json.dumps(row, ensure_ascii=False, allow_nan=False) + "\n")

list(csv.DictReader(f)) on a 2 GB file is a memory error. JSON Lines keeps the conversion streaming and keeps each record independently parseable, which is also what makes a partial output usable after a crash. Raise csv.field_size_limit only when a legitimate field exceeds 128 KB — a large field_size_limit is also how a malformed quote consumes the rest of the file as one field.

Common Compilation/Runtime Mistakes & Version Gotchas

MistakeSymptomCorrect approach
encoding='utf-8' on an Excel exportKeyError: '\ufeffid'utf-8-sig
open(path) without newline=''Records split inside quotesnewline=''
line.split(',')Extra fields on quoted commascsv.reader
int() on an IDLeading zeros lostKeep strings
float for moneyRounding drift, 1e+12Decimal
json.dump with NaNUnexpected token 'N' in the browserallow_nan=False
Assuming ; never appearsWhole file read as one columnDetect the dialect

Version notes: Python 3.12 changed csv error messages to include the offending line, and csv.QUOTE_STRINGS/csv.QUOTE_NOTNULL were added so that None and the string "None" can round-trip distinctly — code that relied on QUOTE_MINIMAL and None continues to work, but a writer using the new constants produces files older parsers read differently. Pandas read_csv applies its own inference (dtype guessing, NaN for NA, inf, NULL), so a pipeline that says "we just use pandas" has the same class of bug with a different surface: pass dtype=str and keep_default_na=False when fidelity matters.

To convert a sample by hand and confirm the expected output, the CSV to JSON converter shows the parsed records before you write code, the JSON formatter validates the result and points at the first invalid token, and the base64 tool is a quick way to check whether a "binary" CSV attachment is really a base64 text body or an encoded Excel file.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.