Product Data Cleansing for Shopify: Supplier Sheet to Import File

Map each supplier column to a Shopify CSV column, group rows that are variants of one product under a single URL handle, build SKUs from a written rule, convert every weight to grams, and import ten rows as drafts first. AI is useful for the judgement steps, such as titles, options and grouping. Spreadsheet formulas should do the arithmetic.

The mistake that costs a day is asking the AI to produce the whole import file in one go. It will give you something that looks like a CSV and quietly alters a price, drops a variant or invents a barcode. Split the job in two: the assistant proposes the mapping and the groupings, you apply them with formulas you can see, and every stage ends with a count you can check.

Follow me on Instagram@sagnikteaches

What Shopify's CSV expects, in the parts that matter here

Shopify's help centre sets out the rules. The file must be CSV, with commas between values and the column headers on the first line, saved as UTF-8. For a new product only Title is strictly required, but if the product has variants you also need the URL handle, and any variant-related column (SKU, weight, price per variant) needs Option1 name and Option1 value beside it.

Connect on LinkedInSagnik Bhattacharya

The headers in the current template include: Title, URL handle, Description, Vendor, Product category, Type, Tags, Published on online store, Status, SKU, Barcodes, Option1 name, Option1 value, Price, Compare-at price, Cost per item, Charge tax, Inventory tracker, Inventory quantity, Continue selling when out of stock, Weight value (grams), Weight unit for display, Requires shipping, Fulfillment service, Product image URL, Image position and Image alt text. Older headers such as Barcode still work, but one file cannot hold both Barcode and Barcodes. Always start from a fresh export or the template Shopify gives you, not from memory, because the header names have changed over time.

Subscribe on YouTube@sagnikteaches

Three more rules affect the cleaning. A product can have up to three options (for example colour, size, finish). Images must be public HTTPS addresses that Shopify can download, one row per image, up to 250 a product. And Status is active, draft or archived, with active as the default if you leave the column out, so write the column in and set it deliberately.

A worked job: a kitchen fitter's trade price list

A kitchen fitter wants to start selling cabinet handles and hinges online from a supplier's price list. The sheet has 480 rows and is typical of what suppliers send. Everything below is an illustration.

The last row is the one that causes real damage. If a barcode column has been opened and saved in Excel, long numbers may be rounded to an exponent, and the original digits are gone. Go back to the supplier's original file for barcodes if this has happened, and format the column as text before you paste anything in.

Stage 1: map the columns and write the rules down

Ask the assistant for a mapping table, not a converted file. Paste the header row and five typical data rows, plus the list of Shopify headers you intend to fill.

Here are the column headers and 5 sample rows from a supplier price list
(handles and hinges for kitchens). Propose a mapping to these Shopify product CSV columns:
Title, URL handle, Description, Vendor, Type, Tags, Status, SKU, Barcodes, Option1 name,
Option1 value, Option2 name, Option2 value, Price, Cost per item, Weight value (grams),
Inventory tracker, Requires shipping.

For each supplier column say: which Shopify column, what transformation is needed,
and whether it is a fixed rule (can be done by formula) or needs judgement.
List supplier columns with no Shopify home. Do not convert any rows.

Illustrative output: Code goes to SKU after a transformation; Description is split into Title, Option1 value (size) and Option2 value (finish), which needs judgement; Trade price becomes Cost per item after stripping the symbol and note, and Price comes from a markup rule, which is a formula; Wt becomes Weight value (grams) after multiplying by 1,000; Pack has no direct home and goes into the title or an option. Save this table as the first tab of your workbook. It is the specification, and it is what you hand to a colleague who repeats the job next season.

Stage 2: fixed rules by formula

Anything with a right answer belongs in a formula, because a formula gives the same answer on row 4 and row 480. Four are typical.

• Cost per item: strip the dollar sign and the "ex" note, then convert to a number. In a spreadsheet, a combination of SUBSTITUTE and VALUE does it. Check the result with ISNUMBER and count the failures.
• Weight in grams: kilograms times 1,000, rounded to a whole number. A weight of 0.62 kg becomes 620.
• Price from markup: your rule written once in a cell, for example cost times 2.1 rounded up to the nearest 95 cents. Putting the multiplier in a labelled cell lets you change it for the whole sheet.
• Status: the word draft in every row for the first import.

Check one product by hand. A handle with trade price $31.40 per pack of 10 and a markup of 2.1 gives $65.94, rounded up to $65.95 for the pack. If you want to sell singles, the pack price divided by ten is $6.60 after rounding, and you must decide whether you sell singles at all. Decisions like that are yours, not the assistant's.

Stage 3: SKUs from a written rule

A SKU is your internal stock-keeping code for one sellable variant. Write the rule once, in a sentence, before touching any row: "supplier code without the finish suffix, then a hyphen, then a three-letter finish code, then a hyphen, then the size in millimetres". HND-0032 with brushed brass at 128 mm becomes HND-0032-BRB-128.

The rules that stop trouble later are plain. Every SKU is unique across the whole file. No spaces. No leading zeros lost (format the column as text). The same product always gets the same SKU on the next import, so the supplier's own code goes inside it rather than a running number that changes if rows move. If two supplier rows would produce the same SKU, that is a duplicate to investigate, not to hide by adding a suffix. Count duplicates with COUNTIF and make sure the answer is zero before going further.

Stage 4: titles, options and variant groups with the assistant

This is the judgement work. The sheet says "T BAR HANDLE 128MM BRSHD BRASS" and "T BAR HANDLE 160MM BRSHD BRASS" and "T BAR HANDLE 128MM BLK". Humans can see these are one product with two sizes and two finishes. A model can see it too, if you ask it carefully.

Below are 40 rows from a supplier list. Group rows that are variants of the same product.
For each group return: product title (sentence case, no abbreviations, no size or finish in it),
URL handle (lowercase, hyphens, no spaces), Option1 name and value (Size), Option2 name and
value (Finish), and the original supplier code for each row.
Rules: never merge rows with different pack quantities; if you are unsure, leave the row
alone and mark it CHECK. Return a table, one row per input row, and do not change any number.

[paste rows: code | description | pack]

A reasonable illustrative result is a table where the three T-bar rows share the title "T-bar handle" and the handle t-bar-handle, with Size 128 mm or 160 mm and Finish Brushed brass or Black. Look closely at what you must fix: a model may write the size inconsistently ("128mm", "128 mm"), invent a "Matt" finish that the sheet never says, or give a pack of 10 and a pack of 25 the same handle. Rule it: sizes always "128 mm", finishes only from a list you supply, packs in the title ("pack of 10"). Then use a formula to check that every handle appears with consistent Option names.

Shopify's file layout needs one more thing here. The first row of a product carries the full details: title, description, vendor, tags. Each later variant row repeats the handle, leaves those out and adds the option, price, SKU and image. Build that structure with a formula that flags the first row of each handle and blanks the repeated fields in the rest. Also limit options to three per product, which the help page states as the maximum; a handle with a fourth dimension should be split or the dimension folded into the title.

Before and after: three supplier rows become one Shopify product

Here is what the transformation looks like on the T-bar example, shown in a trimmed form with only the columns that change. This is illustrative data.

BEFORE (supplier sheet)
HND-0032/BR | T BAR HANDLE 128MM BRSHD BRASS | Pk 10 | $31.40 ex | 0.62 kg
HND-0032/BR | T BAR HANDLE 160MM BRSHD BRASS | Pk 10 | $36.80 ex | 0.74 kg
HND-0032/BK | T BAR HANDLE 128MM BLK         | Pk 10 | $29.90 ex | 0.62 kg

AFTER (Shopify columns)
URL handle | Title | Option1 | Option2 | SKU | Price | Cost | Grams | Status
t-bar-handle-pack-of-10 | T-bar handle, pack of 10 | 128 mm | Brushed brass | HND-0032-BRB-128 | 65.95 | 31.40 | 620 | draft
t-bar-handle-pack-of-10 |                           | 160 mm | Brushed brass | HND-0032-BRB-160 | 77.95 | 36.80 | 740 | draft
t-bar-handle-pack-of-10 |                           | 128 mm | Black         | HND-0032-BLK-128 | 62.95 | 29.90 | 620 | draft

Look at what happened. Three rows became one product with a single title. The empty cells in rows two and three are correct, since a variant row repeats only the handle. Option names (Size, Finish) sit on the first row of each product, which is the pattern a fresh Shopify export of a product with variants shows; copy that export rather than guessing. Prices followed the markup rule: 36.80 times 2.1 is 77.28, rounded up to 77.95, and 29.90 times 2.1 is 62.79, rounded up to 62.95. The black finish costs less, so check that the supplier did not mean a different pack size before you accept it.

Units and pack sizes that trip people up

Pack sizes are the most common silent error. "Pk 10" at $31.40 is a pack price, not a unit price. If the supplier sheet mixes columns that are per unit with columns that are per pack, a markup applied to the wrong one prices the product at a tenth or ten times its value. Add a check column that divides the cost per item by the pack quantity and flags any single-unit cost above a sensible ceiling for that product type.

Lengths need the same care. "1.2m", "1200mm" and "120cm" are the same worktop rail, and if they appear as three option values the store shows three variants that are one. Convert everything to one unit in a helper column (millimetres for a trade like this) and let the Option value be built from it. Weights follow the Shopify column name: the value is in grams, and the unit used for display is a separate setting, so a weight shown to customers in kilograms does not change what you enter.

Stage 5: descriptions, tags and the rest

Supplier descriptions are often thin or copied from a catalogue, so the pass that writes your own is worth doing, but only after the structure is right. Ask for plain descriptions built from the facts in the row and nothing else ("do not add features, dimensions or claims that are not in the data"). Tags and product type follow your own list. Categories are a separate pass with their own check; categorising against Shopify's taxonomy covers it, and a CSV can carry the category in the Product category column as either a breadcrumb or an ID, never both.

For wording that sells, the tutorial on writing Shopify product descriptions with AI picks up where this one stops. Keep the import file's description neutral at first; you can improve it in a later update without touching variants.

Stage 6: the ten-row test import

Never import 480 rows to find out the file is wrong. Cut a test file of ten rows that covers every kind of row: a simple product with no variants, a product with two options, a product with three images, one with a very long title, one with no weight, one whose barcode starts with zero, and one pack-quantity product.

1. Save the sheet as CSV (UTF-8, comma-delimited). Shopify's help page asks for UTF-8 with LF-style line endings; in Excel use the CSV UTF-8 option, and if the import complains, re-save the file from Google Sheets or from a plain-text editor set to LF endings. Then open the result in a plain-text editor and look at the first three lines. Quotation marks around descriptions containing commas are correct; stray semicolons (a sign the file was saved with a different list separator) are not.
2. Check that the header row exactly matches a fresh Shopify export or template.
3. In the admin, import the file. Leave "Overwrite products with matching handles" unticked for a first load into an empty catalogue. With it unticked, rows matching existing handles are ignored.
4. Open each of the ten products. Check the title, the variants and their prices, the SKU on each variant, the weight (grams), the Status and the images.
5. Write down every difference. Fix the cause in the sheet's rule, not in the admin by hand, then reimport the test file after deleting the ten test products.

An illustrative first test turns up three faults: barcodes with the leading zero dropped on two rows; a blank weight on a product that is shipped, which Shopify records as 0 grams, so shipping rates are off; and all ten products set to active because the trimmed test file had lost its Status column, and Shopify treats a missing Status column as active. Each one has a one-line fix in the workbook. The point of ten rows is that these cost twenty minutes, not an afternoon of cleaning up 480 live products.

Updates later: the overwrite trap

The same file can update products once they exist. Tick "Overwrite products with matching handles", and rows with matching handles update the product. The help page warns that blank cells in non-required columns you include clear the existing values, while columns you leave out entirely keep theirs, and that a file missing the option columns can delete existing variants. So a price update should be a thin file: handle, Title, Option1 name, Option1 value, SKU and Price, nothing else.

Take an illustrative case: the supplier raises trade prices by 4 per cent in January. Re-run only the price formula in your workbook, export a file with those six columns, and import it with overwrite on. Before you do, import two products from it and look. If the supplier's change moves your margin enough to matter, compare quotes from another supplier before applying it.

When the supplier sheet is worse than usual

Merged cells and notes in the middle of the data

Unmerge, fill down and delete note rows first. A section header like "Brass handles" sitting in the code column must become a "Range" column on every row beneath it, or the product line is lost. This is plain tidying, so the guide on cleaning messy data is a good companion.

A PDF price list

Extraction from a PDF introduces errors that look like valid numbers. Convert, then check the row count and the total of a numeric column against the PDF's own totals before anything else.

A nursery's craft supplies, or a brewery's ingredients

Products sold by weight or volume need a decision up front: sell the 25 kg sack or sell by the kilogram? The simplest pattern is one product with a unit-size option (1 kg, 5 kg, 25 kg), each size its own variant with its own SKU, price and weight in grams, rather than a per-kilogram price the customer has to multiply. Decide that in the specification tab before the assistant touches the titles.

A checklist to run before the full import

• Row count in equals row count out, with every dropped row explained.
• Zero duplicate SKUs and zero duplicate handle-plus-option combinations.
• Every price is a number; none is zero or below cost.
• Every weight is in grams; products that ship have one.
• Status column present and set to draft.
• Image addresses open in a private browser window without a login.
• The ten-row test passed and was inspected product by product.

When the full file goes in, import it in batches of a few hundred rows so a fault stays easy to trace. Switch confirmed products to active in bulk from the product list afterwards, and keep the specification tab with the workbook: it is the real asset, since next season's sheet will be messy in the same ways.

Supplier-sheet import questions

Can I upload a supplier's Excel file straight into Shopify?

No. Shopify imports CSV files only, saved as UTF-8 with comma separators and the column headers it expects on the first line. Shopify's template uses its own headers, so a supplier's columns have to be mapped and renamed first, and the file saved as CSV.

What happens if I leave out the option columns?

Variant-related columns such as SKU or weight need Option1 name and Option1 value alongside them. If both option columns are left out, Shopify creates a default variant and, when overwriting existing products, deletes the existing variants. Always include them for any product that has variants.

Should the first import be set to active?

Set Status to draft for the first import of a new catalogue. Active is the default if the column is missing, which would put unchecked products on sale. Review a sample in the admin, then switch the confirmed products to active.

How many products can one AI batch clean?

Keep batches small enough to check, usually 30 to 60 rows. Longer runs drift: inconsistent handles, dropped rows and quietly changed numbers. Use AI for the judgement work and a spreadsheet formula for anything that is a fixed rule, such as the price markup.

Further reads

• WooCommerce to Shopify Product Import: Variants and Redirects With AI — Moving an existing shop's products and redirects across.
• Product Data Enrichment for Industrial Catalogues: AI Fills Gaps — Fill the attribute gaps a supplier sheet leaves behind.
• AI Supplier Negotiation Emails for Importers: Price, MOQ, Terms — Negotiate the price list before you import it.
• AI Supplier Management: Track Prices, Lead Times, and Risk — Keep supplier prices and lead times under review.
• AI Invoice Processing: Stop Typing Supplier Bills by Hand — Handle the supplier bills that follow the products.
• Stocky Alternative for Shopify: Purchase Orders and AI Reordering — Reorder stock once the catalogue is live.
• Line Sheet Template: Turn a Product Export Into a Wholesale PDF With AI — Export your products, let AI add wholesale prices, MOQs and case packs in a sheet, then check the maths and produce a PDF buyers trust.
• Product Bundle Pricing: Set a Discount That Still Clears Your Margin — A worked margin sheet for a six-item bundle: costs, card fees, shipping, a discount ladder and how to test it against single-item orders.
• Missing GTIN in Google Merchant Center: What AI Can and Can't Fill — AI must never invent GTINs. Where to get real ones, when identifier_exists is the right answer, and how to check a feed.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Shopify help centre page on importing and exporting products with a CSV file, and the Shopify Standard Product Taxonomy page.