Shopify product CSV import: shaping a supplier spreadsheet into a file that imports cleanly

What a Shopify product CSV import expects

A supplier spreadsheet is one row per product: a code, a name in capitals, a price with a pound sign, a weight typed as 0.9 kg, a barcode Excel shows as 5.01235E+12 and a link to a thumbnail. A Shopify product CSV import expects another shape: a UTF-8 file of no more than 15 MB whose first line holds Shopify’s column headers exactly, with one row per variant, one row per image and a handle holding each product’s rows together.²¹ Getting from the first shape to the second is the whole job.

Very little of it is compulsory. To create products, “the only required column is Title”. URL handle is required as well when a product has variants, and a file that updates products needs both.¹ Leave any other column out and Shopify fills in a default where it needs one: the price becomes 0.00, the weight 0 and the status active, and the product is published to the online store.¹ So a Title column alone puts a product on sale with no vendor, no category, no weight and a price of 0.00.

The limits Shopify publishes are on the file and on each product; its help pages give none for rows. A file that errors or times out is split into smaller files and uploaded one by one.²

The columns that matter, and their older names

Shopify’s sample file has 57 columns, and its header row begins “Title,URL handle,Description,Vendor,Product category,Type,Tags,Published on online store,Status,SKU,Barcode”.⁶ A supplier file maps onto perhaps twenty of them.

The header row printed on Shopify’s troubleshooting page uses older names, starting “Handle,Title,Body (HTML),Vendor,Type,Tags,Published”.³ Shopify “maintains backward compatibility with older column names” while suggesting a move to the current ones.¹ The table pairs the two by what each column holds.

Three details catch people out. The sample file calls the barcode column Barcode and the column reference calls it Barcodes, and a file with both “fails to import”.⁶¹ The Google Shopping columns for gender, age group, MPN, condition and custom labels are metafields an app might read; Shopify’s own Google and YouTube channel “doesn’t use these metafields”.¹ And Collection is “the only column that you can add to the CSV file that doesn’t break the format”.¹

Headers must match exactly, capital letters and all: a mismatch, even extra spaces at the end of the first line, fails the import or opens a Choose column headings window.²³

Overwriting: what a handle match replaces, and what it deletes

Supplier data onboarding explains the Overwrite products with matching handles box. In short: with it clear, products whose handle already exists are “ignored during CSV import”. With it ticked, “Existing values will be replaced for all columns included in the CSV”, so a blank cell writes a blank over the store’s value and a column left out of the file leaves that value alone.¹²

Three more rules on Shopify’s pages go further.

• Variant columns need the option columns. Update a column such as SKU or Weight value (grams) and “you must also include the Option1 name and Option1 value columns”. Without them, “a new default variant is created and existing variants are deleted”.¹
• New option values mean new variants. Changing the Option1, Option2 or Option3 value columns “deletes existing variant IDs, and creates new variant IDs”, which “can break third-party dependencies on variant IDs”.¹
• An import cannot be stopped. Imports “can’t be canceled once they begin, and you can’t view a history of past imports”. Shopify’s advice is to back up your product data first; the store’s activity log shows what an import changed.²

Where supplier files break the import

Each of these faults arrives in the supplier’s spreadsheet, not at the import.

Handles that split one product into several. Shopify groups a product’s rows by handle. A supplier file has none, so the handle gets made from the title, and supplier titles carry the size: FITTED SHEET 400TC WHITE KING, FITTED SHEET 400TC WHITE DOUBLE. Each size then becomes a product of its own. Make one handle per product, “letters, dashes, and numbers” with no spaces, and repeat it on every row.¹ Two products under one handle is the opposite fault, and Shopify’s fix is to “Make sure you have a unique handle for each product in your CSV.”³

Prices with a currency symbol. A supplier’s price reads £34.99, or 34,99 from a supplier on the continent. Price, Compare-at price and Cost per item share one rule, “Only include the monetary value without a currency symbol”, and Shopify’s example is 9.99.¹

Barcodes the spreadsheet has rewritten. Excel “automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15”, which is how a 13-digit EAN comes to read 5.01235E+12.⁷ To record which standard a code follows, Shopify takes the type and a colon in front, as in gtin:5012345678900; a barcode with no type “is imported as a Custom barcode”.¹ GTIN, EAN, UPC and MPN explained covers getting the digits back.

Weights in the wrong unit. Supplier weights arrive as 0.9 kg, 900g or 2 lb. Shopify’s weight column is in grams and takes “only the numerical value, without the unit of measurement or decimals”, so 5.125 kilograms goes in as 5125.¹ Shoppers see the unit set in Weight unit for display, kg by default.¹

Images that will not download. Shopify “downloads the images during the import and re-uploads them in your store”, so each link must be public, on https and without a password.¹ A missing file fails with “An error occurred while trying to download the image”. A mistyped link fails too, and Shopify’s help for that error says the link “needs to be a publicly accessible direct link to the image”.³ Shopify also asks for file names without _thumb, _small or _medium suffixes.¹ In a supplier file, those suffixes mark a smaller copy where the original should be.

On one of our recent jobs, for a musical instrument retailer, we fetched all 12,015 image links on the catalogue before the file was built, and 3,566 came back broken or blocked. Nearly a third of them. The import never saw one. Google Shopping image requirements sets out the audit.

Option values and categories that do not match. A supplier cell reading Size: M / Colour: Black becomes two options: Size with the value M, and Colour with the value Black. Two variants left with the same values fail with “options are not unique”.³ Product category must match Shopify’s taxonomy by “either the category breadcrumb or the category ID exactly”, so a supplier’s own Bedding fails as “Not a valid product category”.³

A supplier row, before and after

Here is one supplier row for a king-size fitted sheet, before and after. The sheet comes in three sizes, so it becomes three rows under one handle; the table shows the first.

The Double and Single rows carry the handle, their own option values, SKU, barcode, price and weight, and nothing in Title, Description, Vendor or Tags, as Shopify’s instructions for a product with variants set out.¹ Each further image needs a row of its own, with the handle and the image link.¹

Shaping the file, step by step

1. Start from Shopify’s header row, not the supplier’s. Export one product from your store, or download the sample file, and keep its first line exactly.²
2. Map each supplier column to one Shopify column, and keep the map for the next file from the same supplier.
3. Leave out what you have nothing for. An absent column is left alone on an existing product; a present, empty one is written as empty.¹
4. Decide what is a product and what is a variant. One handle per product, on every row; one row per variant, with its own option values, SKU, barcode, price, weight and stock.¹
5. Write values the way Shopify reads them. Prices without a symbol, weights in whole grams, a Product category from Shopify’s taxonomy, and Inventory tracker set to shopify, or left blank if you do not track stock.¹
6. Keep barcodes as text. Format the column as Text before the codes go in, since the format “will only affect numbers that are entered after the format is applied”.⁷ Then add the type, and use Barcodes or Barcode, never both.¹
7. Give each image its own row, with a public https link to the full-size file and an Image position counted from 1. Fetch every link before the import, not after.¹
8. Save as UTF-8 with LF line endings and straight quotes, and do not sort the file afterwards. Curly quotes, which a spreadsheet program can add, cause quoting errors, and sorting in Excel or Numbers “might cause your products to be removed from their relevant image links”.¹³²
9. Import a few products first. Shopify tells Partners to “test a small subset of changes first using a dev store”. Tick the overwrite box only for a file built to update.²

When it is not obvious

One product or several? If a shopper chooses between the values on one page, they are variants: a sheet in three sizes and two colours is one product with six variants and two options. Shopify allows 2,048 variants per product, but “Some third-party themes might not support more than 100 variants”, and the same goes for some apps and sales channels, so check yours before importing hundreds.⁴ Things that only share a name, such as a bath and its panel, are separate products with separate handles; keeping parts apart from the products they fit is part of product data enrichment.

When a new file reorders the options. Moving Size from Option1 to Option2 and putting a new option first, as a supplier file that lists Colour before Size can, fails with “Line is invalid (No details)”. Shopify’s workaround is a temporary name, such as Size with a full stop after it, renamed in a second import.²

Before you click Import products

All of this happens in the spreadsheet, before Shopify sees a row, and it is what RefynData’s product data enrichment service does with a supplier file. Each SKU or barcode is matched to the real product, gaps are filled with a link to the source of each value, measures are converted to one unit, spelling variants are folded onto one value, and every item is filed into your own category tree. The full-size original behind each thumbnail is found, and the result is a Google Sheet or Excel file, one tab per category, ready to import.

Whichever way the file is built, export your products first, as the backup Shopify asks for, and import five products before five hundred. Then keep the map from the supplier’s columns to Shopify’s: the next delivery will bring the same pound signs and the same thumbnails.