how-to · · 7 min read
how to update woocommerce stock from a spreadsheet
You counted the stockroom and the numbers are in a spreadsheet. Here's how to get them into WooCommerce with the importer it already has, and the five traps that quietly wreck a stock import.
By Big Bad Agency
Every store does a stock count sooner or later. Someone walks the shelves with a clipboard or a phone, and at the end there’s a spreadsheet of SKUs and numbers. Then comes the slow part: opening 200 products in the WooCommerce admin, one at a time, to type each number in.
You don’t need to. WooCommerce has had a product importer and exporter built in for years, and it updates stock as happily as it creates products. You don’t need a plugin for this. What you do need is to know the handful of ways a stock import goes wrong without telling you.
the short version
- Export your products with only the columns you need.
- Put the counted numbers into the Stock column.
- Import the file back with Update existing products ticked.
- Check five products by hand.
The rest of this post explains each step and the traps between them.
before you start: is stock management on?
WooCommerce only tracks a stock number for products where stock management is switched on. The store-wide switch is in WooCommerce → Settings → Products → Inventory → Enable stock management. Each product (and each variation) can also manage its own stock or not.
If stock management is off, there’s no number to update. A product just says “in stock” or “out of stock”, and that’s all.
step 1: export only the columns you need
Go to Products → All Products and click Export at the top. The export screen asks which columns, which product types and which categories you want.
Don’t export everything. A stock update needs only:
- ID and SKU, which are how the importer finds the product again
- Name, so a human can read the sheet
- Type and Parent, if you have variable products
- In stock? and Stock
A narrow file is easier to fill in, and it also means the import can’t overwrite something you didn’t mean to touch. A full export round-tripped through a spreadsheet can come back with changed descriptions, prices or dates, just because the spreadsheet “helped”.
Variable products export as one row for the parent and one row per variation. Each variation row has the type variation and its parent in the Parent column. Stock usually lives on the variation rows, unless a variation is set to use the parent’s stock.
step 2: put the counted numbers in
Paste or type the counted quantities into the Stock column, matching on SKU. A lookup formula in a second sheet works well. Typing numbers next to product names by eye is how the medium gets the large’s count.
Leave the ID, SKU, Type and Parent cells exactly as exported.
step 3: import with “update existing products”
Back in Products → All Products, click Import and choose your file. Then tick Update existing products. Without it, the importer tries to create new products instead. With it, products that match by ID or SKU are updated, and rows that match nothing are skipped.
On the column mapping screen, check that each column maps to the field you expect. Set anything you didn’t mean to change to Do not import. Then run it.
step 4: check five products by hand
Open five products from the sheet: a simple one, a variation, the first row, the last row, and one whose count was zero. Compare each against the spreadsheet. If those five are right, the rest almost certainly are. If one is wrong, you’ve learned it now rather than from a customer.
the five traps
1. an empty stock cell isn’t “leave it alone”
In WooCommerce’s importer, a blank Stock cell on a product that tracks stock doesn’t mean “no change”. It switches stock management off for that product, and WooCommerce stops counting it.
If you didn’t count something, don’t leave its cell empty. Delete the row from the file instead. A row that isn’t in the file is the only one guaranteed to stay as it was.
2. the import sets the number, it doesn’t subtract
The importer writes the number in the cell, full stop. Suppose you export at 9am, count all morning, and import at 2pm. Any order placed in between has already reduced stock in WooCommerce. Your import then puts the morning number back, so those units are counted twice and you can oversell them.
Count and import during a quiet stretch. Or import only the products you actually counted, soon after you counted them. If orders did arrive in between, adjust those rows by hand before you import.
3. the spreadsheet changes your skus
Spreadsheet apps “fix” values that look like numbers:
- A SKU like
00123loses its leading zeros and becomes123. - A long barcode can come back in scientific notation, like
5.01E+12. - A SKU like
3-4can turn into a date.
The importer then can’t match the row, and depending on what else is in it, the row is skipped or matched wrongly. Format the SKU column as text before you paste anything into it. Save the file as CSV UTF-8, too, so product names with accents survive the trip.
4. “in stock?” and “stock” can disagree
In stock? is the status: 1 for in stock, 0 for out of stock, or backorder. Stock is the number. For a product that manages its own stock, WooCommerce works the status out from the number. It’s still worth not sending a row that says In stock? = 1 with Stock = 0, or the other way round. Fix it in the sheet, where it’s obvious, rather than finding out on the shop page.
5. some stock isn’t in the stock column at all
Some stock isn’t kept in WooCommerce’s stock field. A few setups keep it somewhere else, and a spreadsheet import won’t see it there:
- Variations that use the parent’s stock. Their number is on the parent row, not on the variation rows. Update it there.
- Shared stock pools. Some stores sell one bulk item in several sizes: tea by the gram, fabric by the metre. The real stock there is the pool, not each size. Our free Store Toolkit handles that with a shared pool on the parent product. The pool is its own number, kept by the plugin, and a Stock column in an import does not change it. You set it in the product’s shared inventory tab. Other plugins that do this keep their own number too, so check where yours keeps it before you count.
- Bundles and kits usually work out their stock from the components. Update the components, not the bundle.
when a spreadsheet stops being the right tool
A quarterly count imported from a spreadsheet works fine. If you’re doing it every week because WooCommerce and a supplier, a second channel or a warehouse disagree, the spreadsheet is filling in for a sync. At that point it’s worth a tool that keeps the two in step, so a person isn’t re-typing the gap.
Until then, the built-in importer is enough. Use a narrow file, tick update existing, leave no blank cells, and check five products by hand.