How to clean a supplier spreadsheet before a Shopify import

A supplier spreadsheet may not match what Shopify wants. The mismatches can make an import fail loudly, or worse, succeed and quietly leave you with broken products. This guide walks through what Shopify's own documentation says a product CSV needs, the places where files usually go wrong, and a few checks you can run in a spreadsheet. Everything about Shopify's rules comes from its Help Center page, linked at the end.

Know what Shopify requires

To import new products, Shopify says the only required column is Title. If you add variants for a product, the URL handle column is also required. The first line of the file must be the column headers exactly as Shopify names them, and columns are separated by commas. Save the file as UTF-8 with LF line endings. Shopify offers a sample product CSV you can use as a template; if you do, delete its example products.

Get the handle and title right

The handle is the unique identifier for each product and is used in its page address. Shopify says a valid handle can contain letters, dashes and numbers but no spaces. Because the handle identifies each product, check that every product has one and that no two different products share one.

Structure variants correctly

A product with variants takes one row per variant. Per Shopify, put all the product's fields in the first row and, in the following rows, repeat the handle and leave Title, Description, Vendor and Tags empty while filling in the variant details. A product can have up to three options, such as size and color. If a product has only one option, Shopify says the Option1 name should be "Default Title". Check that every variant has a value for every option, and that no two variants repeat the same combination.

Clean prices and numbers

• Price and compare-at price: include only the number, with no currency symbol, for example 9.99. If Price is empty, Shopify uses 0.00.
• Weight: the weight column is in grams and should be a number with no unit. For example, 4 pounds is entered as 1814.
• Inventory quantity: only applies to stores with a single location. If you have several locations, Shopify points you to its separate inventory CSV.
• SKUs and barcodes: the barcode column is now called Barcodes. A file that contains both the old Barcode column and the Barcodes column fails to import.
• Packed product size: fill in all four packed product columns or leave all four blank. Values in only some of them cause an import error and the product is not imported.

Fix the images before you import

A CSV can only contain text, so every product image must already be on a website. Shopify downloads each image during the import and re-uploads it to your store. The URLs must be publicly accessible, which Shopify describes as behind an https address with no password. The image file name is final once uploaded, and Shopify says to avoid names with _thumb, _small or _medium suffixes. You can add up to 250 images to a product, one row per image. Add alt text where you can.

Set the status to draft while you check

If the Status column is present it needs a value. Shopify's valid values are active, draft and archived. If the column is missing, products import as active. Importing as draft means you can inspect products in the admin before customers see them.

Beware of blanks that overwrite

This one deserves a second read. When you import, you can choose "Overwrite products with matching handles." If you do, then for each matching handle the values in your file replace the existing values. Shopify says that if a non-required column is blank in your file, the matching value in your product list is overwritten as blank. If you leave that option off, products that match an existing handle are ignored during the import. Shopify also says CSV files cannot be used to delete products in bulk.

Checks you can run in a spreadsheet

• Use Excel's duplicate tools to find repeated SKUs and handles. Microsoft's help page explains how to highlight duplicate values and how to remove them. Highlight first and look before you delete anything.
• Use data validation to restrict a column such as Price to numbers in a range, so a typo is caught as you type. Microsoft's help page describes restricting entries to a number or a date range.
• Sort by each column and look at the top and bottom. Blank rows, text in number columns and prices that are ten times their neighbors show up at the edges.
• Import two or three products first, and look at them in the admin before you import the rest.

Where Bash Tail fits

If you would rather not do this by hand, we run SKUForge for Shopify stores. It turns a supplier file into import files and lists the products it held back for review. It creates new products only. The product page lists what it checks and what it does not do.