Update existing WooCommerce products with CSV import: safe round trip

To update existing WooCommerce products with a CSV import, export them from BEAR, change the values in a spreadsheet and load the file into the WooCommerce product importer with Update existing products ticked. The round trip works, but the export and the importer do not use quite the same language: several columns must be mapped by hand, some empty values are written as 0, and the importer writes every mapped cell, empty ones included. Done carelessly, one import can put products on sale for zero, switch on stock management, hide attributes or strip the attributes that variations are built on. Below is the procedure that avoids all of it.

How a CSV import updates existing WooCommerce products

BEAR has no import engine of its own. The Import from CSV button in the Export tab opens the standard WooCommerce importer (Products → Import) in a new browser tab, so an update follows WooCommerce's rules:

• With Update existing products ticked, every row is matched to a product or variation by its ID cell, or by SKU when the ID cell is empty or the file has no ID column. Rows that match nothing are skipped with No matching product exists to update.
• Without the tick the importer creates products, and rows whose ID or SKU already exists are skipped with A product with this ID already exists (or SKU).
• Products that are not in the file are not touched, and nothing is deleted. A file with five rows changes five products.
• Every mapped column is written to every row, and an empty cell in a mapped column clears the value: an empty Sale price removes the sale. A column set to Do not import leaves the field as it is.

The last rule is the key to a safe update: put into the file only what you are going to change, plus ID, and map nothing else. Make a backup before the first run. The importer does not go through BEAR, so BEAR's History tab cannot roll back what an import changed.

Export only the columns you are going to change

1. Filter or tick the products in BEAR. For a variable product the export adds every variation, each on its own row with its own ID.
2. In Settings, switch on ID and the columns you want to edit, for example Regular price and Sale price, and switch off the rest. The file contains exactly the active columns, in the same order. Save the set under Columns profiles to get an identical file every time.
3. Leave the attribute columns off unless the job is to change attributes; the section on variations explains why.
4. Keep the comma in the delimiter drop-down, press Export to CSV, then download CSV.

Bulk editor export and import: column mapping

BEAR names the columns as its editor does. The importer recognises its own names and a few common ones, and lists everything else as Do not import. In an English admin these columns map by themselves: ID, Title (to Name), SKU, Type, Description, Regular price, Sale price, Tax status, Tax class, Categories, Tags, Shipping class, Upsells, Cross-sells, Grouped products, Purchase note, Menu order (to Position), Download limit, Parent, the Attribute N columns, the Download N columns and every Meta: column. In other admin languages fewer of them match. For the rest, pick the field in Map to field:

Some columns have no counterpart in the importer: Slug, Manage stock, Downloadable and Virtual (the Type column already carries these two as simple, downloadable, virtual), Author, Date Published, Total sales, Review count, Average rating and Attribute visibility. Leave them on Do not import. The same goes for Thumbnail and Gallery: the importer has a single Images column (featured image first, then the gallery), while BEAR exports two, so change images in the editor instead.

Two more things on this screen. The header row of a BEAR file ends with a delimiter, so the list ends with an extra column without a name; it is empty, leave it on Do not import. And meta fields that hold lists or structured data (the ones BEAR edits in a popup) are exported as JSON text, which the importer would save as a plain string in place of the original data. Do not import those columns; edit them as described in Meta Fields.

Empty values, zeros and -1: what the importer does with them

BEAR 2.2.4 writes number columns as numbers even when the product has no value: an empty Regular price, Sale price, Stock quantity, Weight, Length, Width or Height becomes 0 in the file. The importer takes the zero literally:

• Sale price 0 puts the product on sale for 0, and the shop sells it for free. Before the import, filter the Sale price column for 0 in the spreadsheet and delete those values: an empty cell means no sale price. Do the same in Regular price for products that have no price at all.
• Stock 0 switches stock management on with a quantity of 0, so a product that did not track stock becomes out of stock, and a variable product's own row turns on stock management at product level. An empty cell does the opposite: it switches stock management off. Export Manage stock together with Stock quantity, empty the quantity on every row where Manage stock is 0, and map Manage stock to Do not import.
• Weight and dimensions 0 replace empty values with 0. Empty them, or do not import these columns.
• -1 removes a sale price only in BEAR's own Bulk Edit tab, as in removing sale prices in bulk. The importer stores -1 as a negative sale price, and the product price becomes -1. In a CSV, an empty cell is the way to remove a sale.

Other values need a look too. Stock status is exported as 1 for in stock and 0 for anything else, so products on backorder come back out of stock; write backorder in their cells. Pending and scheduled products are exported with the words pending and future in Status, which the importer reads as 0 and makes the products private; leave Status on Do not import if you have any. Sale dates are exported without their time, so a sale that starts or ends at a specific hour is moved to midnight; do not import these columns unless you are changing the dates.

If you do this round trip regularly, let the export write empty cells and the real attribute visibility (explained in the next section) by itself. This filter changes only the values of the CSV export. Put it in the child theme's functions.php or a code snippets plugin:

/**
 * BEAR CSV export: write empty prices, stock quantity, weight and dimensions
 * as empty cells instead of 0, and the product's own attribute visibility,
 * so the file can be imported back with the WooCommerce importer.
 */
add_filter( 'woobe_export_product_fields_answer', function ( $answer, $product_id, $fields ) {
	$product = wc_get_product( $product_id );
	if ( ! $product ) {
		return $answer;
	}

	$getters = array(
		'regular_price'  => 'get_regular_price',
		'sale_price'     => 'get_sale_price',
		'stock_quantity' => 'get_stock_quantity',
		'weight'         => 'get_weight',
		'length'         => 'get_length',
		'width'          => 'get_width',
		'height'         => 'get_height',
	);

	// How many CSV cells each column takes: an attribute takes 4,
	// the downloads column takes whatever is left over.
	$widths = array();
	foreach ( $fields as $key => $field ) {
		if ( '__checker' === $key ) {
			continue;
		}
		$widths[ $key ] = ( 'attribute' === $field['field_type'] ) ? 4 : 1;
	}
	if ( isset( $widths['download_files'] ) ) {
		$widths['download_files'] = count( $answer ) - array_sum( $widths ) + 1;
	}

	$index = 0;
	foreach ( $widths as $key => $width ) {
		if ( isset( $getters[ $key ] ) ) {
			$value = $product->{ $getters[ $key ] }( 'edit' );
			if ( null === $value || '' === $value ) {
				$answer[ $index ] = '';
			}
		}

		// Attribute "visible" cell: the product's own setting.
		if ( 4 === $width && '' !== $answer[ $index ] && ! $product->is_type( 'variation' ) ) {
			$attributes = $product->get_attributes();
			if ( isset( $attributes[ $key ] ) ) {
				$answer[ $index + 2 ] = $attributes[ $key ]->get_visible() ? 1 : 0;
			}
		}

		$index += $width;
	}

	return $answer;
}, 10, 3 );

With the filter active, a file that is exported and imported back without changes leaves prices, stock and attribute visibility exactly as they were. A managed stock of 0 stays 0, because only missing values are emptied.

CSV import removed attributes or variations: how to avoid it

The importer handles attributes row by row, and it replaces the attribute list of a product rather than adding to it:

• When a product row has a value in any Attribute N value(s) cell, the product's attributes become exactly the ones in the file. An attribute that has no column, or an empty cell on that row, is removed from the product, including the attributes the variations are built on. The variations then lose those values and show as Any Size or Any Color in the shop.
• When all attribute cells of a row are empty, the product's attributes are not touched.
• A value cell is the complete list of terms for that product, separated by commas. A value that does not exist as a term yet is created, so a typo becomes a new term.
• BEAR exports global attributes only (Products → Attributes). Custom attributes typed on the product page cannot be in the file, so an import with any attribute value removes them. Make them global first, as in converting custom attributes to global.
• Attribute N visible is the Visible on the product page checkbox. BEAR 2.2.4 writes the attribute's Enable archives? setting into it, which is usually off, so an unchanged round trip hides the attributes on product pages. Use the filter above, or set the cells to 1 (shown) or 0 (hidden) before the import. Leave Attribute N global at 1.
• On variation rows the attribute cells are empty in 2.2.4. Because they are empty, the importer leaves the variations' own attribute values alone, so updating prices, stock or SKUs of existing variations by ID is safe. Creating new variations from the file is not: without attribute values they are created as Any ... variations. Create them in BEAR instead, as in bulk creating variations.

So if the CSV has to carry attribute changes, switch on the column of every attribute your products use before the export, and check that each product row lists all of its terms. For most attribute jobs the editor is the shorter way: bulk adding attributes to products adds terms without touching the others.

Excel, Google Sheets and everything in one column

• The mapping screen shows one row with all column names in it. The file uses a different delimiter than the importer expects. Excel saves CSV files with semicolons wherever the system list separator is a semicolon, which is the case in many European locales. Either enter ; in CSV Delimiter under Show advanced options on the importer's first screen, or save the file with commas. What counts is the file you upload, not the delimiter you exported with.
• Accented or non-Latin letters break. In Excel choose CSV UTF-8 (Comma delimited) when saving; plain CSV (Comma delimited) uses the computer's local code page. The importer's Character encoding of the file option is set to Autodetect by default.
• SKUs, codes and prices change. Excel drops leading zeros, turns long numbers into 1.23E+12 and, in locales with a decimal comma, misreads prices such as 29.90. Open the export through Data → From Text/CSV and set such columns to Text, as described in fixing BEAR CSV exports.
• Google Sheets avoids most of this: File → Download → Comma-separated values always writes commas and UTF-8.
• Use previous column mapping preferences? The importer remembers your last mapping, and this advanced option fills it in again. The mapping is stored by column position, not by name, so it is right only for a file with the same columns in the same order. A saved columns profile in BEAR gives you exactly that; check the mapping screen anyway before you run the import.

Checklist before you press Run the importer

1. A backup exists, and the file was tried first with one simple and one variable product.
2. The file has the ID column, and Update existing products is ticked.
3. The mapping screen shows separate columns with sample values, and only the columns you changed are mapped.
4. No 0 stands where the product had no sale price, price or stock tracking, and no -1 in price columns (or the export filter is active).
5. Either no attribute value is in the file, or every product row lists all of its attributes and terms with the right visible value.

The WooCommerce side of the import is documented in the WooCommerce product CSV importer and exporter guide. If an update still changes something it should not, open a topic on the BEAR support forum with the first lines of your file and the mapping you chose.