Importing a supplier price list from a spreadsheet without breaking your product data
- Column mapping is the first and most critical step - without it, supplier prices land in the wrong fields and quotes are built on incorrect costs.
- Never open a CSV supplier file by double-clicking it in Excel; use the Data Import wizard with all columns set to Text to prevent automatic formatting corruption.
- Strip currency symbols and embedded unit labels from price fields before import - a cell reading £49.50 is a text string, not a number, and cannot be used in calculations.
- A test batch of 10 to 20 rows exposes silent errors before they reach your full catalog - test a quote line from the imported product before proceeding to the full load.
- Save the column mapping template after each import so future price list updates from the same supplier take 30 minutes rather than a full working session.
Supplier price lists arrive as inconsistent spreadsheets. This guide covers the complete import routine - column mapping, currency standardization, a test batch, and full load validation - that keeps pricing current without corrupting the catalog your team quotes from.
Getting a supplier's spreadsheet of products and prices into your quoting system correctly takes more than an upload. You need to map the supplier's columns to your system's fields, strip currency symbols and unit labels from prices, validate that SKUs haven't been reformatted by Excel, and run a small test batch before committing the full load. Skip the mapping step and prices land in the wrong fields. Skip the test batch and you find out about a corrupted SKU column when a customer's quote returns a wrong figure rather than during the import itself. The steps below give you a repeatable routine - whether you're refreshing a single supplier's price list or onboarding a new one from scratch.
Why Supplier Spreadsheets Never Arrive the Same Way Twice
A mid-sized promotional merchandise distributor typically works with ten to twelve suppliers. Each one carries tens of thousands of SKUs and formats its data differently - colours named in its own way, size grids that don't align, pricing broken into tiers with labels no two suppliers write the same. That inconsistency is not only a promotional merchandise problem. Any business sourcing products from multiple suppliers faces it. A furniture dealer receives line-item sheets with column headers in a supplier's own shorthand. An AV systems integrator gets a tab-separated file from one supplier and a multi-sheet workbook from another.
The core challenge is that your quoting system expects data in a fixed shape: one column for the supplier SKU, one for the description, one for the unit cost in pounds sterling, one for the minimum order quantity. When a supplier's file uses "Product Code", "Item Name", "Trade Price (excl. VAT)", and "MOQ" across four columns that can appear in any order, nothing maps correctly without a deliberate translation step.
Nearly three-quarters of the largest promotional merchandise suppliers reported higher procurement costs through 2025, which means price lists move more often. The question is not whether you will receive an updated file this quarter - you will. The question is whether your import routine is reliable enough that the updated prices reach your inventory and quotes without errors introduced along the way.
Update frequency
Major suppliers typically issue revised price lists quarterly, though commodity-linked products - especially garments and metals - can update more often. Build your import routine so it can be run in under 30 minutes, not only when you have a spare afternoon.
What Corrupts an Import Before You Notice
The most common import failures happen before a single row reaches the target system. Schema mismatches - where column names or column order differ from what the import template expects - are the most frequent cause of failed CSV imports. But the silent failures are worse. These are imports that complete without errors and corrupt the data they load.
Excel is the most common source of silent corruption. When a file containing numeric product codes is opened in Excel and saved, the default "General" format removes leading zeros - a code like "0421" becomes "421" - and converts long identifiers to scientific notation. A product code that read "5000174621" becomes "5.00017E+09". Once saved, that change is permanent. The downstream effect is that purchase orders raised against the corrupted SKU reference a product that no longer matches the supplier's own code, which causes mismatches when the delivery note arrives.
Date format ambiguity causes the same class of problem. The value 01/02/2026 means January 2nd in the United States and February 1st in the United Kingdom. A price list that includes effective dates or discount expiry dates becomes unreliable if the import does not enforce an explicit date format. Currency symbols cause a different kind of failure: a price field that reads "£49.50" is a text string, not a number. Import it as-is and your system cannot perform arithmetic on it.
The Import Routine
The eight steps below form a repeatable routine. Work through them in order - each step produces a cleaner file for the next one.
Save a working copy of the supplier file before making any changes
Open the supplier's original file and immediately save a copy under a new name that includes the supplier name and the date - for example "Supplier-A-pricelist-2026-09-19-working.xlsx". Never edit the original. If you need to restart or trace an error, the unchanged source file is the only reliable reference. This matters especially when you receive a revised file: starting from the wrong version means your "clean" copy already contains the errors you corrected in a previous import round.
Open CSV files through the Data Import wizard, not by double-clicking
If the file is a CSV, do not open it by double-clicking it in your file browser. That triggers Excel's automatic formatting and immediately corrupts product codes that contain leading zeros or look like dates. Instead, open a blank Excel workbook, go to the Data tab, choose "Get External Data" or "Get Data", and import the CSV with all columns set to Text format. This preserves the supplier's original values before any formatting is applied. If the supplier has sent an XLSX file, check the SKU column before and after any save to confirm values have not changed.
Audit the supplier's column structure against your import template
List every column in the supplier's file and match it to the corresponding field in your system's import template. Write the mapping down in a simple two-column table: the supplier's header in one column, your system's field name in the other. Common mismatches include: the supplier may use "Trade Price" where your template expects "Unit Cost", "EAN" where your template expects "Barcode", or "Min Qty" where your template expects "MOQ". Note every column the supplier uses that your template has no field for - those columns need a decision: import into a notes field, ignore, or request a custom field from your system administrator.
Standardize prices, units, and currency
Work through the price columns and remove any currency symbols, thousands separators, or unit labels embedded in the value. A cell reading "£12.50 per 100" needs to be split into a clean unit cost ("12.50") and a unit description ("per 100") in separate fields. Strip the pound sign from all price cells so the values are plain numbers. For products with multiple sizes or colours at different price points - such as kits and variants - confirm that each variant appears as its own row with its own SKU rather than as additional columns in a single row, which is what most import templates expect.
Unit mismatch
If a supplier prices by the dozen and your system stores unit costs, divide the per-dozen price by 12 before import. Loading a per-dozen cost as a unit cost means every quote built from that product will be underpriced by a factor of twelve - and the error will look correct until someone checks the supplier's actual invoice.
Validate SKU integrity before import
Check the SKU column against the supplier's own reference document if you have one - a previous confirmed purchase order is a reliable source. SKUs should contain only the characters the supplier uses: alphanumeric characters and hyphens are typical; spaces and special characters are usually signs of corruption. Filter the column for cells showing scientific notation (values like "1.2E+08") and restore them from the original file. Flag any SKU that does not match the expected format before it enters the system, because a mismatched SKU cannot be corrected in bulk after the fact.
Run a test batch import of ten to twenty rows
Select a representative sample of rows from the file - include at least one product with a variant, one with a minimum order quantity above one, and one with a long SKU. Import this test batch into a staging environment or a clearly labelled test category in your live system. Review every field in the imported records: check that unit costs are numbers, SKUs are intact strings with no truncation, descriptions are complete, and any tiered pricing has imported into the correct tier fields. A test batch of twenty rows takes five minutes to review. Finding the same error across 2,000 rows takes considerably longer and requires a full reimport.
Test the quote too
Open a quote and add one of the test-imported products as a line item. Confirm that the unit cost, description, and minimum order quantity all pull through correctly. That is the moment the import earns its clean bill of health - or shows you one more thing to fix before the full load.
Run the full import and compare the row count
Import the complete file and note the number of records successfully imported. Compare that figure against the number of data rows in the supplier's file (excluding header rows and any totals lines at the bottom). Any discrepancy - rows rejected, rows skipped - needs investigation before you treat the import as complete. A rejected row typically means a required field was missing or a value failed a type check. Go back to the source file, fix the specific rows flagged in the import error log, and reimport the failed records rather than manually entering them.
Flag discontinued and changed lines after the full import
After the full import, compare the new product list against the records already in your system for that supplier. Products that were in your system but are absent from the new file have likely been discontinued. Do not delete them immediately - check whether any open quotes or purchase orders reference them. Mark them as inactive rather than deleted so that existing documents remain coherent. Products with changed SKUs need the same treatment: the old record stays linked to historical documents while the new SKU becomes the active one going forward.
Saving the Mapping for Next Time
The column mapping you created in Step 3 is the most valuable output of the whole routine. Save it alongside the working copy of the file in a folder named for the supplier. When the next price list arrives, you open that folder, check whether the supplier has changed any column headers since last time, update the mapping if needed, and move through Steps 4 to 8 at pace. What takes two hours the first time should take under thirty minutes by the third iteration.
Where your supplier provides data through a direct catalog integration rather than a manual spreadsheet, the mapping step is handled by the integration itself - Zigaflow connects directly to a number of promotional merchandise suppliers, normalizing catalog data into a consistent format before it reaches your inventory. For suppliers who still send a flat file, the routine above is the difference between a quarterly price update that occupies an afternoon and one that takes most of the week and still leaves you uncertain whether the data is clean.
Sources
- Why Data Imports Fail: Top 11 CSV & Excel ErrorsDataFlowMapper · accessed 2026-09-19
- 11 Common CSV Import Errors and How to Fix Them (2026)FileFeed · accessed 2026-09-19
- Guide to Importing and Synchronizing Price ListsElbuz · accessed 2026-09-19
- Print MIS for Promotional Products Distributors: Managing SKUs and VendorsPrint Monthly · accessed 2026-09-19