JSON and CSV Handling for AI Work in Python

What is it?

AI pipelines spend as much time on files and tables as on prompts. You load examples, documents metadata, evaluation scores or model outputs. JSON is the usual interchange with APIs (JSON Fundamentals for API Work); CSV is common for spreadsheets and labelled eval sets. Python's standard library can do both; larger tables may justify a data library (Python AI Libraries: What to Use When).

Why it matters

Models and embedding APIs are picky about types and encoding. A missing field, a number stored as a string, or a broken UTF-8 file produces confusing failures far from the model. Validating early saves money and debugging time (How to Validate JSON).

How to do it

1. Decide the record shape (required keys and types) before writing loaders.
2. Read with encoding utf-8 (and handle BOM if files come from spreadsheets).
3. Validate: required fields present, types correct, lists not accidentally strings.
4. Normalise text (strip, consistent newlines) before embedding or prompting.
5. Send only the fields the model needs — drop internal ids from the prompt if unused.
6. Write outputs as JSON Lines or CSV with a header row so eval scripts can reload them.
7. Keep raw and processed data separate so you can re-run transforms.

Example

import csv, json
from pathlib import Path

def load_json_records(path):
    data = json.loads(Path(path).read_text(encoding="utf-8"))
    if not isinstance(data, list):
        raise ValueError("expected a JSON array of objects")
    for i, row in enumerate(data):
        if not isinstance(row, dict) or "question" not in row or "answer" not in row:
            raise ValueError(f"row {i} missing question/answer")
    return data

def load_csv_records(path):
    with open(path, newline="", encoding="utf-8") as f:
        rows = list(csv.DictReader(f))
    for i, row in enumerate(rows):
        if not row.get("id") or not row.get("text"):
            raise ValueError(f"csv row {i} needs id and text")
    return rows

def save_jsonl(path, rows):
    with open(path, "w", encoding="utf-8") as f:
        for row in rows:
            f.write(json.dumps(row, ensure_ascii=False) + "\n")

Use these records as the inputs to RAG indexing or evaluation loops.

When to use it

Use structured file handling whenever you batch-embed documents, build golden question sets, or store model outputs for comparison. Prefer databases (Databases for AI Applications) when many writers need concurrency and transactions.

Practical notes

Keep a clear directory layout: 'raw/' for untouched exports, 'clean/' for validated records, and 'runs/' for timestamped model outputs. When a third-party CSV uses different column names, map them in one place rather than sprinkling renames through the embedding script. For evaluation, prefer JSONL so each line is one case you can stream. If a model returns almost-JSON, repair it in a dedicated function and log failures instead of silently dropping rows. Document the expected schema in a short README next to the data folder so the next person (or future you) knows which fields are required before calling an expensive embedding endpoint.

Common mistakes

• Assuming CSV numbers are ints — they arrive as strings until you cast.
• Nested JSON dumped into a single CSV cell without an escape plan.
• Writing model output as invalid JSON because you concatenated strings (How to Validate JSON).
• Committing files that contain personal data (AI Privacy and Security: What to Know Before You Paste Data).

Related concepts