Verified against ChatGPT · 2026-08-10
Turn a messy CSV export into a precise cleanup spec before you touch the actual data
Reads a sample of a messy CSV and produces an exact, ordered list of cleanup transformations to apply, so the fix is a repeatable spec you can hand to a script rather than a one-off manual scrub.
The prompt
Ready to copy — highlighted parts are example details you can swap.
Look at the sample rows below from a messy CSV export and write the exact, ordered cleanup spec needed to make it analysis-ready — not the cleaned data itself, the spec for cleaning it, since this needs to be repeatable on every future export from the same source.
SAMPLE ROWS (WITH HEADER)
name,signup_date,revenue,region
JOHN SMITH ,03/04/2026,"$1,204.50",us
jane doe,2026-03-05,N/A,US
John Smith,03/04/2026,1204.50,United States
WHAT THIS DATA WILL FEED INTO
Loading into a BI dashboard that aggregates revenue by region and month
KNOWN QUIRKS FROM THIS SOURCE
This export sometimes duplicates a row if the source system times out and retries the batch job
Go column by column. For each column, identify every distinct problem visible in the sample: inconsistent date formats, inconsistent casing or whitespace, a numeric column stored as text with currency symbols or thousands separators, mixed null representations ("N/A", empty string, "null", "-" all meaning the same thing), duplicate rows or duplicate keys, and encoding artifacts (mangled characters from a bad encoding conversion). For each problem found, write the exact rule to fix it — not "clean up the dates" but "parse as MM/DD/YYYY where day/month are unambiguous; for ambiguous dates like 03/04/2026, flag for manual review rather than guessing the format" — a fix I could hand directly to someone writing a script. Order the rules in the sequence they should actually be applied, since some fixes depend on others running first (deduplication should generally happen after whitespace/casing normalization, or near-duplicates caused by a trailing space won't be caught). Call out anything in the sample that looks like it needs a human decision rather than an automated rule — a row that looks like test data mixed into a production export, or a value that's ambiguous even after normalization.
WHAT NOT TO DO
Do not just say "standardize the date format" without naming the actual target format and how to handle ambiguous cases. Do not silently assume how nulls should be handled downstream (dropped vs. imputed vs. kept as null) — state the assumption and flag it as something to confirm against Loading into a BI dashboard that aggregates revenue by region and month.
OUTPUT FORMAT
1. Column-by-column problem list with the exact fix rule for each.
2. The applied order of operations, numbered.
3. Rows or values needing a human decision rather than an automated rule.
4. Any assumption made about null-handling, flagged for confirmation.Customize
Optional — swap in your own details for the highlighted parts above.
Why this works
Asked to "clean this CSV," a model will often just clean the sample rows shown and hand back tidied data, which solves the wrong problem entirely for anyone dealing with a recurring export — the actual need is a reusable rule set, not a one-time fix, so this prompt explicitly redirects the output away from cleaned data and toward a spec, which forces the model to generalize each observed problem into a rule rather than a patch applied to the specific rows in front of it. Requiring the exact target format and an explicit ambiguous-case rule (rather than "standardize dates") matters because date ambiguity — is 03/04/2026 March 4th or April 3rd — is precisely the kind of judgment call a model will silently resolve one way if not told to flag it, and a script built from a spec that quietly picked a guess will misparse a meaningful fraction of ambiguous dates without anyone noticing until a downstream aggregate looks wrong. Ordering the operations rather than listing them as an unordered set addresses a real dependency in data cleaning that's easy to overlook — deduplication run before whitespace trimming will miss the exact duplicates that trimming would have revealed, since " JOHN SMITH " and "John Smith" won't be recognized as the same key until normalization happens first — and a model asked only for a list of fixes has no reason to surface that ordering constraint unless the prompt asks for a sequence specifically. Flagging null-handling as an assumption to confirm rather than silently picking one (drop vs. impute vs. keep) matters because whether "N/A" revenue should become $0, a blank, or an excluded row changes the actual aggregate numbers in the downstream dashboard, making it a business decision disguised as a technical cleanup step.
What you get back
Column: signup_date — Problem: two formats present (MM/DD/YYYY and YYYY-MM-DD). Fix: parse both to ISO YYYY-MM-DD; where a date like 03/04/2026 is ambiguous (both day and month ≤12), flag row for manual review rather than guessing. Column: revenue — Problem: currency symbol, thousands separator, and "N/A" as null all present. Fix: strip "$" and ",", cast to decimal; treat "N/A" as null, not zero — confirm with downstream dashboard owner before deciding whether nulls should be excluded from the revenue aggregate or shown as $0. Order: 1) trim whitespace/normalize case, 2) parse dates, 3) parse currency, 4) deduplicate on normalized name+date+region.
Verified against
ChatGPT GPT-5.1 · 2026-08-10
Changelog
- 2026-08-10 — Initial publish, verified against ChatGPT GPT-5.1.
Need this built into your business?
If a prompt isn't enough — what Scult builds, built and maintained for you — that's Scult's day job.
EXPLORE WHAT SCULT BUILDS
