How to Clean a CSV With AI Without Uploading It to ChatGPT

The way to clean a CSV with AI without uploading it is to split the job in two. The assistant never sees the file; it sees the column headers and a handful of made-up rows in the same shape, and it writes the transformation: a Python script, a Power Query step or a formula. You run that transformation on the real file on your own machine, and you check the row counts. The data stays with you, the rules are written down, and next month you re-run the same script instead of asking again.

This is a how-to. We ran the workflow end to end on October 10, 2026 with a fresh session of Claude (Sonnet 5.5 via the Claude Agent SDK, no tools enabled), and everything below (the prompt, the returned script, the output) is what actually happened, including one surprise. For the rules on when you are allowed to put client data into an AI tool at all, read Is AI safe for financial data?; this post is about the mechanics.

Why not just upload the file?

Step 1: build a redacted sample

Take the real header row. Then write five rows by hand that have the same *shapes* as the real data but none of the real values. The model needs the shapes (a slash date, an ISO date, an amount in parentheses, an amount with a dollar sign and a thousands comma, a description with doubled spaces) and nothing else.

Here is the sample we used. It is five fake rows of a US bank export:

Date,Description,Amount
03/04/2026,"POS PURCHASE  AMZN Mktp US*2K4L7  SEATTLE WA",-42.17
3/5/2026,"ACH CREDIT STRIPE TRANSFER ST-X1Y2Z3",803.00
2026-03-05,"CHECK 1042",-1250.00
03/07/2026,"ONLINE PMT  COMCAST   CABLE",(129.99)
03/09/2026,"DEPOSIT MOBILE",$2,500.00

Step 2: ask for the transformation, with rules

The prompt matters more than the model. Four rules make the output safe to run: read everything as text, add new columns instead of overwriting, flag ambiguous values instead of guessing, and print row counts. This is the exact prompt we sent (the five rows above were pasted where indicated):

Here are the headers and 5 made-up sample rows from a bank export (real file has about 400 rows, same shape):

[sample pasted here]

Write a Python pandas script I can run locally on the full file (bank.csv) that:
1. Reads every column as text.
2. Adds Date ISO (YYYY-MM-DD). Slash dates are US month/day/year. Flag rows where the day and month are both 12 or under with AMBIGUOUS in a Flags column, but still convert them as US.
3. Adds Amount Clean as a number: strip $ and commas, treat parentheses as negative. Anything that cannot be parsed stays blank and gets UNPARSED in Flags.
4. Adds Description Clean: trim, collapse repeated spaces, and uppercase.
5. Does not modify or drop the original columns or any rows.
6. Prints the row count before and after, the number of flagged rows, and the sum of Amount Clean.
7. Writes bank_clean.csv.

Step 3: what came back

The first thing the assistant did was point out a bug in our sample: the last row's amount, $2,500.00, contains an unquoted comma, so a plain CSV reader would see four fields instead of three and fail. That is a real problem in real bank exports, and we had not noticed it. The script it wrote reads the file with Python's csv module and re-joins the overflow into the Amount column, reporting how many rows it repaired. Here is the script, exactly as returned apart from removing two docstrings for length:

#!/usr/bin/env python3
import csv
import re
import sys
from datetime import date
from decimal import Decimal, InvalidOperation

import pandas as pd

IN_PATH = sys.argv[1] if len(sys.argv) > 1 else "bank.csv"
OUT_PATH = "bank_clean.csv"


def read_rows(path):
    for enc in ("utf-8-sig", "cp1252"):
        try:
            with open(path, newline="", encoding=enc) as f:
                rows = list(csv.reader(f))
            break
        except UnicodeDecodeError:
            continue
    else:
        sys.exit("Could not decode file as UTF-8 or cp1252")

    header, body = rows[0], rows[1:]
    n = len(header)
    repaired = 0
    fixed = []
    for r in body:
        if len(r) > n:
            r = r[: n - 1] + [",".join(r[n - 1:])]
            repaired += 1
        elif len(r) < n:
            r = r + [""] * (n - len(r))
        fixed.append(r)
    return header, fixed, repaired


SLASH = re.compile(r"^(\d{1,2})/(\d{1,2})/(\d{4})$")
ISO = re.compile(r"^(\d{4})-(\d{2})-(\d{2})$")


def parse_date(s):
    s = s.strip()
    m = SLASH.match(s)
    try:
        if m:
            month, day, year = int(m[1]), int(m[2]), int(m[3])
            iso = date(year, month, day).isoformat()
            return iso, ("AMBIGUOUS" if month <= 12 and day <= 12 else "")
        m = ISO.match(s)
        if m:
            return date(int(m[1]), int(m[2]), int(m[3])).isoformat(), ""
    except ValueError:
        pass
    return "", "DATE_UNPARSED"


NUM = re.compile(r"^[+-]?(\d+\.?\d*|\.\d+)$")


def parse_amount(s):
    t = s.strip()
    if not t:
        return None
    neg = t.startswith("(") and t.endswith(")")
    if neg:
        t = t[1:-1]
    t = t.replace("$", "").replace(",", "").replace(" ", "")
    if not NUM.match(t):
        return None
    try:
        val = Decimal(t)
    except InvalidOperation:
        return None
    return -abs(val) if neg else val


def main():
    header, body, repaired = read_rows(IN_PATH)
    df = pd.DataFrame(body, columns=header, dtype=str)
    rows_before = len(df)
    print(f"Rows before: {rows_before}")
    if repaired:
        print(f"Note: repaired {repaired} row(s) with unquoted commas "
              f"(joined extra fields into the last column)")

    iso_dates, flags, amounts, descs = [], [], [], []
    for d, desc, amt in zip(df["Date"], df["Description"], df["Amount"]):
        row_flags = []
        iso, dflag = parse_date(d)
        iso_dates.append(iso)
        if dflag:
            row_flags.append(dflag)
        val = parse_amount(amt)
        amounts.append(val)
        if val is None:
            row_flags.append("UNPARSED")
        descs.append(" ".join(desc.split()).upper())
        flags.append(";".join(row_flags))

    out = df.copy()
    out["Date ISO"] = iso_dates
    out["Amount Clean"] = [float(v) if v is not None else None for v in amounts]
    out["Description Clean"] = descs
    out["Flags"] = flags
    out.to_csv(OUT_PATH, index=False)

    rows_after = len(out)
    flagged = sum(1 for f in flags if f)
    total = sum((v for v in amounts if v is not None), Decimal("0"))
    print(f"Rows after: {rows_after}")
    if rows_after != rows_before:
        print("WARNING: row count changed!")
    print(f"Flagged rows: {flagged}")
    for name in ("AMBIGUOUS", "UNPARSED", "DATE_UNPARSED"):
        c = sum(1 for f in flags if name in f.split(";"))
        if c:
            print(f"  {name}: {c}")
    print(f"Sum of Amount Clean: {total:,.2f}")
    print(f"Wrote {OUT_PATH}")


if __name__ == "__main__":
    main()

Step 4: run it locally and check the numbers

We ran the script on the five-row sample with Python 3 and pandas. This is the console output, unedited:

Rows before: 5
Note: repaired 1 row(s) with unquoted commas (joined extra fields into the last column)
Rows after: 5
Flagged rows: 4
  AMBIGUOUS: 4
Sum of Amount Clean: 1,880.84
Wrote bank_clean.csv

And the output file:

Date,Description,Amount,Date ISO,Amount Clean,Description Clean,Flags
03/04/2026,POS PURCHASE  AMZN Mktp US*2K4L7  SEATTLE WA,-42.17,2026-03-04,-42.17,POS PURCHASE AMZN MKTP US*2K4L7 SEATTLE WA,AMBIGUOUS
3/5/2026,ACH CREDIT STRIPE TRANSFER ST-X1Y2Z3,803.00,2026-03-05,803.0,ACH CREDIT STRIPE TRANSFER ST-X1Y2Z3,AMBIGUOUS
2026-03-05,CHECK 1042,-1250.00,2026-03-05,-1250.0,CHECK 1042,
03/07/2026,ONLINE PMT  COMCAST   CABLE,(129.99),2026-03-07,-129.99,ONLINE PMT COMCAST CABLE,AMBIGUOUS
03/09/2026,DEPOSIT MOBILE,"$2,500.00",2026-03-09,2500.0,DEPOSIT MOBILE,AMBIGUOUS

Alternatives if you cannot run Python

Pitfalls

If the file is a bank export headed for QuickBooks or Xero, the bank statement CSV cleaner and QuickBooks CSV import cleanup pages show the deterministic version of the same steps, including the 3-column and 4-column layouts QuickBooks expects.