Normalize Vendor Catalogs from Multiple Suppliers (Without Going Insane)

If you work with multiple suppliers, you know the pain. Vendor A sends an Excel file with prices in column F. Vendor B sends a CSV with prices in column C - and they include the currency symbol. Vendor C doesn't even include headers. Getting all this data into your inventory system requires normalizing everything to one format.

The Vendor Data Problem

Suppliers export data from whatever system they use, in whatever format that system produces. You end up with a collection of files that look nothing alike, even though they contain the same basic information: SKU, product name, price, quantity.

Common Inconsistencies

The Target Format

Before cleaning anything, define your target format. What columns does your inventory system need? What format should SKUs be in? What date format? Document this so you have a clear goal.

Example target format:
Column      | Format               | Required
sku         | ABC-123 (uppercase)  | Yes
name        | Title Case           | Yes
price       | 19.99 (no symbol)    | Yes
quantity    | Integer              | Yes
vendor_code | Original from vendor | No

Strategy: Per-Vendor Pipelines

The key insight is that each vendor's format is consistent - they send the same format every time. Instead of manually cleaning each file, create a reusable cleanup pipeline for each vendor. Once built, you can process new files in seconds.

Building a Vendor Pipeline

For each vendor, identify what transformations are needed. Common steps include: renaming columns to your standard names, formatting prices, standardizing SKUs, and handling empty values.

Example pipeline for "Vendor A":
1. Rename column "Part Number" → "sku"
2. Rename column "List Price" → "price"
3. Rename column "Product Name" → "name"
4. Rename column "Stock" → "quantity"
5. Remove "$" from price column
6. Convert SKU to uppercase
7. Replace "N/A" with empty
8. Remove rows where SKU is empty

Handling SKU Formats

SKUs are often the trickiest part. Vendors use different separators (dash, underscore, slash), different cases, and sometimes embed extra information. Decide on your standard format and transform everything to match.

SKU Normalization Steps

Price Cleanup

Prices come in many formats. Currency symbols, comma separators (European), amounts in cents - all need to be normalized to plain decimal numbers.

Input variations → Normalized output:
$19.99    → 19.99
19,99     → 19.99 (European)
1999      → 19.99 (cents)
$1,234.56 → 1234.56
"19.99"   → 19.99 (quoted)

Empty Value Standardization

Different systems represent 'no data' differently. Before importing, standardize all empty representations to actual empty cells or a consistent placeholder your system understands.

Workflow: Processing a New Vendor File

Once you've built pipelines for each vendor, processing new files is simple: upload, select the vendor's pipeline, run it, and export the cleaned result ready for import.

Handling New Vendors

When you get data from a new vendor for the first time, don't just manually fix it. Take an extra few minutes to build a pipeline. You'll use it every time that vendor sends updates.

Combining Multiple Vendors

After cleaning individual vendor files, you may want to combine them into one master catalog. Add a 'vendor' column before merging so you can track where each product came from and filter later if needed.