Lap 1: Read the CSV Like a Person First
Open the file before you clean it. A model can fix a CSV far more easily when you can describe what is wrong in plain words, and the only way to describe the problem is to look at the rows yourself first. Open the file in a spreadsheet or a plain text editor and read the first few lines, the last few lines, and a few rows from the middle.
Ask yourself simple questions as you look. Does the first row hold the column names? Does every row seem to have the same number of fields? Do any lines end in the middle of a sentence? Are there rows that look twice as long as their neighbours? Write down what you see, even when the honest answer is that you are not sure.
Pay attention to the shape of the file as well as the words. A CSV is only a text file with an agreed separator, and that separator is often a comma, a semicolon, a tab, or a pipe. If the whole file opens as one giant column, the separator is not the one your tool expected. Noting that early saves you from asking a model to fix a problem that is only a setting.
Pit Stop: List the Messes You Can Actually See
Most messy CSVs fail in a small set of ways. Naming those failures turns a vague feeling that the file is wrong into a concrete list of tasks you can hand to a model.
- Broken quoting: a comma inside a name or address closes a field early, so the rest of the line slides into the wrong columns.
- Mixed separators: some rows use commas and others use semicolons, often after a file was merged by hand.
- Mangled headers: column names carry spaces, stray capitals, accents, or blank labels that show up as an empty first cell.
- Placeholder values: missing data appears as blanks, N/A, null, dashes, a lone dot, or the word unknown.
- Duplicate rows: the same record appears twice with tiny differences in spacing or capitalisation.
- Odd types: one date column mixes 2024-03-01, 01/03/2024, and free text like ASAP.
- Hidden characters: non-breaking spaces, smart quotes, or stray line breaks sit inside otherwise clean cells.
Keep the list short and specific, one line per problem. A note like numbers in the price column sometimes carry a currency symbol is useful. A note like the data looks bad is not. This list becomes the brief you give the model, and later it becomes your checklist for the finished file.
First Run: Give the Model Rules, Not Just a File
A model cleans best when you give it rules, not just a file. Write the request the way you would explain the job to a careful junior colleague: say what the file is for, what the clean version must look like, and which choices the model must never make on its own. For example, keep every row, never invent a value, use ISO dates, normalise headers to lowercase with underscores, and leave a missing value blank rather than filling it in.
Add one or two examples inside the prompt. Show one broken row next to the cleaned row you expect. Examples do more for consistency than long descriptions, and they anchor the awkward edge cases that are hard to describe in words.
You can run this kind of work through SwiftRelay, a pay as you go relay that gives you one API host for Claude, GPT, Gemini, DeepSeek, Kimi, GLM, Qwen, Grok and other models. It works with Claude Code, Codex, Gemini CLI and any OpenAI-compatible SDK: Claude Code and Gemini CLI take the bare host https://api.fastapirelay.com, while OpenAI-style SDKs add /v1. Billing is per token, so a small cleaning job costs only the tokens you use. For steady cleaning passes, Claude Sonnet 5.5 and DeepSeek V4 Flash are both sensible picks.
Ask for the output in a fixed shape. Say that you want the cleaned CSV and a separate change log, and say which format each should take. A clear output contract saves you from untangling a reply that mixes prose, code, and half a data file.
Keep Every Change Small Enough to Check
The fastest way to lose trust in a cleaned file is to accept a whole new file with no idea what moved. Ask the model to describe its changes in plain language before it hands back data, so you can read the plan and stop early if something looks wrong.
- Row count before and after, and a reason for any row removed.
- The list of columns touched and the rule applied to each one.
- Values that were filled, and values deliberately left blank.
- Any row the model was unsure about, listed separately.
Work in rounds rather than in one giant pass. A first round can fix quoting and separators, a second round can normalise values, and a third round can sort out data types. Each round produces a file you can open and check, and if a round goes wrong you lose only that round of work.
Never let the model guess at facts. If a postcode is missing, it stays missing and gets flagged. If a date is ambiguous, ask the model to mark it as ambiguous instead of choosing an interpretation. A blank you understand is far better than a confident invention you cannot see.
Spot check a handful of random rows against the source after each round. Pick rows at random, not the ones you already know. This is the cheapest habit in the whole process and the one that catches the mistakes a checklist misses.
Before the Green Light: Check the Clean File
Treat the model output as a draft until your checks pass. The last lap is plain verification work, and it needs no model at all.
- The row count matches the original, unless you agreed to drop duplicates.
- Every row has the same number of fields as the header.
- Headers match the names you agreed on, with no blanks.
- No placeholder text such as N/A, unknown, or a lone dash survives where blanks belong.
- Each column holds one consistent type: dates look like dates, numbers look like numbers.
- A few rows read correctly when checked by eye against the source file.
- The original file is kept untouched as a safe copy.
Run the same checks twice with two different tools where you can: once in a spreadsheet and once with a small script or a command line count. Two independent checks catch far more than one careful check, because each tool fails in different ways.
If you want to try the whole loop on real files, sign up for SwiftRelay and use the small gift credit on new accounts to run your first cleaning pass. After that, billing stays per token, and inviting friends earns you 14% of their top-ups.
Cleaning a messy CSV is mostly careful reading with a fast typist beside you. The model does the repetitive work of rewriting, normalising, and flagging, while you keep the judgement calls and the final check. Do that small amount of thinking at both ends of the process, and the clean file is ready for the green light.