bar chart
A made-up column of whole numbers: which field it leans toward
Illustrative ranking, not real scores. Price leans hardest on the raw values, but case size picks first, so it gets the column.
A whole number also passes the money check.
Wins because it picks before price.
Looks the same as a count.
Looks the same as a count.
Only column headings were read by the supplier import. If a supplier gave the cost column a name it didn't know, that column was ignored.
The names it knew were copied into the server code, once per kind of import. Nobody could tell it which column was the price. It guessed, and you saw the guess in the result.
I fixed it in three steps.
A shared field list and a review screen
First I made one list of fields for each import type: a label, a short description, the names it goes by, and whether it's required. The screen and the server both use it, so they can't drift apart.
When you pick a file, the screen suggests which column feeds which field. It shows a sample value next to each field and previews the first few rows. You can change anything, and a column can only feed one field. You can't import until there's a way to identify each product, plus a quantity for stock counts. The server uses your choice first and only falls back to known names when that cell is blank.
Looking at the values
Next, the importer reads a couple dozen filled cells from each column and sorts them by shape: number, money, yes or no, code, readable text. Each field has its own taste. Names like varied text, prices like money, stock fields like numbers. That score is added to the heading score, and fields take turns picking columns.
My test file had a few made-up rows and nonsense headings, and it still found the names, costs and brand.
Where it has to guess
Then I added case size, and hit the wall. A column of small whole numbers could be a case size, a stock count, a target or a reorder point. The values can't tell you which. A whole number even looks a bit like a price. Case size wins only because it picks before the others.
Each field in turn takes the best column not yet claimed.
That's a rule I chose, not something the data proved, so the preview matters.
Codes get a higher bar
Product codes now need a matching heading or enough code-like cells before they're suggested. A wrong code would match rows to the wrong product. I think a short all-caps brand can still sneak through as a code, so that one is worth watching for.
Case size also changes the product itself, not the supplier's entry. Other suppliers' orders likely use that number too.
Files that will fool it
Prices with a comma for the decimal point won't look like numbers. By my reading, whole-dollar prices can get offered as case size. And the importer only samples the top of the file, so if the file is sorted by category, the first rows may not look like the rest of the column.