WooCommerce product CSV import: shaping a supplier file to the built-in importer’s columns and rules

What the WooCommerce importer expects

A supplier’s spreadsheet arrives with a Category column reading Kitchen/Cookware/Frying Pans on one row and Cookware; Frying pans on the next, for two pans that belong on the same shelf. Neither is what a WooCommerce product CSV import expects. The importer reads > between the levels of a category path and a comma between separate categories, so the path it wants is Kitchen > Cookware > Frying pans.¹

The importer is built in, under Products > All Products, then Import. It takes a CSV or TXT file, matches the columns it recognises to product fields, and adds or updates the products the rows describe; any column it does not recognise is not imported by default.¹ So the work is not the upload. It is shaping the supplier’s file to WooCommerce’s product CSV import schema.¹²

The file should be UTF-8, with 1 and 0 for true and false and commas between multiple values in a cell.¹ Excel is not among the editors WooCommerce recommends, and the page warns that spreadsheet applications “can change formatting or character encoding”, so check the saved CSV before importing it.¹ The whole job is one platform’s version of product data enrichment: take the file as it arrived and return one the store will accept.

The columns, and the values each one accepts

Only two columns are marked required, SKU and Name, and even a missing SKU is not refused: WooCommerce “generates one if this value is omitted”.¹ The rest are optional in that the import runs without them, not in that the product which appears is the one you meant.

Measurements need the closest look. The schema’s only instruction for Weight, Length, Width and Height is “Parse only numbers.”¹ The unit belongs in the header, not the cell: the schema names the column Weight (unit), and WooCommerce’s own sample file has Weight (lbs) and Length (in).³ The unit itself is a store setting, one for weight and one for dimensions, chosen under WooCommerce > Settings > Products > General.⁵

One more rule sits behind the table: a product with no category goes into the default category, Uncategorized unless someone has renamed it or chosen another.⁷

What Update existing products matches on

One option on the upload screen decides what a repeat import does. With Update existing products selected, the importer looks up each row’s ID or SKU in the store: “Existing products that match by ID or SKU are updated. Products that do not exist are skipped.”¹ With it unselected, rows whose IDs or SKUs already exist are skipped instead.¹

That makes the SKU the key to the file. A row without one is not rejected, but the SKU WooCommerce generates for it is one the supplier’s next file cannot match.¹ WooCommerce’s advice for repeat imports is to “keep stable IDs or SKUs”: for a supplier file, a SKU on every row that will not change between deliveries.¹

The trap is a supplier column headed ID. The mapping screen maps recognised columns automatically, and ID is one of the schema’s own names.¹ But the ID column “Identifies an existing product to update”, and the schema on GitHub is blunter: “Defining this will overwrite data for that ID on import.”¹² With Update existing products selected, a supplier’s 1482 that happens to match one of your product IDs updates that product with the supplier’s row.¹ A supplier’s own code belongs in SKU or a meta: column, never in ID.

Where supplier files break it

Supplier files are built for a buyer or a warehouse, not for this importer. Five things in them break it.

• Separators. Categories arrive with slashes or pipes, or split across columns such as Department and Range, where the importer wants one path per category with > between levels.¹ And because WooCommerce “treats commas as field separators”, a comma inside a single value, in a category name or an attribute value, needs a backslash: a category called Places, People, Cities is written Places\, People\, Cities.¹ Mapping supplier categories onto your own tree comes first; the syntax comes second.
• Units in the cell. 1200 g, 1.2 kg and 1.2kg in one Weight column. The importer parses only numbers, so it cannot turn grams into kilograms for you: convert every row to the store’s unit first.¹⁵
• Words where the importer wants numbers. Yes, No and In Stock in columns that take 1 and 0, and prices written as £24.99. WooCommerce’s advice for a supplier file is to remove “formulas, currency symbols, extra whitespace, and unsupported formatting”.¹
• Image links that do not load. A bare file name, a cloud-storage link behind a redirect script, or a link that has died since the sheet was made. WooCommerce copies each image into the Media Library during the import, so it must be directly accessible then; a file name works only for an image already there.¹ 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.
• HTML in descriptions. On some servers, WordPress can mistake a CSV containing HTML for another file type and refuse it with “Sorry, this file type is not permitted for security reasons.” The documented way round is to upload the file to the site’s uploads directory and enter its server path under Show advanced options.¹

One supplier row, before and after

Here is one row for a frying pan as a supplier sent it, set against the importer’s columns. Bold is the value the importer gets; amber is a flag for a person, because no rule settles it.

Weight is the row to check twice, because it can go wrong without looking wrong: strip the g from 1200 g without converting and the pan weighs 1,200 kg. Stock is the honest flag. Plenty is not a number, and a blank Stock cell turns stock management off.¹ Choosing between that and an invented quantity is a decision for a person, not a rule.

Variable products: parent and variation rows

A variable product is one product in several versions, and each variation “can have its own price, SKU, stock level, and other attributes”.⁶ In the CSV, that is one parent row and one row per variation.

The parent row has Type set to variable, its own SKU and every value in Attribute 1 value(s). Each variation row has Type set to variation, its own unique SKU and Name, the parent’s SKU (or its ID, written id:123) in Parent, and only its own value; given more than one, WooCommerce uses the first.¹ Attribute 1 global is 1 for an attribute already set up under Products > Attributes and 0 for one that belongs to the product alone.¹⁷ Generally, WooCommerce says, “it’s preferable to define attributes globally”.⁷

Whether the variations then appear comes down to spelling and price. The importer page says “Keep the attribute name and value spelling identical in the parent and variation rows”, and its own example of what to normalise is L, l and Large, turned into the single value you want.¹ A variation without a price does not show in the store.⁴

Spelling also decides what shoppers see, because each spelling of a value is one more option in the store’s filters. The fix is the same for the filter and the import: normalise attribute values before the file is built.

WooCommerce 11.1 changed two things. With Update existing products selected, a simple product already in the store can become variable in one import if its parent row comes first; earlier versions, or files whose row order is uncertain, need two.¹ And an update can add a variation: a row matching no product becomes a new variation if its Parent is an existing variable product and the row has a SKU and attribute values the parent already has.¹

How to prepare the file, step by step

Eight steps, in an order that stops each one undoing the last. Work on a copy and keep the supplier’s original untouched, as supplier data onboarding sets out.

1. Start from an export. Export a few of your own products and use the file as the template, since a WooCommerce export “already follows the required schema”.¹
2. Decide whether the file adds or updates. New products need no ID column; an update needs each product’s SKU or ID as the store has it, with Update existing products selected.¹
3. Rename the headers and fill the gaps. Use the schema’s exact names (Weight (kg), not Wt), move any column with no home to meta: or drop it, and give every row a Type and a Published value.¹
4. Make every value the importer’s. Yes and No become 1 and 0, currency symbols and formulas come out, and weights and dimensions are converted to the store’s unit before the unit is removed.¹⁵
5. Rewrite categories as paths, with > between levels, commas between categories and a backslash before any comma inside a name.¹
6. Split attributes into their columns, with one value on each variation row and one spelling everywhere.¹
7. Check every image link. Each must be a direct URL that loads, or a file already in the Media Library, with the featured image first. Add alt text after the import; the importer cannot.¹
8. Save as UTF-8 and test small. Back up the store, import a handful of rows on a staging site and read the skipped-row report, then import the rest in batches, staying on the page while each runs.¹

What to do next

Most of that list is not WooCommerce work. It is the shaping every channel asks of a supplier file: one value per cell, one spelling per value, one unit per column, and links that load. RefynData’s product data enrichment service does it before the import: each SKU matched to the real product, items filed into your own category tree, spelling variants folded onto one canonical word, measures converted to one unit and image links fetched, with every value keeping a link to its source. You get a Google Sheet or Excel file, one tab per category, with a quality score that warns but never blocks the export.

Start from an export of your own products, and give every row a SKU that will not change. Test a handful of rows on staging before the rest goes in. Then keep each fix as a rule for that supplier, because the next delivery will arrive with the same slashes, the same grams and the same Yes.