Map supplier product codes to internal SKUs

Define the canonical SKU and its aliases

Choose the internal SKU used for stock and orders, then treat supplier and legacy codes as aliases. Combine supplier ID with source code because identical strings from different suppliers may identify different goods. Product-name similarity is not enough: check model, size, colour and pack quantity. Agree how canonical SKUs can be changed and preserve older relationships. A code used in historical orders should not silently start identifying a different physical item.

Include unit and pack conversion in the mapping

Use supplier ID, source code, internal SKU, source unit, base unit, conversion factor, reviewer and effective date. If BX-10 represents a box of ten while your SKU counts individual units, quantity three means thirty units, not three. A packaging change needs a new effective mapping rather than an overwritten factor. Hold rows with an unknown unit. Correct code matching with an incorrect pack conversion can still produce a large inventory error.

Limit normalisation to deliberate comparison rules

Trim accidental surrounding spaces in a comparison value, but do not assume leading zeroes, hyphens or letter case are irrelevant. Codes 0012 and 12 may be distinct, so avoid automatic numeric conversion. Preserve the original string and document the comparison rule. Also check what a spreadsheet application does when opening a CSV. The import can already be damaged before your mapping step if the source code is converted to a number or date.

Hold unmatched and ambiguous rows

Classify results as matched, unmatched, ambiguous or outside the mapping’s effective period. Do not automatically create a new product for every unmatched alias; it may already exist under another code. Hold multiple matches until a reviewer resolves them. The database’s record-update key and the product-code mapping are separate checks. Finding an existing row does not prove that the source quantity belongs to that product or that the conversion factor is current.

Trace the impact of an incorrect mapping

Identify supplier, source code, effective period and import IDs when a mapping is wrong. Fixing the lookup table does not reverse earlier updates. Trace which orders or stock adjustments used it, compare source data with the current state and plan a scoped correction. If goods have already shipped, customer handling is a separate responsibility. Keep the old mapping and correction history so a later investigator can explain why the earlier result occurred.

Test identifier boundaries and packaging changes

Test leading zeroes, full-width characters, the same code at two suppliers, box versus unit, a retired code and multiple matches. Compare row counts and quantity totals before and after the proposed update. A result labelled “all matched” is insufficient if the converted quantities are wrong. Have someone review mapping changes independently before a large import. A small set of deliberate boundary cases can prevent a perfectly executed import from updating the wrong goods.

BX-10: three boxes are not three units

A CSV row from supplier A contains BX-10 and quantity three. With ten units per box and internal SKU ITEM-012, the base quantity is thirty. Supplier B may use the same string for twelve-unit boxes and therefore needs another mapping. If A later changes to eight-unit boxes, use an effective period rather than rewriting earlier receipts as twenty-four units. Show source quantity, unit, factor and result together. A plausible result is not enough; it must trace back to supplier and date.

Choose a tool and budget around the actual workload

A CSV mapping table may be enough for a small catalogue. A linked register helps when suppliers, packaging and effective dates become difficult to track. Estimate unmatched and ambiguous rows as well as total SKUs. Include review of code changes, re-import work and physical checks in the budget. AI can suggest candidates from names, but a plausible description is not proof of product identity. Approve mappings before they are allowed to change stock or order quantities.

• Import CSV into Airtable: new records, merging updates and duplicate checks
• Choose CSV import automation by update and error behavior
• Normalize expense CSV rows with Make