When every supplier sends a different spreadsheet

Ask a supply chain team where their time goes and you will hear about formats. One supplier sends an Excel file with the headings on row 4 under a merged title. Another sends a CSV named .xls. A third sends the same columns every month but renames them without warning. A fourth puts the store code in a column called location and the fifth calls it site_id.

None of this is interesting work. All of it has to be done before anything useful can happen, which is why it usually lands on one person who has become quietly indispensable.

The tooling assumption that breaks here

Most data tooling assumes a stable schema. You define the shape once and data arrives in that shape. That assumption holds beautifully for your own systems and collapses entirely for anything arriving from outside your company, because you do not control the sender and the sender has no incentive to be consistent.

So the work becomes: reshape the incoming thing into the expected shape, by hand, every month, forever.

What we do about the mechanical part

A good chunk of this is genuinely mechanical, and mechanical problems should be solved mechanically:

• What a file is comes from its first bytes, not its extension. A CSV saved as .xlsx loads. A pre-2007 .xls loads. An HTML export pretending to be a spreadsheet is refused with an explanation rather than a stack trace three steps later.
• Merged cells are read as the value they span. A region merged down its rows belongs to each of them.
• A merged title above your headings no longer wins. The heading row is the one with the most distinct text cells, which is a small heuristic that removes a large amount of swearing.
• New files are matched to a dataset by their column headings, not by file name — because file names are a convention nobody follows. If sixty per cent or more of the columns line up, the file continues the dataset it belongs to.

That last one is what makes a supplier renaming their file harmless.

What stays a judgement

The naming, mostly. That location and site_id are the same key is a fact about your business, not about the files. The agent will notice that two columns share values and propose the link; whether that link is correct is yours to confirm, and a link is refused outright if the two columns share no values at all — which catches the cases where a plausible-looking match is nonsense.

Once declared, a filter set on one dataset's column reaches the others through the link, even when they name the column differently. You declare the correspondence once instead of remembering it every month.

The bit that matters commercially

The reason this is worth fixing is not the hours. It is that the reconciliation work is currently load-bearing and held by one person.

Every company with forty suppliers has someone who knows that vendor 17's file has a blank first sheet and vendor 22 reports in cases rather than units. That knowledge is not written down. It is not in the workbook, because a workbook has nowhere to put it. It leaves when they do.

An approved dataset with stated assumptions is a place to put it. That is a less exciting claim than automation, and a more durable one.