WooCommerce Product CSV Import: Clean Supplier Data With AI First

Clean the supplier file before WooCommerce sees it: one header row, UTF-8, one SKU per sellable item, sizes and colours spelled one way, prices as plain numbers, and variations tied to a parent by SKU. Use AI to design the rules on ten rows, apply them in the spreadsheet, then import only those ten rows on staging.

The importer is strict but not clever. It does exactly what the columns say, so a supplier file with "L", "Large" and "lrg" produces three separate size values and a broken variable product. AI is good at spotting those inconsistencies and writing rules for them. WooCommerce is where you prove the rules worked.

Follow me on Instagram@sagnikteaches

What a typical supplier file looks like

An illustrative case: a small shop selling workwear and gloves buys from a distributor that sends a spreadsheet every season. The file has these headers: Item No, Desc, Colour/Size, Cost, RRP, Stock Qty, Img, Cat. A few rows, as they arrive:

Connect on LinkedInSagnik Bhattacharya

Count the problems in four rows: shouting capitals and inconsistent title style, a currency symbol and a space inside the cost, colour and size jammed in one cell with three different spellings of size, an empty stock cell that could mean zero or unknown, an image file name with no path or address (usable only if that file is already in your media library), and categories separated by a pipe character with inconsistent spacing. A real file has the same faults across several thousand rows.

Subscribe on YouTube@sagnikteaches

The target: the exact columns WooCommerce reads

Before cleaning anything, know what you are cleaning towards. These are the columns from WooCommerce's product CSV schema that matter for a first import of simple and variable products. Names must match what the importer recognises, or you map them on the column mapping screen.

The importer expects a CSV saved as UTF-8, with commas as the field separator, and wants any value containing a comma wrapped in double quotation marks. A category whose own name contains commas needs each comma escaped with a backslash, as in WooCommerce's own example Places\, People\, Cities. Images must be reachable on the web or already in the media library, and the core importer cannot set image alt text.

Writing cleanup rules with AI, on ten rows

Pick ten rows that cover the mess: two uppercase titles, a row with no stock figure, one with a currency symbol, one combined colour and size cell, one with no image, one single-size product. Paste only those into the assistant and ask for rules, not for a cleaned file.

Below are 10 rows from a supplier spreadsheet. I will import them into WooCommerce via its product CSV importer. Do not clean the rows yet. Instead, write a numbered list of cleanup rules I can apply to the whole file, covering: one consistent case for names, splitting the Colour/Size cell into two fields, normalising size values (L, l, Large, lrg all become Large), stripping currency symbols and spaces from price columns, treating blank stock as unknown not zero, converting the Cat column to WooCommerce category format (comma between categories, > for hierarchy), and what to do with rows that have no image. For each rule, give one before and after example from my rows. List any value you cannot normalise with certainty, and any rule where two readings are possible.

[paste 10 rows]

An illustrative reply gives rules such as: "1. Names: sentence case, keeping the product line name capitalised (GRIP PRO WORK GLOVE - leather palm becomes Grip Pro work glove, leather palm). 2. Split Colour/Size on the slash and trim spaces. 3. Size map: l, L, Large and lrg become Large; xl and XL become XL. 4. Prices: remove $ and spaces, keep two decimals. 5. Stock: blank stays blank; do not enter 0." Under ambiguities it lists the helpful questions: does "One size" become a size attribute or no attribute? Is the pipe in the Cat column a separator or a hierarchy? Those are decisions for you, not the model.

Answer them and write the final rules into a "Rules" tab in the spreadsheet. That tab is reusable: the next season's file goes through the same ones, and the time you save accumulates there.

Applying the rules to the whole file

With rules fixed, apply them with tools that behave the same way on row 8,000 as on row 8. In a spreadsheet that means formulas: TRIM and PROPER or sentence-case formulas for names, a lookup table for the size map, SUBSTITUTE to strip the currency symbols, SPLIT or text-to-columns for the colour/size cell. If you prefer, ask the assistant to write a short script or a Google Sheets formula for each rule and test it on your ten rows. Check its output against your ten examples before running it on the full file.

Keep the original sheet untouched and build the WooCommerce version on a second tab, so every cleaned cell can be traced to its source. For a broader look at cleaning data in a sheet, see cleaning messy data with AI.

Building SKUs and variation rows

Supplier item numbers often encode product, colour and size, as in WG-100-BLK-L. That is helpful: the parent SKU is the part before the colour and size (WG-100), and each child keeps the full code. If the supplier does not provide a clean code, design a pattern and apply it everywhere, always uppercase, with hyphens, no spaces.

For this glove, with two colours and sizes L and XL, the variable product has a parent row and four child rows. In one attribute scheme, colour and size are two attributes. Here is what the finished rows look like, with the long text columns left out:

The remaining visible and global columns follow the pattern: Attribute 1 visible is 1 on every row and Attribute 1 global is 0 for a local attribute, repeated for attribute 2. Attribute names and values must be spelled the same in the parent and child rows. WooCommerce's own guidance for supplier files stresses exactly this: remove extra spaces and use one capitalisation for each value.

Rows the rules cannot settle

Whatever the assistant concludes, some rows need a human. Collect them in a "Review" tab with a reason, instead of fixing them silently. The common groups:

• Two supplier rows with the same item number but different descriptions.
• Products with no price or a cost above the RRP.
• Sizes outside your map ("XS/S", "10-12", "Jumbo").
• Images that return an error when you open the address, which WooCommerce's guidance notes can happen when hosts block requests or serve a page instead of the image.
• Stock figures that are negative, or text such as "TBC".

Each one gets a decision: fix it, import it as a draft or leave it out. A hundred rows left out of a first import is not a disaster; a hundred wrong rows live on your shop is.

The dry run: ten rows on staging

1. Back up the store. A full backup through your host or a backup plugin, taken just before the import.
2. Use a staging copy or test site. A copy of the shop where nothing is visible to customers.
3. Choose ten rows with variety. One simple product, one variable parent with its variations, one product with no image, one with a sale price, and one with a long description containing commas and quotation marks.
4. Set Published to -1 (draft). Nothing goes public while you check.
5. Save as a UTF-8 CSV and reopen the file in a plain text editor to see the real contents. Spreadsheet software can change formatting, so look at the saved file before importing.
6. Go to Products, All Products, then Import. Choose the file, continue, and read the column mapping screen. Recognised columns map automatically; check each dropdown, and set anything unmapped to Do not import on purpose.
7. Run the importer and wait without navigating away.
8. Open each imported product. Check the title, description, categories, price, stock, images, and for the variable product, that all variations exist with the right attributes and prices.

Keep a short log: what you expected, what you got, what you changed in the rules. When all ten match, scale up to a hundred rows, then the rest, in batches. Large files can exceed the server's memory, upload limit or processing time, which is why WooCommerce suggests splitting big CSVs into smaller batches.

Updating products already in the shop

For later supplier files with new prices or stock, tick Update existing products. WooCommerce matches rows by ID or SKU, so stable SKUs matter. Export a few existing products first, compare the headers and identifiers with your file, and keep only the columns you intend to change. Columns the importer does not recognise, or that you set to Do not import, are not imported, so an update file with only SKU, Regular price and Stock should change nothing else; open two updated products on staging and confirm it did not. From WooCommerce 11.1, a simple product can become a variable one, with its new variations, in a single import, provided the parent row comes before its variation rows; on older versions it takes two imports, parent first. Check the importer's current documentation for the version you run, because behaviour has changed between releases.

Five cleanup cases and what each rule looks like

• Combined dimensions. A cell reads "30 x 20 x 10 cm". Split into three numeric columns, Length, Width and Height. Leave the unit text out of the cell.
• Prices with a tax note. "6.40 + tax". Decide whether your prices include tax, then keep only the number and record the rule in your Rules tab.
• Multiple images in one cell. Addresses separated by semicolons. Replace the separator with a comma, put the best front-facing image first, and make sure each starts with https.
• Pack quantity in the name. "Box of 12" inside the title for some products and in a separate column for others. Choose one place, usually the name for shoppers and an attribute for filtering, and apply it everywhere.
• Brand in the title. Some rows start with the brand, some do not. Move the brand to a column (or a brand taxonomy if your theme has one) and keep titles in the form the shop wants, such as type first, then model.

Reading the saved file before WooCommerce does

Open the CSV in a plain text editor, not the spreadsheet, and look at the first three lines. This is what the importer will actually read. An illustrative extract of the glove file, cut down to the Size attribute to fit the width:

Type,SKU,Name,Published,Regular price,Categories,Images,Parent,Attribute 1 name,Attribute 1 value(s),Attribute 1 visible,Attribute 1 global
variable,WG-100,"Grip Pro work glove, leather palm",-1,,"Gloves > Work gloves, Safety",https://example.com/wg100blk.jpg,,Size,"Large, XL",1,0
variation,WG-100-BLK-L,Grip Pro work glove - Large,-1,14.99,,,WG-100,Size,Large,1,0

Three things to look for. First, any name or description with a comma is wrapped in double quotation marks, as the first row shows. Second, there are no stray quotation marks, curly apostrophes turned into odd symbols or a line of nothing but commas at the bottom, which spreadsheets sometimes add. Third, every row has the same number of fields as the header. If you see a row that runs on or breaks mid-line, a description probably contains a line break that the spreadsheet did not quote correctly.

You can also give the assistant the first five lines of the saved file and ask: "List every value here that would break a WooCommerce CSV import, using the column rules I pasted earlier." It reads text well, and this is a good use of it because the input is small and the task is checking, not transforming.

Blank stock and zero stock are different things

This catches many first imports. In the WooCommerce schema, a numeric Stock value turns on stock management for the product, and a blank leaves it off. If your supplier's blank cell means "we do not know", importing it as 0 would mark an item that might be available as out of stock, and putting 999 would invite oversells. Decide per supplier. For a distributor that updates its file weekly, treat blank as "not managed", set In stock? to 1 and plan a weekly stock update. For a distributor whose blank means "none", set Stock to 0 and In stock? to 0. Write the choice in the Rules tab, and re-read it when a new supplier joins.

What the cleanup is worth in time

A rough sum, for an illustrative file of 1,200 supplier rows covering 300 products with variations. Fixing each row by hand at 40 seconds is about 13 hours. Designing rules on ten rows with an assistant takes around 30 minutes, applying them with formulas about an hour, reviewing flagged rows perhaps 90 minutes, and the staging dry run another hour. That is about four hours, and next season's file, run through the same Rules tab, may take 90 minutes. The saving is mostly not the first file but every file after it, and the rules tab also documents how your shop treats supplier data for whoever takes over.

The variation that vanished

An illustrative mistake: a shop imported a hundred variable products and found that thirty of them showed no size options. The cause was the supplier file's habit of writing "XL" in some rows and "Xl" in others, so the parent row listed S, M, L, XL while some children carried Xl, which did not match. WooCommerce's documentation says a variation's attribute values must match the parent's options, and those mismatched variations were never created. A ten-row test on one product with a clean size column had passed, so nobody saw it. The fix was a size map applied to the child and parent columns in one pass, then rerunning those thirty products. The lesson is to put an awkward product in the dry run, not a tidy one.

Where AI belongs in this job, and where it does not

Use it to write rules, to flag ambiguity, to draft formulas and to review a sample of the finished CSV: "Here are ten rows of my final file; list any value that does not match the WooCommerce schema I pasted." Do not use it to compute prices, generate SKUs one by one, or transform several thousand rows in a chat window. Models are inconsistent over long tables, they sometimes alter a digit while "tidying", and a changed digit in a SKU or price is the hardest fault to find afterwards.

Once products are live, the same approach extends to the text on each page. The tutorial on bulk WooCommerce product descriptions with AI picks up from the product import, and the one on WooCommerce SEO and schema checks covers the titles and meta fields. If your catalogue is large and the supplier sends files often, a CSV-driven description workflow shows how to treat rows as a batch.

WooCommerce CSV import questions

Can I paste my whole supplier file into an AI chat?

For a handful of rows, yes. For thousands, no. Long tables hit length limits and models drift partway through. Use the AI on a ten-row sample to design the cleanup rules, then apply those rules to the full file with spreadsheet formulas, find-and-replace or a short script the assistant writes for you.

Does WooCommerce create SKUs if my supplier file has none?

Do not count on it. The current WooCommerce documentation says the importer does not automatically generate a SKU for a blank CSV value, so create stable SKUs in the file first. A supplier's own item number, or a pattern you design, is better than leaving it to chance.

Should I import on the live shop first?

No. Take a backup, test on a staging copy or a test site with one parent product and a few variations, and only then import the rest. WooCommerce's own guidance recommends a small test on staging before importing a large catalogue.

Why did my import skip rows?

The usual causes are an existing SKU or ID without 'Update existing products' ticked, a column or value that does not match the schema, a variation whose Parent value does not match, and a file too large for the server in one go. Check the importer's log, correct the file and rerun a small batch.

Can the CSV importer set image alt text?

No. WooCommerce's documentation says the core importer cannot add, edit or update alt text for product images. Set it afterwards in the media library, or use a plugin built for it, and keep the images themselves reachable by the importer.

Further reads

• AI for WooCommerce Stores: Descriptions and Support Replies — Descriptions and support replies for a WooCommerce shop.
• Clothing Product Descriptions: A Checklist of Fabric, Fit and Care Errors — Check size and fabric facts in apparel copy.
• WooCommerce to Shopify Product Import: Variants and Redirects With AI — The reverse job: moving products out of WooCommerce.
• Best AI for Ecommerce Operations: Orders, Stock, Returns, Suppliers — Keep stock and supplier data tidy after launch.
• AI Supplier Management: Track Prices, Lead Times, and Risk — Track the supplier prices and lead times behind the file.
• Best AI Ecommerce Store Builder: Shopify, WooCommerce or BigCommerce? — Check WooCommerce is the right platform for the catalogue.
• AI Chatbot for WooCommerce: Costs, Order Lookup and a Test Script — Plugin or hosted widget, per-conversation pricing, order lookup with read-only keys, and a policy test script to run before launch.
• Product Categorisation With AI: Fixing Shopify Taxonomy at Scale — Fix uncategorised Shopify products in bulk: shortlist, batch prompts, validate against the official list, audit a sample, then import only the category column.
• AI Assistant for WooCommerce: What Plugins Do and What to Review — What third-party AI assistant plugins do in a WooCommerce store, what they can see, and the checks to run on a staging copy first.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: WooCommerce documentation on the Product CSV Importer and Exporter and the product CSV import schema.