Pasting a list from a spreadsheet

A supplier's price list or stock sheet can be brought into the Products editor by SKU. Press Paste a list, the clipboard button beside the download button on the Products toolbar (point at it, or reach it with the keyboard, and it says "Paste a list of SKUs and new prices or stock from a spreadsheet"). It is also in the right-click menu and in Bulk tools on Products (select any cell first), by name: Paste a list…. Orders, Coupons and Reviews do not have it.

1. Column to change: Stock, Stock status, Regular price, Sale price or Cost of goods. Only columns showing in the grid are offered, so show the one you want in Display first. Cost of goods is there only while WooCommerce's cost of goods feature is on.
2. In your spreadsheet, select the SKU column and the column with the new values, copy, and paste into Pasted list. One product per line. A spreadsheet copies its cells separated by tabs, which is what the box expects. Lines typed by hand can be SKU,value instead; a value that contains a comma then has to be in quotes (QA-1,"12,50"), and a line with more than two values is left out, because a comma inside 1,200 would split one number in two. Or press Open a file… under the box and choose the supplier's CSV file: its rows fill the box (see Opening a supplier's CSV file, below).
3. Whenever a spreadsheet paste has two columns or more, choose SKU is in and New value is in. The SKU does not have to come first, and the two columns you need do not have to be side by side. When the first line says SKU, that column is picked for you (a sheet headed Price, SKU is read the right way round), even if you chose another column for a list pasted before it. Without such a line the first column is taken as the SKU, so change SKU is in when your SKUs are not first. Choosing the column the other one is on moves the other one, and the dialog says where it went. Lines typed by hand with commas are always the SKU first.
4. Press Check the list. Nothing is saved yet.

Every product and variation with a matching SKU is checked, whatever the filters show. A variation is found by its own SKU. Drafts, pending, scheduled and private products are included, as they are in the grid; products in the Trash are not. SKUs are compared the way your database compares them, which on almost every WordPress site ignores letter case and accents, so qa-001 finds QA-001 and MULLER-1 finds MÜLLER-1, whatever language the site is in. The database also decides which line each product it finds belongs to, so a product it matched is never reported as having no SKU match.

On a shop translated with WPML (with WooCommerce Multilingual) or Polylang for WooCommerce, a product and its translations share one SKU. They count as one product: the one in the language the editor lists, or in the default language while it lists all languages. That is the row a change typed into the grid would go to, and what happens to the translations afterwards is up to the translation plugin, as it is for any edit made in the grid. Two products that are not translations of each other still count as more than one.

Values are read the way your store writes them (WooCommerce → Settings → General): its decimal separator and thousand separator, with the currency symbol allowed in front or behind. A value written any other way is refused rather than guessed: on a store that writes 1,234.50, 12,50 is not read as 1250, and the line says how the store writes prices. Stock is a whole number (below 0 is allowed, as WooCommerce keeps backorders), unless a plugin has told WooCommerce to count stock in decimals. A stock figure grouped differently from the way your store writes numbers (1,234 on a store with no thousand separator) is refused with an example of how the store writes it. A stock status is In stock, Out of stock or On backorder, in any letter case, in the language your WordPress admin is shown in, or WooCommerce's own words instock, outofstock and onbackorder. A blank value never clears a price or a stock figure: that line is left out.

The check shows Will change, Already that value and Left out, then every change as Now and New, then the lines left out, grouped by the reason, each group with its number of lines. "Nothing to change" appears only when every product the list reached already has its value:

• No product has this SKU.
• More than one product has this SKU: both are named, and neither is changed. Give them different SKUs first. (A product's translations are not counted twice; see above.)
• On more than one line: two lines reach the same product (the same SKU twice, or in two letter cases). Neither is used, because which one you meant cannot be told.
• The value could not be read, with what the store expects.
• Not available for this product type: a price on a variable or grouped product (a variable product's variations have the prices), or stock on a grouped or external product.
• Your account cannot edit these products.
• Stock is handled another way on these products: stock that is not tracked (switch on Manage stock first, or the figure would be thrown away on saving), a variation that uses its parent's stock (paste the parent's SKU), a stock status on a product whose stock is managed (WooCommerce sets it from the stock figure), or a stock status on a variable product (it comes from its variations).
• Sale price not lower than the regular price: a sale price at or above the product's regular price, or a regular price at or below its sale price.
• Skipped lines: a first line that is the list's column names, a line without a SKU or a value, a line that does not reach the column you chose, or a comma line with more than two values.

Apply changes saves the changes through the same save as the grid, twenty products at a time with the progress in the toolbar, and the grid then shows what is stored. The message says how many products were updated and how many lines were left out, and its Undo puts every one back; the run is also in History, where it can be viewed and reverted like any save. Back returns to the pasted list with your choices kept. Save or discard your own edits in the grid first: the dialog does not open while there are unsaved changes.

A list can have up to 5,000 lines; it is checked two hundred lines at a time. The check reads the values as they are at that moment: if somebody changes one of those products before you press Apply changes, your pasted value is saved over theirs, the same last-save-wins rule as the grid. The pasted list itself is not kept anywhere; History keeps only the previous values, as it does for every save.

Opening a supplier's CSV file#

Open a file…, under Pasted list, opens a .csv, .tsv or .txt file. You can also drop the file on the dialog. The file is read in your browser and is not uploaded: nothing leaves the page until Check the list, which sends the lines just as it sends pasted ones.

• Separator. Commas, semicolons, tabs and vertical bars (|) are all read. The dialog picks the one that gives the same number of values on most of the file's first twenty lines, and says which it used ("Opened prices.csv, with its values separated by semicolons. Lines: 120."). Excel in much of Europe saves a CSV with semicolons, because the comma is its decimal separator, so QA-1;12,50 is a SKU and a price of 12,50. A first line sep=;, which Excel writes and reads, is followed and skipped.
• Quotes. A value in quotes is kept whole, commas, semicolons and line breaks included, and "" inside it is one quote, as Excel writes them. A quote inside a value, such as Pipe 1/2" brass, is only a character.
• Characters. A file saved as UTF-8 (Excel's CSV UTF-8, with or without the mark Excel puts at its start) or as UTF-16 (Excel's Unicode Text) is read as such. Any other file is read as an older Excel's CSV from Windows in Western Europe (Windows-1252), so MÜLLER-1 and € come through, and the dialog says so. If accents still look wrong, save the file as CSV UTF-8 and open it again.
• Columns. The rows go into Pasted list separated by tabs, as a spreadsheet copies them, so a file with many columns works like a paste: choose SKU is in and New value is in, or let a first line that says SKU pick the SKU column. The rule for lines typed with commas does not apply to a file. Blank lines are kept, so the line numbers in the check are the file's own.
• Values are read the way your store writes them, as for a paste: a file with 12,50 on a store that writes 12.50 has those lines refused, each with how the store writes prices.

A file is refused, and the box left as it was, when it is a spreadsheet file (.xlsx, .xls, .ods or .numbers: save it as CSV first), when it is larger than 5 MB, when it has more than 5,000 lines with anything on them (the line of column names counts: split the file), when it has only one column, or when it is not text at all.

Paste a list changes only the one column you choose, from a pasted list or a CSV file. It is not an import: it creates no products and changes no other column. For anything else, edit the grid.