What to do when a client sends you data in 4 different formats

The reply to "can you send the sales data?" arrives as four attachments and a link. There's Q2_summary.pdf, a Google Sheet shared "so it stays live," an Excel workbook with six tabs named things like Sheet1 (2), and a CSV that opens as one column of gibberish. The client thinks they've been thorough. What they've actually done is hand you the same numbers four times, each stored in a way that fights you differently, and left you to work out which one is the source of truth. The trap is diving into whichever file you opened first. Do the boring triage instead.

First decide which file is the source, not which is easiest

Before you clean anything, work out where the numbers actually come from. The PDF is almost always a printout of one of the other three, so it's a reference, not a source. The Google Sheet may have live formulas pulling from tabs you can't see. The Excel workbook's six tabs might be six months, or the same month edited six times. Ask the client one question — "which of these does your team consider the real record?" — and you'll save an hour of reconciling files that were never meant to agree. Pick one source; treat the rest as things to check it against.

The example: four files, four different traps

Say the same June revenue figure shows up like this across the four:

Q2_summary.pdf   ->  "June revenue: £84,210"   (a picture of a number)
Google Sheet     ->  =IMPORTRANGE(...) 84,210   (live, could change tomorrow)
workbook.xlsx    ->  June tab: 84,210 ; June(2) tab: 83,900
june.csv         ->  £84,210               (broken encoding)

Four traps, one per format. The PDF number can't be trusted for totals because you can't see the rows behind it — extract the underlying table, don't retype the figure. The Sheet's IMPORTRANGE means today's 84,210 could be tomorrow's 84,600, so export it to a static CSV the moment you agree it's the source, and note the date. The workbook has two June tabs that disagree by £310 — that £310 is a question for the client, not something to average. And the CSV's £ is a classic UTF-8 file opened as the wrong encoding; reopen it with Data → From Text/CSV and set File Origin to 65001: Unicode (UTF-8) and the pound sign comes back.

A calm order of operations

1. Inventory the files — list each one, its format, and what you think it contains. Four files, four lines.
2. Name the single source of truth with the client, in writing, before cleaning.
3. Get everything into the same shape — extract the PDF table, export the Sheet to CSV, split the workbook tabs, fix the CSV encoding — so all four are flat tables you can read.
4. Reconcile the others against the source — one shared total, checked to the penny. Log any file that disagrees and why.
5. Clean and combine the source only, keeping the other three as evidence, not inputs.
6. Write down the decision — "June figures taken from workbook.xlsx June tab; June(2) tab was a superseded draft" — so next quarter you don't relitigate it.

Follow that order and four contradictory attachments become one clean table with a paper trail, instead of a guess you can't defend.

Then fix the intake, not just this file

Getting data in four formats isn't a one-off; it's what happens when nobody told the client how to send it. After you deliver, send them a one-line intake standard: "for future months, export the raw table as a single CSV from your system — no PDFs, no live links." You will not win every time, but even halving the format chaos turns a two-hour reconciliation into a twenty-minute one, and that time was coming straight out of your margin.