Supplier Price List Cleanup for Marketplace Uploads (Shopify, Amazon, eBay)

12 min readPipeSheets Team

A supplier price list is not a product feed. It is a document built for a buyer and an accountant: SKUs typed inconsistently, three price columns where you only need one, currency symbols and thousands separators baked into the numbers, and pack sizes hidden in a description field. To upload it to Shopify, Amazon, or eBay you have to reshape it into the exact columns each platform expects. This guide walks the full workflow, field by field, so the file that reaches Seller Central or your Shopify import screen is clean, correctly priced, and mapped to your own SKUs.

The stakes are higher than a rejected import. The single most expensive mistake in this workflow is pushing the wholesale cost column into the retail price field. Do that on Amazon or Shopify and you will sell real inventory at your buy price until someone notices. Everything below is organized to make that specific error impossible, and to get the rest of the file through validation on the first try.

What does a marketplace upload actually need from a price list?

Every marketplace importer wants the same three things from your data, no matter how different their templates look: a unique identifier it can match, a single clean price number, and each sellable unit on its own row. A supplier price list usually fails all three. Before touching a specific platform, get the file into that shape.

The universal requirements underneath every marketplace template:

One identifier column that is unique per sellable unit and matches your system, not just the vendor's.
One price column holding a plain number: no currency symbol, no thousands separator, a period as the decimal point.
One row per sellable unit, with pack size resolved so 'case of 12' and 'each' are never in the same price without a note.
Consistent text encoding (UTF-8) so accented brand names and special characters survive the upload.

How to normalize vendor SKUs before matching

Vendor SKUs are the join key for the entire job, and they are almost always dirty. The same part number shows up as ABC-1001, abc 1001, and ABC1001 across three rows or three files. Leading and trailing whitespace hides in cells that look identical. Some vendors prepend a line-code prefix (VEN-, W-, the rep's initials) that your marketplace SKUs do not carry.

Normalize before you match, in a fixed order: trim whitespace first, then standardize case, then strip or map any prefix, then collapse internal separators if your scheme requires it. Do it in that order every time so the result is deterministic. A SKU like ' abc-1001 ' with a trailing space will never match 'ABC-1001' in a lookup, and the failure is silent: the row imports as a brand-new product instead of updating the one you meant.

BEFORE (vendor SKU column, as received)
  abc-1001
  ABC-1001
  VEN-ABC-1002
   abc 1001
  abc-1003 

AFTER (trim -> uppercase -> strip 'VEN-' prefix)
  ABC-1001
  ABC-1001
  ABC-1002
  ABC 1001   <- still wrong: space vs hyphen, needs a find/replace pass
  ABC-1003

Never let Excel touch a SKU column that looks numeric. A vendor SKU like 0041500 loses its leading zeros the moment Excel parses it as a number, and 1.20E+11 is what a 12-digit UPC becomes on save. Once those digits are gone the match is broken and there is no clean way to recover the original. Keep SKUs and barcodes as text end to end.

Which price column do I upload: wholesale, MAP, or MSRP?

This is the decision that turns a routine import into a costly one. A supplier price list often carries several money columns, and they mean completely different things. Uploading the wrong one is the mistake to design against.

The columns you are likely to see, and what each one is:

Wholesale / cost / dealer price: what you pay the supplier. This is your cost of goods, not a selling price. It belongs in an internal cost field, never in the marketplace price column.
MAP (Minimum Advertised Price): the floor the brand allows you to advertise at. Useful as a guardrail, but it is a minimum, not necessarily your price.
MSRP / list / retail: the manufacturer's suggested retail. Often a reasonable starting point for your selling price, though you may price above or below it.
Your calculated retail: cost times your markup, or a rule built on MAP/MSRP. This is what usually belongs in the price field.

Decide the mapping explicitly and write it down before you build the file: which source column becomes the marketplace price, and which becomes an internal cost you keep but do not upload. Rename the columns so the intent is unmistakable. A column literally named 'wholesale_cost' will never get dropped into Shopify's Variant Price by accident the way an ambiguous 'Price' column will.

Sanity-check the final price column before you upload: sort it low to high and look at the top rows. If your cheapest 'retail' prices look like buy costs, you mapped the wrong column. This 30-second check has saved more margin than any other step in this workflow.

How to strip currency symbols and thousands separators

Marketplace price fields want a bare number. Shopify's documentation is explicit that the Variant Price and Variant Compare At Price fields must not contain currency symbols. Amazon's flat file guidance says to enter prices as numbers with a period as the decimal separator and no dollar sign or commas. A price list, meanwhile, is formatted for reading: $1,299.00, or 1.299,00 if the supplier is European.

You need to remove the currency symbol, remove the thousands separator, and make sure the decimal point is a period. Watch the European trap: in 1.299,00 the period is the thousands separator and the comma is the decimal. Blindly deleting commas turns 1.299,00 into 1.29900, off by a factor of 100. Convert the format deliberately rather than stripping characters at random.

BEFORE (price column, mixed formatting)
  $1,299.00
  $49.99
  1.299,00      <- European: . is thousands, , is decimal
  USD 12.50
  -            <- placeholder for 'no price'

AFTER (bare number, period decimal, blanks left empty)
  1299.00
  49.99
  1299.00
  12.50
  (empty)

How do I handle pack sizes and units of measure?

Supplier price lists frequently price by the case while you sell by the each, or they mix both. A row reading 'Widget, case of 12, $18.00' means $1.50 per unit if you are listing singles. Upload the $18.00 against a single-unit listing and you have overpriced by 12x; do the reverse and you sell a case for the price of one. The pack information is often buried in a description or a separate 'UOM' column rather than a clean quantity field.

Resolve unit-of-measure issues before pricing:

Pull the pack quantity into its own numeric column (case_qty) instead of leaving 'case of 12' inside a description string.
Decide the unit you actually sell in, and compute a per-unit price consistently across the whole file.
Split combined rows: if a supplier lists the same item as both 'each' and 'case', they are two listings with two SKUs, not one.
Flag any row where the unit is ambiguous rather than guessing; a wrong per-unit price is invisible until a customer buys it.

Variant rows and merged cells

Price lists exported from Excel love merged cells. A parent product name spans several rows visually, but only the first cell actually holds the value; the rest are blank. When you save that sheet as CSV, the merge disappears and you are left with one populated row followed by several rows missing their product name. Marketplace importers read every row literally and will either reject the blanks or attach variants to the wrong parent.

Shopify's model is a good mental picture even if you sell elsewhere: variants of one product share a single Handle, the parent data sits on the first row, and each variant row carries its own Variant SKU, Variant Price, and Option values. Shopify's docs add a sharp edge here: if your import includes the Variant SKU column but omits the Option1 Name and Option1 Value columns, the variant option is deleted. So when you flatten merged cells, fill the Handle (or your grouping key) down every variant row, and keep the option columns intact.

BEFORE (merged 'Handle' cell, blanks after CSV export)
  Handle        Option1 Value   Variant SKU   Variant Price
  cotton-tee    Small           TEE-S         19.99
  (blank)       Medium          TEE-M         19.99
  (blank)       Large           TEE-L         21.99

AFTER (Handle filled down; option + SKU columns kept)
  Handle        Option1 Value   Variant SKU   Variant Price
  cotton-tee    Small           TEE-S         19.99
  cotton-tee    Medium          TEE-M         19.99
  cotton-tee    Large           TEE-L         21.99

Matching supplier IDs to your marketplace SKUs

The vendor's SKU is rarely the SKU you list under. Amazon is strict about this: your seller SKU must match what is already in your Seller Central account, and a mismatch throws errors during upload. eBay uses a Custom label (SKU) field that is whatever identifier you assign. Shopify tracks by Handle and Variant SKU. So the supplier's part number is an input to your mapping, not the value you upload.

Maintain a crosswalk: vendor SKU on one side, your marketplace SKU on the other. When a new price list arrives, normalize the vendor SKU column (per the steps above), then map it to your SKU using that crosswalk. Rows that do not match are either genuinely new products or normalization misses; either way they need a human look before they go live, not a silent pass-through.

Discontinued and out-of-stock flags

Price lists communicate availability in ad-hoc ways: a 'DISC' in a status column, a shaded row, a note like 'NLA' (no longer available), or simply a missing price. These are not upload-ready. A marketplace wants a quantity number, and often you would rather zero out or end a listing than create it. Standardize the availability signal into one consistent column before you build the feed, and map the vendor's shorthand to whatever your target platform expects for stock.

Common supplier availability shorthand to normalize:

DISC, DISCO, NLA, D/C -> discontinued: end or skip the listing rather than import it.
OOS, BO (backorder), '0 avail' -> out of stock: set quantity to 0 instead of deleting the product.
A blank or dashed price -> unknown: hold the row for review; do not upload a listing with no price.
'Call for price', 'TBD' -> not sellable yet: exclude until you have a real number.

The full before-and-after

Here is a messy supplier row set and the same data reshaped for a marketplace upload, pulling every step together: SKU normalized, cost mapped away from the price field, currency and separators stripped, pack size resolved, and availability standardized.

BEFORE (supplier price list, as received)
  Item Desc                    Vendor#      Cost      MSRP       Status
   Cotton Tee - Small          ven-tee-s    $9.50     $19.99
  Cotton Tee - Medium          VEN-TEE-M    $9.50     $19.99
  Widget (case of 12)          w-2001       $18.00    $2,499.00  DISC
  Gadget                       g-3003       12,50 EUR 24,99 EUR  OOS

AFTER (marketplace-ready, cost kept internal, not uploaded)
  sku        title              price   compare_at   qty   internal_cost
  TEE-S      Cotton Tee Small   19.99                100   9.50
  TEE-M      Cotton Tee Medium  19.99                 80   9.50
  WIDGET-EA  Widget (each)      2.08                   0   1.50
  GADGET     Gadget             24.99                  0   12.50

Note what changed in that Widget row: the case cost of $18.00 became a per-unit price of $2.08 derived from MSRP, the wholesale cost dropped to an internal column, the case of 12 was resolved to an each, and the DISC status zeroed the quantity for review. That is the difference between a file that lists correctly and one that quietly loses money.

Where PipeSheets fits

Most of this is repetitive cleaning you do not want to redo by hand every time a supplier sends a fresh list. PipeSheets is a CSV and XLSX cleaner where you build a pipeline once and rerun it: trim whitespace on the SKU column, apply an uppercase case transform, use find and replace (with regex) to strip a 'VEN-' prefix or a currency symbol, rename 'Cost' to 'internal_cost' so it can never land in the price field, standardize the availability values, and preview the before and after with detected column types before you download. Because it does not parse SKUs and barcodes as numbers the way Excel does, leading zeros and long UPCs stay intact through the whole pass.

The Quick Clean preset handles the first mile in one click: it trims whitespace across every column, standardizes null-like values (N/A, null, None), drops fully empty rows and columns left behind by merged cells, and normalizes headers to snake_case so 'Vendor #' becomes a clean 'vendor'. From there you save a custom pipeline for the vendor-specific rules and reuse it on every price list that supplier sends. PipeSheets does not map columns to a specific marketplace template for you, so keep that mapping step yours; it handles the cleaning and normalization that has to happen first.

Pre-upload checklist

Run through this before you send the file to Shopify, Amazon, or eBay:

SKU column is text, trimmed, cased consistently, and any vendor prefix stripped or mapped.
Leading zeros and long barcodes are intact (not 4.15E+06 or a shortened number).
The price column is your intended retail, not wholesale cost, verified by a low-to-high sort.
Prices are bare numbers: no currency symbol, no thousands separator, a period decimal.
European numbers (1.299,00) were converted deliberately, not by deleting commas.
Pack sizes are resolved to the unit you actually sell; per-unit prices are consistent.
Merged-cell parents are flattened; the grouping key is filled down every variant row.
Variant option columns are present if you include a SKU column (required on Shopify).
Vendor SKUs are mapped to your marketplace SKUs via a crosswalk; unmatched rows are held for review.
Discontinued rows are ended or excluded; out-of-stock rows are set to quantity 0.
The file is saved as UTF-8 CSV (or Amazon's accepted .txt/.csv) so special characters survive.
You previewed the before and after and spot-checked a handful of rows end to end.

The pattern that makes this sustainable is turning the cleanup into something repeatable. The first time a new supplier's price list lands, you invest in getting the normalization and price mapping right. After that, the same rules run against every refreshed list they send, and the file that reaches the marketplace is correct before validation ever sees it.