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.

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:
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)# 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'}# 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 twoThe \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
| Decision | Naive choice | Safe choice | Failure it prevents |
|---|---|---|---|
| Encoding | utf-8 | utf-8-sig | \ufeffid key, KeyError |
| Open mode | open(path) | open(path, newline='') | Split records on quoted newlines |
| Reader | split(',') | csv.reader / DictReader | Broken quoted commas and quotes |
| Numbers | int(v) / float(v) | int(v) after validation | Lost leading zeros, 1e+12 |
| Booleans | v == 'true' | Explicit set of accepted values | TRUE/1/yes silently false |
| Empty | None | '' or None by policy | Mixed null spellings in JSON |
| JSON dump | default | allow_nan=False | NaN in JSON (invalid) |
| Dialect parameter | When | Example |
|---|---|---|
delimiter | Non-comma CSV | ; from European Excel |
quotechar | Single-quote quoting | ' |
doublequote=False + escapechar | Backslash-escaped quotes | \" |
skipinitialspace | , value | Leading spaces in fields |
field_size_limit | Huge text cells | Raise above 131072 |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. Sniffing the dialect without guessing wrong
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
# 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 vJSON 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
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
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
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
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
| Mistake | Symptom | Correct approach |
|---|---|---|
encoding='utf-8' on an Excel export | KeyError: '\ufeffid' | utf-8-sig |
open(path) without newline='' | Records split inside quotes | newline='' |
line.split(',') | Extra fields on quoted commas | csv.reader |
int() on an ID | Leading zeros lost | Keep strings |
float for money | Rounding drift, 1e+12 | Decimal |
json.dump with NaN | Unexpected token 'N' in the browser | allow_nan=False |
Assuming ; never appears | Whole file read as one column | Detect 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 toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.