04 — CSV ingestion across banks

Duration: 15 min Prerequisites: chapter 03 (env installed), Python + pandas.

The problem

Pick 5 different banks and you’ll get 5 different CSV dialects:

Bank	Separator	Encoding	Header	Amount
Demo file (this project)	;	UTF-8	Date;Card Number;Description;Category;Debit;Credit	2 columns
Visa Royal Bank	,	UTF-8 BOM	Transaction Date,Description,Amount	1 signed column
Capital One	,	ASCII	Posted Date,Card,Description,Category,Debit,Credit	2 columns
Chase	,	UTF-8	Transaction Date,Post Date,Description,Category,Type,Amount	1 signed column

Our ingestion layer must swallow all of that and produce one output format:

DataFrame with columns: date, card, description, category, debit, credit

The plan in 3 steps

# 1. Detect the separator (the one that splits the header best)
delim = ";" if first_line.count(";") > first_line.count(",") else ","

# 2. Read with BOM-tolerant UTF-8
text = path.read_text(encoding="utf-8-sig")

# 3. Map headers to canonical names
HEADER_ALIASES = {
    "transaction date": "date",
    "post date":        "date",
    "card number":      "card",
    "merchant":         "description",
    "category":         "category",
    "debit":            "debit",
    "credit":           "credit",
    "amount":           "amount",
    # ... ~30 aliases
}

The code in csv-llm-shared/ingest.py

from io import StringIO
from pathlib import Path
import csv
import pandas as pd

HEADER_ALIASES = {
    # date
    "date": "date",
    "transaction date": "date",
    "posted date": "date",
    "post date": "date",
    # card
    "card number": "card",
    "card": "card",
    # description
    "description": "description",
    "merchant": "description",
    "merchant name": "description",
    # category
    "category": "category",
    # amounts
    "debit": "debit",
    "credit": "credit",
    "amount": "amount",
}


def _detect_delimiter(sample: str) -> str:
    return ";" if sample.count(";") > sample.count(",") else ","


def _normalize_header(h: str) -> str:
    return HEADER_ALIASES.get(h.strip().lower(), h.strip().lower())


def _parse_amount(value) -> float:
    if value is None:
        return 0.0
    s = str(value).strip()
    if not s:
        return 0.0
    s = s.replace(",", ".").replace(" ", "").replace("\xa0", "")
    try:
        return float(s)
    except ValueError:
        return 0.0


def read_csv(source) -> pd.DataFrame:
    """Read a CSV (path or file-like) and return a standardized DataFrame."""
    if isinstance(source, (str, Path)):
        text = Path(source).read_text(encoding="utf-8-sig")
    else:
        data = source.read()
        if isinstance(data, bytes):
            data = data.decode("utf-8-sig")
        text = data

    delim = _detect_delimiter(text.splitlines()[0])
    rows = list(csv.reader(StringIO(text), delimiter=delim, quotechar='"'))
    if not rows:
        return pd.DataFrame(
            columns=["date", "card", "description", "category", "debit", "credit"]
        )

    raw_header = rows[0]
    header = [_normalize_header(h) for h in raw_header]
    df = pd.DataFrame(rows[1:], columns=header)

    for col in ("date", "card", "description", "category"):
        if col not in df.columns:
            df[col] = ""

    if "debit" not in df.columns and "credit" not in df.columns and "amount" in df.columns:
        df["amount"] = df["amount"].apply(_parse_amount)
        df["debit"] = df["amount"].clip(lower=0)
        df["credit"] = (-df["amount"]).clip(lower=0)
    else:
        df["debit"] = df.get("debit", 0).apply(_parse_amount)
        df["credit"] = df.get("credit", 0).apply(_parse_amount)

    return df[["date", "card", "description", "category", "debit", "credit"]]

The utf-8-sig trick

utf-8-sig is the same as utf-8 except it ignores the BOM (\ufeff) if present. Excel inserts a BOM when exporting UTF-8; without -sig your first column is named \ufeffDate instead of Date. Classic bug, free fix.

_parse_amount is fearless

Input	Output
"12.34"	12.34
"12,34"	12.34 (FR style)
"1 234,56"	1234.56 (FR with space)
"1\u00a0234,56"	1234.56 (non-breaking space)
""	0.0
"abc"	0.0
0 (int)	0.0

Test on the demo CSV

import sys
sys.path.insert(0, "csv-llm-shared")
import ingest

df = ingest.read_csv("csv-llm-ollama/data/data1-anonymized.csv")
print(len(df))                     # 734
print(df.columns.tolist())
# ['date', 'card', 'description', 'category', 'debit', 'credit']
print(df.head(2))

Real output:

734
['date', 'card', 'description', 'category', 'debit', 'credit']
         date              card           description   category  debit  credit
0  2027-12-27  ************4382      Late Payment Fee       Fees  24.31     0.0
1  2027-12-26  ************4382        Postbox Rental    Housing 489.53     0.0

Read multiple CSVs at once

df = ingest.read_many(
    ["my-transactions-may.csv", "my-transactions-june.csv"],
    profile="visa-personal",
)

read_many concatenates everything and adds a profile column (used in chapter 05 when inserting into SQLite).

Takeaways

• A bank CSV comes in many shapes: separator, encoding, single or two-column amount.
• read_csv normalizes through a HEADER_ALIASES dict and a simple _detect_delimiter.
• utf-8-sig solves the Excel BOM problem.
• Output is always a DataFrame with date, card, description, category, debit, credit.

Next: Normalization and SQLite →