Importing a supplier price list from a spreadsheet without breaking your pricing
- A supplier price list is only safe to import when columns are mapped, prices carry a single consistent unit, currency symbols are removed from cells, and each product code appears exactly once.
- Mapping supplier column names to your system's field names once - and saving that mapping per supplier - removes the manual reformatting cost from every future price update.
- Unit inconsistency (per item vs per pack) is the most common cause of imported costs that produce wrong quote totals, because the numbers look plausible until a quote is actually built from them.
- Duplicate product codes must be resolved in the spreadsheet before import, not by investigating inconsistent prices in your catalog or quotes afterward.
- A test import on ten rows confirms that your mapping configuration works as intended before a formatting error propagates across hundreds of product records.
Most import tools accept a supplier price list without complaint and leave the errors for you to find later, in quotes and purchase orders. This guide covers the four conditions a supplier spreadsheet must meet before import, and seven steps to clean it in one pass.
A supplier's price list is ready to import when four conditions are true: columns map to the fields your system expects, prices express a consistent unit (per item, per pack, per carton), costs are in one currency without symbols embedded in the price cells, and each product code appears exactly once. When any condition fails, most import tools accept the file without an error message. The damage surfaces later - when a quote goes out at the wrong margin, when a purchase order is raised at a cost-per-carton when the job needed cost-per-unit, or when the same SKU appears twice in your catalog with two different prices and the system picks one at random. The problem is not the import tool. It is that the work of preparing the data was deferred to after the import, where it costs far more to fix. This guide takes each condition in turn so you can move a supplier spreadsheet from inbox to usable cost data in one pass.
What actually breaks when a price list is imported without preparation
Badly prepared imports produce three categories of failure, and only one of them is obvious. The first is an outright import error: the tool rejects a file, logs a row count, and you know immediately that something is wrong. That is the easy case.
The second category is silent corruption. The file imports cleanly, but a mis-mapped column - "Net Price" mapped to a description field, for example - leaves your cost database blank for hundreds of products. You discover this when a quote built from inventory records returns a zero cost line and a salesperson fills in a number from memory.
The third category is the most expensive: plausible but wrong. A price that should be cost-per-item has been imported as cost-per-box of 24. The records look reasonable. Quotes build without warnings. The error only surfaces when a customer queries the total or an order comes in below margin. Research into supplier catalog onboarding practices shows that rushed manual reformatting is the primary point where cost errors enter a catalog - a mis-mapped column publishes wrong prices before anyone realizes the mapping was wrong.
The steps below do not guarantee a perfect import. They guarantee that the common failure modes are addressed in the spreadsheet, where they are easy to find, rather than in live pricing data, where they are not.
Where this problem is most visible in practice
Promotional merchandise distributors deal with supplier price list imports more frequently than most, and the complexity is higher. A typical supplier file from a major distributor carries separate columns for each quantity break (50, 100, 250, 500 units), distinct pricing for each decoration method, setup fees, run charges, and in some cases PMS colour surcharges. A distributor receiving updated files from ten or fifteen suppliers needs each file to arrive in a consistent internal format before any of it can flow into a quote. When that preparation step is skipped, the distributor ends up with ten different cost structures that cannot be compared, and pricing errors that only become visible when a client challenges a margin calculation. The conditions below apply across industries, but the stakes are particularly clear in high-volume, multi-supplier businesses where a single import populates dozens of active lines.
Audit the file structure before you change anything
Open the spreadsheet and record what is there before touching a single cell. Note the number of columns, whether row one contains headers or data, how many rows (products) are present, and whether the file uses multiple tabs. Suppliers frequently split pricing variants, quantity breaks, or discontinued items across separate tabs - an import that reads only the first sheet will miss data without flagging a warning. Write down the column names exactly as they appear. "Net Price," "NP," "Cost," and "Unit Cost" are often the same field with four different names, and your mapping work in the next step will be faster if you have the full column inventory in front of you before you start.
Map supplier column names to your system's field names
Create a two-column reference - on paper or in a separate sheet - with the supplier's header on the left and your system's field name on the right. Assign every column to one of three categories: import as-is (it maps directly to a required field), transform then import (the data needs changing before it maps), or discard (your system does not use it). Pay particular attention to product identifiers. Suppliers routinely use "Item #," "Product Code," "SKU," "Ref," and "Cat No" for different levels of classification - and only one of them is the identifier your system expects to receive. A mapping saved per supplier means you can reuse it the next time that supplier sends an updated file, removing the manual reformatting cost from every future price update.
Normalize units before you touch any price
Unit inconsistency is the most expensive formatting problem to fix after an import, because the errors it creates look plausible. A file where some products are priced per item, others per dozen, and others per box of 48 will produce costs that survive a visual scan and calculate incorrectly when a quote is built from them. Before the import, add a column to the supplier's file labelled "Confirmed Unit" and fill it for every row. If a product is priced per box of 24, record "24" in a separate quantity-per-pack column and divide the box price to get cost per unit - or configure your import mapping to carry both the pack price and the pack quantity, if your system supports it. Never leave a unit assumption undocumented inside the import configuration. What looks like a reasonable cost of £1.20 per item looks very different when it is actually £1.20 per pack of twelve.
Remove currency symbols and resolve multi-currency rows
A price cell containing "£8.50" or "$12.00" will be read as text by most import tools. The row imports without an error, the price field is left blank, and you have a product in your catalog with no cost attached. Before importing, remove all currency symbols and thousands separators from price columns, leaving only the numeric value. If the file contains prices in more than one currency - common when a supplier operates across multiple markets - split the file by currency before importing and tag each batch with the correct currency code at import time. Deferring the currency decision to after the import means re-processing rows you have already committed to your catalog, and it creates a window where purchase orders can be raised against costs in the wrong currency.
Identify and resolve duplicate product codes
Sort the cleaned file by product code and look for rows where the same code appears more than once. Duplicates are common: a supplier may list the same product at two price points (current and sale), show a discontinued and a current version under the same base code, or have corrected a pricing error by adding a second row rather than editing the first. Your system will handle these duplicates in one of three ways depending on how it is configured: import both rows and create two records, import the first and ignore the second, or import the second and overwrite the first. None of those outcomes is predictable unless you have made the decision in the spreadsheet first. Decide for each duplicate whether you need one record, two records with differentiated codes, or neither - and make that decision before the import runs, not by investigating inconsistent quote results later.
Run a test import on ten rows
Take the first ten rows of the cleaned and mapped file and import them alone. Compare every field in the imported records against the source spreadsheet line by line: product name, cost, unit, currency, supplier reference. This step is not about finding data errors - you addressed those in steps one through five. It is about confirming that your mapping configuration does what you intended: that price fields were read as numbers not text, that no column was silently skipped, and that the identifier your system uses to match incoming records to existing ones is working correctly. A mapping error found on ten rows takes minutes to fix. The same error found after a five-hundred-row import takes an afternoon and carries the risk that quotes built in the meantime used incorrect costs.
Commit the full import and check live records against source
Once the test batch is correct, import the full file. After the import completes, run a spot-check on at least five products selected at random from different parts of the file. Verify cost, unit, currency, and supplier reference against the original spreadsheet. Then check whether products already in your catalog have been updated correctly - if the supplier has issued new pricing on lines you already held, the import should have overwritten the old cost, not created a second record. A product showing the previous supplier cost is not caught until a purchase order is raised against it or a quote using that line reaches a customer. Confirm the records match the source file while the spreadsheet is still open and the comparison is immediate.
Connecting imported costs to quotes and customer pricing
A supplier price list that has been imported correctly becomes the cost foundation for every downstream workflow: quotes, purchase orders, and - for businesses that offer customer-specific pricing - API-driven price structures. When that foundation is clean, a salesperson building a quote pulls cost data that reflects what the supplier actually charges. When it is not, the quote system inherits whatever errors the import introduced, and margin management becomes guesswork.
For businesses offering agreed price levels to specific accounts, accurate supplier costs at import time are essential. A customer pricing API applies a margin or markup rule on top of the underlying cost - which means the output is only as accurate as the cost data it starts from. An incorrectly imported cost-per-carton, applied with a 30% markup rule, produces a customer-facing price that looks structured but is priced from a wrong base. The error compounds at every level of the pricing stack, which is why the seven steps above are worth running completely before any imported data is used in live pricing.
Sources
- Supplier catalog onboarding: normalizing inbound formatsdatathere · accessed 2026-09-21
- Import a Supplier CSV into Shopify with AI MappingApimio · accessed 2026-09-21
- Excel price list management problems: how to stop sabotaging salesOn Page · accessed 2026-09-21