Automate price lists: what breaks when the supplier changes Excel

Some time ago, I wrote about automating a distributor and mentioned loading price lists as the process with the best return. I was asked for details, so here is the implementation, including the parts that break.

The scenario: the supplier sends a list every two or three weeks. PDF or Excel, different format depending on who exported it that day. Someone opens it and loads hundreds of items manually into the management system.

The startup error: sending the entire file to the model.

The first thing one tries is to pass the PDF to the LLM and ask for the JSON of the 800 rows. It works in the demo with 20 rows and crashes with the real file: it skips middle rows, invents codes that do not exist, and the cost per run increases unnecessarily.

The model does not have to read the 800 rows. It has to read the header.

Supplier file │ ▼ Deterministic table extraction (pdfplumber / openpyxl) │ ▼ LLM on the first ~15 rows → column mapping │ {code: "B", price: "F", bundle: "D"} ▼ Apply that mapping to the 800 rows with common code │ ▼ Matching against the master → validations → batch writing

The LLM is used once per file, not once per row. What varies is the layout, not the data.

Code matching: three passes and a tray

The supplier code almost never matches yours. There are three attempts, in order: Exact match against the supplier code saved in the master. Normalized match: without spaces, without hyphens, without leading zeros. Match by description, and only if the similarity passes a high threshold.

What does not match is not created. It goes to a review tray and someone looks at it.

A flow that creates products only fills the master with duplicates in three months, and that is never cleaned up.

When the human resolves a case from the tray, the equivalence is saved. The tray of the second month is half that of the first.

Validations that stop writing

Deterministic, in code, after the model: Price variation greater than the threshold (I started at 40%) → stops that row. Zero, negative or non-numeric price → stops. Repeated code within the same file → stops both rows. Fewer rows than expected against the last run → stops the entire batch.

That last one is what saves it.

If the supplier sends a partial list with 60 items and your flow assumes it is the complete list, any logic of "what did not come is discontinued" turns off the catalog.

What I learned by trial and error

Decimal separator. 1.234,56 and 1,234.56 depending on who exported.

If you parse it with the local config and the file came from elsewhere, you change prices by a thousand.

VAT. Some suppliers send it with VAT, others without, and the same supplier changes criteria when changing systems.

It is not deduced from the number: it is saved as an attribute of the supplier and validated against the resulting margin.

Price per bundle. The column says "price" and it is per box of 12. If your master carries it per unit, everything enters multiplied by 12.

Scanned PDFs. Some arrive as an image. There, OCR is necessary first, and there, human review of the entire batch is convenient: OCR confuses 0 with 8 and that error passes all validations because it is a plausible number.

Idempotence and going back

Two things that seem unnecessary until the first time you need them: Hash of the file before processing. The same list resent by email is not processed twice.

Batch id in each write, with the previous price saved.

Reverting an entire batch must be a query, not a backup restoration.

What remains

The process goes from half a day to a couple of minutes, plus the time of the review tray, which shrinks by itself.

But what really changes is not the time: it is that the prices of the shelf and those of e-commerce stop being out of sync with the system, because now they are updated in the same movement.

Two rules that I got from this and are valid for any automation that writes in a production system: the model interprets and the code decides, and no flow writes without being able to undo.

I write about automation with AI for Argentine SMEs from Varka.

The non-technical version of this note, for business owners, is on the blog.