Data cleaning prompt for messy records

Paste this into a chat window and replace each {{variable}}, or open it in the prompt builder to fill them in a form. Sections are marked with XML tags, which every major model reads well.

You clean tabular data carefully. Every change you make is traceable, and you never silently drop or invent values.

<context>
The records, as {{input_format}}:
<records>
{{records}}
</records>
</context>

<constraints>
Cleaning rules:
{{rules}}

Always:
- Return exactly one output row per input row, in the same order, with the same ID.
- If a value can’t be cleaned with confidence, leave it unchanged and add the row ID and the reason to the issues list.
- Don’t fill missing values unless a rule says how.
- Don’t change fields that no rule mentions.
</constraints>

<output_format>
Two sections: the cleaned records in the same format as the input, in a code block, then a list headed “Issues” with one line per problem (row ID, field, reason), or “None”.
</output_format>

<task>
Clean these records according to the rules below and return them in the same order.
</task>

You clean tabular data carefully. Every change you make is traceable, and you never silently drop or invent values.

<constraints>
Cleaning rules:
{{rules}}

Always:
- Return exactly one output row per input row, in the same order, with the same ID.
- If a value can’t be cleaned with confidence, leave it unchanged and add the row ID and the reason to the issues list.
- Don’t fill missing values unless a rule says how.
- Don’t change fields that no rule mentions.
</constraints>

<output_format>
Two sections: the cleaned records in the same format as the input, in a code block, then a list headed “Issues” with one line per problem (row ID, field, reason), or “None”.
</output_format>

<context>
The records, as {{input_format}}:
<records>
{{records}}
</records>
</context>

<task>
Clean these records according to the rules below and return them in the same order.
</task>

{{input_format}}

CSV with a header row, JSON Lines, or a JSON array.

{{records}}

The rows to clean, including an ID column. Batches of a few hundred rows are easier to check than thousands.

{{rules}}

Your normalisation rules as a list, such as “country: ISO 3166-1 alpha-2 code”, “phone: E.164 format”, “name: trim spaces, keep the original capitalisation”.

{{input_format}}

CSV with a header row

{{records}}

id,name,country,signup_date
101, Amina Yusuf ,United Kingdom,03/10/2026
102,Leo Martins,Brasil,2026-10-04
103,K. Osei,,4 Oct 2026
104,Mei Tan,UK,10/03/2026

{{rules}}

- name: trim surrounding spaces only
- country: ISO 3166-1 alpha-2 code
- signup_date: YYYY-MM-DD; the source system writes DD/MM/YYYY

What to look for. Row 103 has no country, and the rules don’t say how to fill it, so it should stay empty and appear under Issues. The DD/MM/YYYY note matters: without it, 03/10/2026 is ambiguous and could become 10 March instead of 3 October.

You clean tabular data carefully. Every change you make is traceable, and you never silently drop or invent values.

<context>
The records, as CSV with a header row:
<records>
id,name,country,signup_date
101, Amina Yusuf ,United Kingdom,03/10/2026
102,Leo Martins,Brasil,2026-10-04
103,K. Osei,,4 Oct 2026
104,Mei Tan,UK,10/03/2026
</records>
</context>

<constraints>
Cleaning rules:
- name: trim surrounding spaces only
- country: ISO 3166-1 alpha-2 code
- signup_date: YYYY-MM-DD; the source system writes DD/MM/YYYY

Always:
- Return exactly one output row per input row, in the same order, with the same ID.
- If a value can’t be cleaned with confidence, leave it unchanged and add the row ID and the reason to the issues list.
- Don’t fill missing values unless a rule says how.
- Don’t change fields that no rule mentions.
</constraints>

<output_format>
Two sections: the cleaned records in the same format as the input, in a code block, then a list headed “Issues” with one line per problem (row ID, field, reason), or “None”.
</output_format>

<task>
Clean these records according to the rules below and return them in the same order.
</task>

• All models

  Diff the input and output files. With one row per row and unchanged IDs, any unexpected change stands out immediately.

• Gemini

  Google’s guide recommends few-shot examples with consistent formatting. Add two or three before-and-after rows that show your trickiest rule, in exactly the input format.

• OpenAI GPT

  For large batches, the Batch API suits this kind of offline work, and the batch API calculator shows what it saves for your model.

• Extract structured JSONPull fields from emails, documents or pages into a fixed JSON shape, with null instead of guesses.
• Generate test dataRealistic fixtures that match your schema, with deliberate edge cases and clearly fake personal data.
• Regex from examplesBuild a regex for your flavour from strings that should and shouldn’t match, with an explanation and tests.

Edited the template? Compare your version with this one in the prompt diff, and count it for your model with the token counter. In the glossary: batch api. Browse all 29 prompt templates.