CSV Export & Ampersand now changed to &

Hi @9ball,

Can you tell us which CSV editor are you using?

We recommend using:

• Online spreadsheet editor?Google Drive / Docs
• Free spreadsheet app in?OpenOffice
• Free Calc app in?LibreOffice
• Numbers for macOS

If possible, avoid Microsoft Excel due to formatting and character encoding issues.

https://woocommerce.com/document/product-csv-importer-exporter/#what-csv-editors-do-you-recommend

I hope this helps!
-OP

hi,

I am using Microsoft Excel, but i don’t think that is the issue.
As the variable Parent SKU’s shows correctly on the exported CSV file as: BLACK&CREAM-X

but it’s only the “variation” SKU’s that adding amp; to the CVS file: BLACK&CREAM1

variable BLACK&CREAM-X
variation BLACK&CREAM-S
variation BLACK&CREAM-M
variation BLACK&CREAM-L

i did remove the products and manually re-added the product and all variations SKUs and exported the CSV again and opened within windows 11 Notepad /txt file.
and is still showing the: BLACK&CREAM1

but the Parent variable SKU shows ok and amp; isn’t being added on the export CSV: BLACK&CREAM-X

Hey, @9ball! Thanks for the clarification.

I was not able to replicate this on my website.

Have you tried one of the other editors my colleague suggested to see if the issue persists?

Are you using the default WooCommerce importer/exporter or are you using another plugin?

Looking forward to your reply.

Have a wonderful day!

i used the default WooCommerce importer/exporter and used different other editors with no luck.

i also couldn’t replicate this issue on my other websites. (which are 99% the same setup).

As i couldn’t find what the issues was, i had to edit the database SKU’s manually re-editing them and removing the amp; from the variations SKU’s with &

Now seems to be exporting the CSV variation SKU’s for the product with &…. ok

Now seems to be exporting the CSV variation SKU’s for the product with &…. ok

That’s great to hear! Thanks for letting us know @9ball .

As i couldn’t find what the issues was, i had to edit the database SKU’s manually re-editing them and removing the amp; from the variations SKU’s with &

Either that, or using the find and replace functionality in spreadsheet editors, yeah.

We’re happy to help, so please feel free to reach out to us again if you have any other questions.

i thought i had sorted it, now the only way to update the stock qty for the variation SKU’s that have a “&” within the SKU’s is by using the ID number.

if i try to use the variation SKU’s with & or without amp; the the woocommerce importer can’t find the products /SKU’s at all.

even though i changed the SKU’s within the database and removing the amp; after importing a test CSV file with the correct ID, SKU, Stock

id type sku stock
123 variation BLACK&CREAM-S 1
456 variation BLACK&CREAM-M 1
789 variation BLACK&CREAM-L 1

now seem the database has changed SKU’s back again and added the amp; after the test import

BLACK&CREAM-S
BLACK&CREAM-M
BLACK&CREAM-L

Seem it maybe an import issue.

Hey, @9ball!

Let’s investigate this further.

Do you mind sharing the CSV file so we can take a look?

Furthermore, I’d like to understand your site properly. Please share with us the necessary information below for us to investigate the issue further:

System Status Report which you can find via WooCommerce > Status > Get system report > Copy for support.
Fatal error logs (if any) under WooCommerce > Status > Logs. Then you can use the selector to search for Fatal Errors.
You could copy and paste your reply here or paste it via https://gist.github.com/ and send the link here.

Have a wonderful day!

hi,

Live website, WooCommerce > Status > Logs. No Fatal Errors.

i setup a Staging site, which runs within the hosting website root

Clean WordPress Version: 6.5.5 install using WPvivid Plugins - Staging

Using a Clean database auto setup within Cpanel:
PHP version: 8.1.28
Server connection collation: utf8mb4_unicode_520_ci
Charset: utf8mb4

Default Theme: Twenty Twenty-Three
Woocommerce Version 9.0.2 (No other plug-ins are installed)

Added only One variable product, with default Price, Stock, Colour and Size

variable SKU TESTINGTSHIRTBLACK&CREAMSHORT-G

variation SKU TESTINGTSHIRTBLACK&CREAMXLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMMEDIUM
variation SKU TESTINGTSHIRTBLACK&CREAMSMALL

then exported the Standard Woocommerce CSV and the SKU's have now show a added amp;

variation SKU TESTINGTSHIRTBLACK&CREAMXLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMMEDIUM
variation SKU TESTINGTSHIRTBLACK&CREAMSMALL

Opened the CSV file in Libreoffice, Mordern CSV, Excel, always the same.

seems looking at the the phpMyAdmin database has also changed to be & after the product was added and saved.

Issues:

1, cant update the Stock using the variation SKU TESTINGTSHIRTBLACK&CREAMXLARGEhi,

Live website, WooCommerce > Status > Logs. No Fatal Errors.

i setup a Staging site, which runs within the hosting website root

Clean WordPress Version: 6.5.5 install using WPvivid Plugins - Staging

Using a Clean database auto setup within Cpanel:
PHP version: 8.1.28
Server connection collation: utf8mb4_unicode_520_ci
Charset: utf8mb4

Default Theme: Twenty Twenty-Three
Woocommerce Version 9.0.2 (No other plug-ins are installed)

Added only One variable product, with default Price, Stock, Colour and Size

variable SKU TESTINGTSHIRTBLACK&CREAMSHORT-G

variation SKU TESTINGTSHIRTBLACK&CREAMXLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMMEDIUM
variation SKU TESTINGTSHIRTBLACK&CREAMSMALL

then exported the Standard Woocommerce CSV and the SKU's have now show a added amp;

variation SKU TESTINGTSHIRTBLACK&CREAMXLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMLARGE
variation SKU TESTINGTSHIRTBLACK&CREAMMEDIUM
variation SKU TESTINGTSHIRTBLACK&CREAMSMALL

Opened the CSV file in Libreoffice, Mordern CSV, Excel, always the same.

seems looking at the the phpMyAdmin database has also changed to be & after the product was added and saved.

Issues:

1, i can't update the Stock using the SKU's TESTINGTSHIRTBLACK&CREAMXLARGE ect ect as the products SKU's do not exist to update.
2, i can't even search in WooCommerce products for the variation SKU's as the product do not exist.
3, if you search for the SKU's with the & in woocommerce product search then it finds them.

Hey, @9ball!

I tried replicating this on my test site, but couldn’t. All the variations keep the name with &.

Do you mind sharing the CSV file with this product so we can take a look?

Have you tried updating the CSV file with Google Sheets too? Does it has the same outcome?

Looking forward to your reply.

Have a wonderful day!

hi,

the product was made manually within WooCommerce add product, and not by importing a CSV file, it’s the exported CSV file that has issues.

even if i import a CSV it works ok, but as soon as i export a CSV file, its got the &amp: added, Which wasn’t on the import or when added the product manually.

i even tried the wordpress tools export XML ” Variations” and even this XML export file shows the SKU’s with &amp:

wp:postmeta
wp:meta_value><![CDATA[TESTINGTSHIRTBLACK&amp;CREAMSMALL]]

Hi @9ball,

I also tried replicating by creating a new test product in my store with some of your shared SKUs and exported all products without having any issues.

Could you try exporting the CSV with only WooCommerce as the active plugin to see if this persists?

HI,

After setting up a subdomain with new database, and new wordpress, and WooCommerce
here’s what i found..

i created a product manually with Variations. with colour, size stock, price etc
all the variation SKU’s had a” & “within them.

i exported the woocommerce CSV file and it all looks fine, and No &amp;
showing up on the CSV file SKU’s

thought i had sorted it…until

if i update the opened CSV file, and change the Stock qty amount and change the prices
and save and upload the CSV file as it was downloaded with all columns’

and Tick the box “Existing products that match by ID or SKU will be updated. Products that do not exist will be skipped.”

everything seems to upload ok, until i redo a new export woocommerce CSV file to check the new stock qty and new prices have updated.

now all the SKU’s have a &amp; on the CSV file.

Hi @9ball

This could potentially be due to encoding issues. Which software are you using to edit it? When you open the CSV file, the software you use might interpret the “&” symbol in the SKU differently. It’s important to ensure that your CSV file is in UTF-8 format to prevent such issues.

If you’re using Excel to open and save your CSV file, you might need to use Google Sheets or another program that handles CSV encoding better.

If the issue persists, please provide us with more information about the software you’re using to open and edit the CSV file and the exact steps you’re following. This will help us better diagnose and resolve your issue.

Looking forward to hearing from you!

hi,

i’ve used :
Google doc’s saved as save as comma separated CSV
Openoffice V4.1.15 opened as UTF-8 CSV and saved has UTF-8 CSV
Modern CSV saved as UTF-8 CSV
Excel saved as comma separated CSV

i’ve even tried changing the woocommerce import to Character encoding of the file upload using UTF-8 before importing.

today

1, i used Google doc’s only to import the product, and it imported ok,

2, i then exported the CSV in woocommerce and opened up in google doc’s changed the price and stock amount, saved as CSV and reimported, import ok.

i then re-exported a CSV again within woocommerce opened up in google docs, and again the & has now changed to &amp;

to add to the above, when the CSV export shows: &amp;
i deleted the one product from within Admin WooCommerce products.

but looking at the database the product is still showing and hasn’t been removed /deleted in:
_postmeta
wc_product_meta_lookup

so even by recreating the product again manually or by upload CSV, even after it been deleted from within the Admin woocommerce side, if using the same SKU’s but without the amp;
the product that’s still within the database with &amp; seems to over righting the newly made product.

so on the next CSV export it will again have amp; added to the &