ChatGPT, Claude, Copilot or Gemini · Practical walkthrough

Give AI a data dictionary before it combines your files

Column names are clues, not definitions. Before AI joins two exports, tell it that 001 is a code rather than the number one, that amounts are in pence, and which rows count.

Task by Task6 min read + practice

What you'll make

a short data dictionary for the columns that matter, and a joined table whose row count you have checked against the source.

What you'll need

the two files you want to combine, which you’re allowed to share with a work AI chat (ChatGPT, Claude, Copilot or Gemini), and the question the joined table should answer. Practise with the fictional files in the worked example. About fifteen minutes.

Example result.

Field	Meaning	Rule
customer_id	Customer code	Text: keep leading zeros (001 is not 1)
amount_pence	Invoice amount	Whole pence: divide by 100 for pounds
status	Invoice state	Only “open” rows are in scope
invoice_id	One invoice	One row per invoice in the result

1. Ask AI to draft the dictionary as questions

Attach both files and ask:

Before combining these files, list each column and what you think it means, as questions for me to confirm.
For each, say its likely type (text, whole number, date), its unit if it has one, and whether it looks unique. Flag any column whose meaning you are guessing from its name.
Don't combine anything yet.

Then correct the two or three answers that matter most, usually the ID columns, the units and which rows are in scope. That’s your dictionary.

Why this works: a model guessing from column names is how 001 becomes 1 and pence become pounds. Asking for questions gets the guesses out where you can fix them.

What you should see: one line per column, with the guesses marked, ready for you to confirm or correct.

2. Give it the dictionary and ask for the join plan

The grain is what one row of the result represents, such as one invoice. Name it, so a join can’t quietly change it:

Use this data dictionary: [dictionary]. Combine the files at one row per [grain], matching on [key], for [scope] only.
Before the full result, show the row counts for each file, any key that appears more than once, any key with no match, and the expected number of result rows.
Keep IDs as text and amounts in their original unit with a separate converted column. If a key matches more than one row, flag it; don't pick one.

[dictionary] is your confirmed lines from step 1. [grain] is what one result row is, such as “invoice”. [key] is the matching column, such as customer_id. [scope] is the rows that count, such as “open invoices”.

Why this works: counts before results let you spot a doubled or dropped row while it’s still a plan.

What you should see: a short plan with input counts, any duplicate or unmatched keys, and an expected row count.

3. Check the row count and three rows

Compare the result’s row count with the number of in-scope rows in the source. Then check three rows by hand: an ordinary one, one with a leading-zero ID, and any row the plan flagged.

Why this works: a join that adds or loses rows can still produce a reasonable-looking total. The count is the quickest test there is.

What you should see: the expected number of rows, IDs still with their leading zeros, and pounds that are the pence divided by 100.

Check the result

The result has one row per invoice (or whatever your grain is), and the count matches the in-scope source rows.
IDs keep their leading zeros.
Amounts in pounds are the pence divided by 100, and the pence column is still there.
Any duplicate or unmatched key is flagged, not silently resolved.

The usual failure: a duplicate key doubles a row. A customer appears twice in the customer file, so their invoice appears twice in the result and the total goes up. It looks plausible because every row is real. Spot it by comparing row counts.

The result has more rows than the in-scope source because [key] [value] matches more than one row in [file]. Keep one row per [grain], flag that key as a duplicate with both matches listed, and recalculate the total and row count.

[value] is the duplicated key, such as 002, and [file] is the file it appears twice in.

Worked example: the customer who appeared twice

Fictional example. Two exports:

invoices.csv
invoice_id,customer_id,amount_pence,status
INV-101,001,1250,open
INV-102,002,4800,open
INV-103,017,2600,open
INV-104,999,9900,void

customers.csv
customer_id,name
001,Alder Studio
002,Brook & Co
002,Brook and Co Ltd
017,Cedar Lane Bakery

The first join returned four rows and an open total of £134.50.

Four rows was one too many. There are three open invoices. Customer 002 appears twice in customers.csv, so INV-102 appeared twice. The repair prompt kept one row per invoice and flagged 002’s two names for someone to resolve.
The total reconciles after the repair. 1,250 + 4,800 + 2,600 = 8,650 pence, or £86.50. The wrong total was £86.50 plus a second £48.00.
The dictionary did its job elsewhere. IDs stayed 001 and 017 rather than 1 and 17, amounts stayed in pence with a pounds column beside them, and the void INV-104 for 999 was left out of scope.

Task kit for this job: Download (ZIP) · Reusable instructions

Go deeper

Keep raw and converted units side by side

Keep the original pence column and add a clearly named pounds column, rather than overwriting it. The divisor comes from the dictionary, not from how plausible the amounts look. If two files use different units for the same thing, see conflicting units.

Reuse the dictionary, not last month's answers

Save the dictionary and the join rules with the routine, and bring fresh files each time. Check whether a new export has changed a column’s meaning before you reuse it; a familiar filename doesn’t guarantee the same contents. The repeatable questions guide shows how.