Importing your supplier list from a spreadsheet without breaking your pricing
- A unique supplier code column is the single most important field in any supplier import - without it, the same supplier imports as multiple records, each with potentially different cost prices.
- Lead times must be a single integer in working days, not a range or phrase, for a purchasing system to generate accurate purchase order timing and reorder alerts.
- Currency codes belong in their own dedicated column, separate from cost prices - leaving them blank causes the system to apply the account default currency, which may not match what the supplier actually invoices in.
- Run a test import on five to ten rows before committing the full list, and compare each field against your source spreadsheet before importing the remainder.
- After import, raise a test purchase order against your highest-volume supplier to confirm that cost prices, currency, and lead times populated correctly from the supplier record.
A supplier spreadsheet import fails when supplier codes, lead times, currency codes, and cost prices are inconsistent or missing. This guide walks through preparing those four fields, mapping them correctly, and checking the result after the import runs.
A supplier spreadsheet import works when four columns are in shape before the upload runs: a unique supplier code, a lead time value, a currency code, and a cost price. Without all four formatted consistently, a purchasing system cannot distinguish between two records for the same supplier entered under slightly different names, cannot calculate realistic delivery dates, and cannot build accurate purchase orders. Get those columns right first, and the rest of the import - contact details, payment terms, shipping addresses - falls into place cleanly. Miss them, and you end up with duplicate records, wrong costs, and a supplier list that actively undermines the pricing you have already agreed with customers.
The four columns that matter before you start
Procurement teams that migrate supplier data for the first time often focus on the visible fields: company name, address, phone number. Those matter, but they are not the fields that break pricing when they are wrong.
The four columns that create problems when they are missing or inconsistent are:
Supplier code. This is the unique identifier that stops the same supplier appearing twice in your system. Without it, "Acme Print Ltd" and "Acme Print" and "ACME PRINTING" look like three separate suppliers to any import tool. A supplier code - even a simple short reference like ACP001 - acts as the anchor that deduplicates on import. Research published in 2026 found that organizations without a clean supplier code structure end up with 10 to 20% of their vendor database as duplicates, which splits payment history and creates phantom records that corrupt spend analysis.
Lead time. A lead time field tells your system how far in advance to raise a purchase order when stock runs low or a job is confirmed. Without it, reorder alerts either trigger too late or not at all. The field should contain a number representing working days, not a range or a phrase. "5-7 days" cannot be processed; "6" can.
Currency code. If you buy from suppliers in different currencies, every cost price in the spreadsheet needs a three-letter ISO currency code in a dedicated column. Mixing USD, EUR, and GBP in the same column without labeling them means the system has no way of knowing which exchange rate to apply when a purchase order is raised. One missing currency code on a key supplier can push every purchase order raised against that record to the wrong cost.
Cost price. This is the per-unit price you pay the supplier, not the price you charge the customer. The cost price column must use a consistent decimal format across the whole sheet. If some rows use a comma as the decimal separator and others use a full stop, half your cost prices will import as thousands rather than unit costs. A figure entered as "1,250" when it should be "1.250" makes a single item look 1,000 times more expensive than it is.
Duplicate records are the most common import failure
Organizations that have completed a structured vendor cleanup consistently find that duplicate supplier records account for 10 to 20% of their total database. Even a small supplier list of 50 records can contain five or more duplicates if it has been maintained across different spreadsheets by different people over time.
Why pricing breaks when the file is not right
The connection between supplier data quality and customer pricing is direct, and it is easy to underestimate. When a purchasing system imports a supplier's cost price, that figure becomes the floor for every quote raised against products sourced from that supplier. If the cost price is wrong - whether through a formatting error, a duplicate record with an outdated price, or a missing currency code - every quote built on top of it carries the error forward.
For businesses in promotional merchandise, where a single order might involve ten or more supplier lines across different decoration methods, a single incorrect cost price can make an entire quote unprofitable before it leaves the building. The promotional merchandise industry compounds this risk because the same physical product might be sourced from multiple suppliers at different costs depending on quantity, and the system needs all those cost lines to be accurate to calculate run charges and setup fees correctly.
The same logic applies to businesses using inventory management features tied to supplier pricing. If the cost price on a supplier record is wrong, every stock valuation that draws from it is also wrong, and the discrepancy only surfaces when someone manually compares the system figures against a supplier invoice.
How to prepare and run the import
Export your existing spreadsheet to a clean CSV file
Before touching the data, export everything to a plain CSV with UTF-8 encoding. Remove any merged cells, colored formatting, and rows that contain totals or subtotals - import tools read every row as a data record, so a subtotal row imports as a supplier. Save the CSV with a clear filename that includes the date, so you know which version was used if the import needs to be repeated.
Add a supplier code column if one does not already exist
Scan the spreadsheet for any existing reference numbers used in correspondence with suppliers - account numbers, short codes, anything consistent. If no codes exist, build them now. A simple format works: the first three letters of the supplier name plus a three-digit number (for example, MID001 for Midocean, PEN001 for Pencarrie). Every row must have a unique code. Duplicates in this column are the primary cause of duplicate supplier records after import.
Standardize names, lead times, and currency codes
Work through the supplier name column and remove trailing spaces, inconsistent capitalization, and abbreviated entries that might create confusion. Use the trading name as it appears on invoices - that is the name your accounts team will recognize when matching a supplier invoice against a purchase order. In the lead time column, replace every range, phrase, or blank with a single integer in working days. For any supplier where lead time is genuinely unknown, use a conservative default - five working days is a reasonable starting point for most trade suppliers. In the currency column, replace any written currency name with the three-letter ISO code: GBP, USD, EUR, AUD. Check that the code matches the currency the supplier actually invoices in, not the currency of your bank account.
Check and format the cost price column
Sort the spreadsheet by the cost price column and scan for outliers. A row showing 12,500 when neighboring rows show 12.50 is almost certainly a decimal separator error. Check the formatting settings on your spreadsheet software if you are based in a region that uses commas as decimal separators, and ensure the export produces full-stop decimals throughout. Remove any currency symbols from the cost price column - the system reads the currency from the dedicated currency code column, not from a symbol embedded in the price figure. A column containing "£12.50" imports differently to one containing "12.50", and some systems reject the symbol entirely.
Map your columns to the system's expected fields
Every purchasing system asks you to map your spreadsheet columns to its own field names during import. Your column called "Supplier Reference" needs to map to the system's "Supplier Code" field; your "Days to Deliver" column needs to map to "Lead Time (days)". Work through the mapping screen methodically and save the mapping configuration before running the import. That saved configuration becomes the template for every future update to the supplier list, so the work done now pays forward. When you reach the currency and cost price fields, confirm that the default currency for each supplier record is being set at this stage, not left blank. A blank currency field on a supplier record causes the system to fall back to the account default, which may not match the currency you actually pay in.
Run a test import with five to ten rows
Before importing the full list, select five to ten representative rows - a mix of domestic and international suppliers, suppliers with different lead times, and at least one with a decimal cost price and one with a round-number cost price. Import only those rows. Then open each imported supplier record and compare it field by field against your original spreadsheet. Check: Does the supplier code match? Is the lead time a number, not a phrase? Is the currency code correct for that supplier? Is the cost price showing the right figure with the right number of decimal places? If any field is wrong, correct the mapping configuration and re-run the test batch rather than importing the full list with a known error. Fixing ten records is quick; fixing three hundred is a project.
Import the full list and run spot checks
Once the test batch passes, import the full supplier list. After the import completes, run three spot checks: pick a supplier from the start of your list, one from the middle, and one from the end, and verify each record against the source spreadsheet. Then search for any supplier name that appears more than once in the system - if duplicates exist, they will have different supplier codes, so you can merge or delete the unwanted record before any purchase orders are raised against it.
Search by common words after import
Run a search in your supplier list for words that appear in multiple supplier names - words like "print", "supply", or "group". A post-import name search catches any duplicates the supplier code column missed, particularly where two team members entered the same supplier under slightly different codes before the import was standardized.
What to check after the import runs
A successful import confirmation screen does not guarantee the data transferred correctly. The confirmation means the rows were processed without a system error; it does not mean every cost price, lead time, or currency code landed in the right field.
Spend ten minutes after the import running four checks. First, raise a test purchase order against one of your highest-volume suppliers and confirm the default cost price and currency populate automatically from the supplier record. Second, check the lead time on the same supplier record and confirm it is a number rather than a text string. Third, open the inventory view and confirm that any products linked to that supplier show the correct cost price. Fourth, run a count of total supplier records in the system and compare it to the number of rows in your original spreadsheet - a higher count means duplicates exist; a lower count means some rows were rejected and need to be investigated.
Cost prices drift after import
The most common post-import problem is not an error message - it is silence. Cost prices imported from a spreadsheet become the system's working costs for every purchase order raised from that day forward. When a supplier updates their prices, that change needs to go into the system directly, not just into a spreadsheet saved on someone's desktop. Establish a process for updating supplier cost prices in the system at the same time as price changes are agreed, so the two records never diverge.
The time spent cleaning the file before import is almost always less than the time spent fixing corrupted cost prices after the fact. Duplicate supplier records consistently account for 10 to 20% of total vendor databases before a structured cleanup, according to research published by supplier data management specialists in 2026. For a small business with 50 active suppliers, two duplicates are manageable. For a business with 300 suppliers across multiple currencies, unchecked duplicates create pricing errors that surface months later when a supplier invoice lands and nothing in the system matches it cleanly.
Sources
- The Real Cost of Bad Supplier DataSupplier.io · accessed 2026-09-20
- Supply Chain Data Quality Risks & How to Fix ThemRPI Consultants · accessed 2026-09-20
- Vendor List Template for Excel (Free Download)ProjectManager · accessed 2026-09-20