Working with CSV & JSON

Read and write CSV with DictReader/DictWriter, JSON load/dump, invalid input, custom types and JSON Lines.

Two text formats dominate everyday data exchange. CSV (comma-separated values) is how spreadsheets, bank statements and database exports travel. JSON (JavaScript Object Notation) is the language of web APIs and configuration files. Python's standard library handles both with the csv and json modules — no installs needed.

CSV basics#

A CSV file is a table in plain text: one row per line, fields separated by commas, usually with a header row. Fields that contain commas or quotes are wrapped in double quotes:

employees.csv

name,department,salary,start_date
Ada Lovelace,Engineering,145000,2021-03-15
Grace Hopper,Engineering,160000,2019-07-01
"Smith, John",Sales,72000,2023-01-09
Linus T,Support,58000,2024-11-20

You could split each line on commas — but "Smith, John" shows why you shouldn't. The csv module handles quoting, escaping and line endings correctly.

Reading CSV with `DictReader`#

csv.DictReader uses the header row as keys and gives you each row as a dict:

Python

import csv

with open("employees.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    print(reader.fieldnames)
    for row in reader:
        print(f"{row['name']:<14} {row['department']:<12} {row['salary']}")

Output

['name', 'department', 'salary', 'start_date']
Ada Lovelace   Engineering  145000
Grace Hopper   Engineering  160000
Smith, John    Sales        72000
Linus T        Support      58000

Two important details:

Open CSV files with newline="", as the csv docs require. It prevents blank lines between rows on Windows and broken multi-line fields.
Every value is a string. CSV has no types, so convert numbers and dates yourself.

Python

import csv
from collections import defaultdict
from datetime import date

totals = defaultdict(int)
newest = None

with open("employees.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        salary = int(row["salary"])                     # str -> int
        started = date.fromisoformat(row["start_date"]) # str -> date
        totals[row["department"]] += salary
        if newest is None or started > newest[1]:
            newest = (row["name"], started)

for dept, total in sorted(totals.items()):
    print(f"{dept:<12}₹{total:>9,}")
print("Most recent hire:", newest[0], newest[1].strftime("%b %Y"))

Output

Engineering ₹  305,000
Sales       ₹   72,000
Support     ₹   58,000
Most recent hire: Linus T Nov 2024

The plain csv.reader yields each row as a list of strings instead — useful for files without headers:

Python

import csv

with open("employees.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)          # pull off the header row manually
    first = next(reader)
print(header[0], "->", first[0])

Output

name -> Ada Lovelace

Writing CSV#

csv.DictWriter writes dicts; it quotes fields automatically when needed:

Python

import csv

products = [
    {"sku": "KB-01", "name": "Keyboard", "price": 2499},
    {"sku": "MS-02", "name": 'Mouse, "silent" edition', "price": 799},
]

with open("products.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["sku", "name", "price"])
    writer.writeheader()
    writer.writerows(products)

with open("products.csv", encoding="utf-8") as f:
    print(f.read(), end="")

Output

sku,name,price
KB-01,Keyboard,2499
MS-02,"Mouse, ""silent"" edition",799

Notice how the comma and the inner quotes were escaped for you. Use csv.writer with writerow([...]) for lists.

Dialects and Excel

Some files use semicolons or tabs. Pass delimiter=";" or delimiter="\t" to the reader/writer. For CSVs that Excel should open with non-English characters intact, write with encoding="utf-8-sig" (it adds a byte-order mark Excel looks for). For large or messy spreadsheets, pandas (pd.read_csv) is often more convenient — see the data analysis lesson.

JSON basics#

JSON looks almost exactly like Python literals:

JSON	Python
object `{"a": 1}`	`dict`
array `[1, 2]`	`list`
string `"hi"`	`str`
number `42`, `3.14`	`int`, `float`
`true` / `false`	`True` / `False`
`null`	`None`

The four functions to remember:

json.dumps(obj) → JSON string; json.loads(text) → Python object.
json.dump(obj, file) → write to a file; json.load(file) → read from a file.

Python

import json

user = {"id": 1, "name": "Ada", "admin": True, "tags": ["math", "code"], "manager": None}

text = json.dumps(user)
print(text)
print(type(text))

back = json.loads(text)
print(back == user, back["tags"][0])

print(json.dumps(user, indent=2, sort_keys=True))

Output

{"id": 1, "name": "Ada", "admin": true, "tags": ["math", "code"], "manager": null}
<class 'str'>
True math
{
  "admin": true,
  "id": 1,
  "manager": null,
  "name": "Ada",
  "tags": [
    "math",
    "code"
  ]
}

Reading and writing JSON files#

Python

import json
from pathlib import Path

settings = {"theme": "dark", "font_size": 14, "recent": ["notes.md", "todo.txt"], "city": "मुंबई"}

with open("settings.json", "w", encoding="utf-8") as f:
    json.dump(settings, f, indent=2, ensure_ascii=False)

with open("settings.json", encoding="utf-8") as f:
    loaded = json.load(f)

print(loaded["recent"][-1], loaded["city"])
print(Path("settings.json").read_text(encoding="utf-8").splitlines()[1])

Output

todo.txt मुंबई
  "theme": "dark",

ensure_ascii=False writes non-English characters as-is instead of \u0941-style escapes, which keeps files human-readable.

Handling invalid JSON#

Real-world input is messy. json.loads raises json.JSONDecodeError (a subclass of ValueError) with the exact position of the problem:

Python

import json

bad = '{"name": "Ada", "age": 36,}'      # trailing comma isn't valid JSON
try:
    json.loads(bad)
except json.JSONDecodeError as e:
    print(f"Invalid JSON: {e.msg} at line {e.lineno}, column {e.colno}")

Output

Invalid JSON: Illegal trailing comma before end of object at line 1, column 26

Common JSON gotchas: no trailing commas, no comments, strings must use double quotes, and keys must be strings (json.dumps({1: "a"}) turns the key into "1").

Serialising custom types#

json only understands the basic types. Dates, Decimal, sets and your own classes need converting:

Python

import json
from dataclasses import dataclass, asdict
from datetime import datetime, timezone
from decimal import Decimal


@dataclass
class Payment:
    id: int
    amount: Decimal
    paid_at: datetime


p = Payment(17, Decimal("499.00"), datetime(2026, 9, 30, 16, 45, tzinfo=timezone.utc))

try:
    json.dumps(asdict(p))
except TypeError as e:
    print("TypeError:", e)


def to_json(obj):
    if isinstance(obj, datetime):
        return obj.isoformat()
    if isinstance(obj, Decimal):
        return str(obj)                 # keep exact value as a string
    raise TypeError(f"can't serialise {type(obj).__name__}")


print(json.dumps(asdict(p), default=to_json))

Output

TypeError: Object of type Decimal is not JSON serializable
{"id": 17, "amount": "499.00", "paid_at": "2026-09-30T16:45:00+00:00"}

The default function is called for any object json doesn't know how to handle. When loading, convert back explicitly (e.g. datetime.fromisoformat(...)), or use a validation library such as Pydantic.

Worked example: CSV → JSON report#

A typical task: read a CSV export, aggregate it, and produce JSON for a dashboard or API.

Python

import csv
import json
from collections import defaultdict

with open("employees.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.DictReader(f))

by_dept = defaultdict(list)
for row in rows:
    by_dept[row["department"]].append(int(row["salary"]))

report = {
    "employees": len(rows),
    "departments": [
        {"name": dept, "headcount": len(s), "avg_salary": round(sum(s) / len(s))}
        for dept, s in sorted(by_dept.items())
    ],
}

with open("report.json", "w", encoding="utf-8") as f:
    json.dump(report, f, indent=2)

print(json.dumps(report["departments"][0]))
print(report["employees"], "employees exported")

Output

{"name": "Engineering", "headcount": 2, "avg_salary": 152500}
4 employees exported

JSON Lines#

For logs and big datasets you'll often see JSON Lines (.jsonl): one JSON object per line. It can be processed line by line without loading everything:

Python

import json

events = [{"event": "login", "user": 1}, {"event": "purchase", "user": 1, "amount": 499}]
with open("events.jsonl", "w", encoding="utf-8") as f:
    for e in events:
        f.write(json.dumps(e) + "\n")

with open("events.jsonl", encoding="utf-8") as f:
    total = sum(json.loads(line).get("amount", 0) for line in f)
print(total)

Output

Common mistakes#

Splitting CSV lines on commas manually — quoted fields break it. Use csv.
Forgetting that CSV values are strings — convert before doing maths.
Missing newline="" when opening CSV files.
Confusing load/loads and dump/dumps.
Using eval() to parse JSON — never; it executes code. Use json.loads.
Storing money as JSON floats — use strings or integer paise.

What's next#

Our functions and dataclasses have been sprinkled with annotations like list[str] and -> float. Next lesson: type hints — how to write them and how tools use them to catch bugs before you run your code.

CSV basics#

Reading CSV with DictReader#

Writing CSV#

Dialects and Excel

JSON basics#

Reading and writing JSON files#

Handling invalid JSON#

Serialising custom types#

Worked example: CSV → JSON report#

JSON Lines#

Common mistakes#

What's next#

Quick quiz

Reading CSV with `DictReader`#