Supplier price list updates: what to check before you import

The file arrives by email on a Tuesday: a supplier’s new price list, in Excel. Three tabs, merged cells, prices per case for some items and per unit for others, discounts in a separate file. Someone in purchasing spends the afternoon reshaping it into the ERP’s format, runs the import, and the ERP says everything went fine.

Fine for the ERP, yes. It checked that the item codes existed and the columns were filled in. It didn’t check whether a price had doubled, whether a pack size had gone from 10 to 12, or whether an item had quietly disappeared from the file. The real work of supplier price list updates is in the checks before the import.

The import is the easy part

ERPs can import purchase prices. Their checks are about format, not substance. Three examples from their own documentation:

• Sage 200 checks that the file has the mandatory fields and matches items by code. If the supplier’s item code in the file differs from the one on record, the import overwrites it. Nothing in the documentation mentions whether a price is plausible.
• Odoo imports vendor price lists from Excel or CSV. But without the ID column, its documentation warns that imported rows create new entries instead of updating existing ones, and shows an item listed twice after a price drop.
• Business Central creates new price lists as drafts, and they only apply once set to active. It also rejects two lines with the same settings but different prices.

That last point is useful: a draft status is the natural place for a human sign-off before prices take effect.

What goes wrong in supplier price files

Not all of these are visible by eye in a file with thousands of lines.

• A case price read as a unit price, or the other way round, or a pack size that changes between versions.
• The supplier’s selling unit doesn’t match your purchasing unit: per metre on one side, per roll on the other.
• Decimal separators: “1,250” and “1.250” mean different things depending on the country, and numbers stored as text don’t import cleanly.
• Damaged item codes: lost leading zeros, stray spaces, added prefixes, or Excel turning a code into a date.
• Gross prices in one file and a discount grid in another, to be combined before you know the real cost.
• No effective date, or one that has already passed.
• Discontinued items that vanish without notice, and their replacements arriving under new codes.
• Currency not stated, or prices with and without tax mixed together.
• Duplicate lines with different prices.

Nobody is to blame: every supplier has its own file, and spreadsheets check nothing. Spreadsheet risk is a research field of its own: the European Spreadsheet Risks Interest Group (EuSpRIG) has been collecting real-world cases of costly spreadsheet errors for years.

Checks worth automating

Before the import, a checking step can do in seconds what a person would do badly in a day:

1. Structure: expected columns present, numbers readable, encoding correct.
2. Item codes: unknown to the ERP, new, or present in the old price list but missing from the new one.
3. Duplicates within the file.
4. Units and pack sizes, compared with the item record.
5. Change against the previous price, with a threshold per product family. Above it, the line is flagged. A sharp drop deserves a look too: it sometimes hides a unit error.
6. Effective date, present and in the future.
7. Currency.

The result is a report per file: so many lines passed, so many flagged with the reason, so many rejected. The buyer no longer reads the whole file, only the exceptions.

For each supplier, you record once where the item codes, prices, unit and pack size sit in their file. That setting replaces the memory of the one person who “knows everyone’s files”.

What a buyer still has to decide

The checks flag; the buyer decides.

• Accepting or challenging an increase. The same increase can be normal for one supplier and abnormal for another. The buyer compares it with what was negotiated.
• Linking a new item code to the right item record, or a replacement to the item it replaces.
• Deciding the impact on selling prices and margins.
• Switching the price list from draft to active.

Industry formats help, up to a point

Some sectors have common formats. In French building supply, for example, FAB-DIS is a shared exchange file created by manufacturers’ and distributors’ trade bodies. For technical attributes, the ETIM classification gives a precise, uniform list of the main technical characteristics for each product class.

A supplier who sends a clean standard file saves you the reshaping. It doesn’t save you the checks on substance: a price can be perfectly formatted and still wrong. And many suppliers, especially smaller ones, don’t use these formats at all.

When automation isn’t worth it

• You have few suppliers, with one or two updates a year. A checklist and a file template are enough.
• Files change layout every time and volumes are tiny. Setting up each supplier would cost more than typing.
• Your suppliers already send clean standard files. You only need the price-change and date checks, not the whole reshaping step.

A simple receive, check, approve, import flow

The flow has five steps: an inbox for price lists, reshaping according to the supplier’s setting, the checks, the buyer’s review of flagged lines, then importing the approved lines, as a draft where the ERP allows it. Each file received is archived with its check report, so the day someone asks where a price came from, the answer exists.

Start with the two or three suppliers whose prices change most often or cover the most items. Those are the files where errors cost the most.

We’ve written this flow up as a use case: Supplier price lists in Excel, checked before the ERP import. Technical specifications often arrive with the prices; see PDF data sheets turned into a product database. And for the sales side, read how to automate sales order entry without replacing your ERP.

If price list updates take your team whole days, send us a typical file and we’ll tell you which checks would make the biggest difference for you.

Sources

• Sage, Import supplier price lists, Sage 200 documentation.
• Odoo, Import vendor pricelist, version 20.0 documentation.
• Microsoft Learn, Set up prices and discounts, Business Central documentation.
• FAB-DIS, Les créateurs (in French).
• ETIM International, etim-international.com, and ETIM France, Classification (in French).
• EuSpRIG, European Spreadsheet Risks Interest Group and its list of real-world cases.