There are a few problems with the WooCommerce CSV Import Suite that I have come up against. Some I have already talked about, but here are a few more. Some have workarounds, and some are just a little puzzling.
I am hoping that most of these issues will be solved by the time you need to read this. In the meantime, I hope knowledge of the issues are useful, and the workarounds help get your shop set up.
Selecting Which Columns To Import
This is a real pain to work around. When setting up a shop, and when products need updating often, it is necessary to update just a few properties of groups of products. For example, you may need to update just the shipping classes of many products in bulk.
When doing this, you want to avoid updating other properties of the products.
Ideally I would want to import my master spreadsheet – or a selection of rows from that spreadsheet – and tick off which columns to import and which to ignore. Unfortunately there is no way to do this in the WooCommerce CSV Import Suite, and it is not something that the developers are able to implement for technical reasons.
What you need to do, is to copy and paste the columns you want to import into a new worksheet, export that worksheet to a CSV, then import that CSV. Before long it can get very difficult to manage so many files.
SugarCRM, for all its own flaws, handles this nicely by offering a drop-down list of where to map every single column when importing, which includes a “do no import” option.
Exporting a Selection Of Products
So, you want to export all products in a given category, update them in your spreadsheet, then import the updates. Unfortunately you can’t do this. You get to export everything, or a specified count of products (starting a specified count number, in some unspecified order).
The import/export suite sorely needs the ability to export products that have been selected in a search of some sort – all in a category, a state etc. I’ll keep pushing WooThemes for this, because it is one of the features the plugin is sold on (“export products, update them, then import them again).
Exporting everything and sorting out what you need in your spreadsheet is not so reliable when the shop gets big – exporting too many products at once just results in memory errors.
Update: I have been looking at why the export runs out of memory. It is because the export loads ALL products for exporting into memory in one go, then goes through them all to see what columns are available, then goes through them all again to output each record. A quick experiment confirms that the export could do both phases in batches of N (say 2000) products. It will involve many more database queries, but at least it would work. So fetch the first 2000 products, make a list of available columns. Discard the first 2000 products, fetch the next 2000 and update the column list with anything new foun. Then carry on until the last product. Then start from the beginning (in batches of 2000) to do the actual export. The exported rows can also be written to a temporary file rather than directly to the output stream. Then the temporary file can be offered for download. This would help with memory problems caused when the PHP output buffer fills.
Notepad UTF-8 + BOM
I’ve talked about this before: when exporting products from Excel to CSV, it will be in an 8-bit Western (Latin1, ISO8859-1, Windows 2591 – all the same thing) characterset. You need to convert that to UTF-8 when importing into the shop, as the import is not able to handle that conversion itself.
I suggested to one client that Notepad would work. Windows 7 Notepad will open an ISO8859-1 file, and save it as UTF-8. Well, kind of.
Notepad actually saves it as “UTF-8 + BOM”, which adds three bytes to the start of the file. You cannot see these three bytes in any editor, but they do have an effect on the import. When importing the CSV, those three bytes are treated as apart of the first column name title. So if your first column is the SKU, the importer won’t automatically recognise the column heading “sku”.
There are two ways to work around this:
- Map the first column by hand. Just made sure you check the field mappings carefully when you import, and be away that the first column will never map automatically.
- Put an empty “sacrificial” column at the start of your spreadsheet. Call it “ingore” and it won’t be imported.
Why Notepad adds these BOM bytes when saving as UTF-8is a mystery. UTF-8m unlike some other multi-byte encodings, only has one “byte order”. The BOM is there to tell editors what byte order the encoding is, and so it is totally redundant when there is only one choice.
Like all these other issues, they are with WooThemes as tickets, so we will see if it can be handled in the product so we can all have a smoother experience.
Stock Level Handling
Stock levels. Now there is an important figure for managing your shop. You need to know how much of everything you have.
An online shop is not necessarily only an online shop. There could be a proper bricks and mortar shop selling the same products. There could be other outlets – products sold on related sites. That is not to mention losses and damages on the shop floor. Ultimately, there will be a stream of inventory updates to apply to the shop.
And that word is key: updates. The inventory count may go down by three, if products are sold in the physical shop. They may go up by ten as more stock comes in. The import suite needs to be able to apply those updates (-3 or +10) to whatever level the stock is at now. Unfortunately teh import suite only supports overwriting the stock levels with a new absolute value.
This isn’t stock management. This is just a number.
Edit: I had a reply from WooThemes concerning this. The gist of it was that this isn’t going to change. The CSV import will load initial stock levels only, and really cannot be used once the shop is live and purchases are being made. Stock and intentory handling must be done outsite of WooCommerce, with a more sophisticated API to keep things synchronised. It is just a limitation to be aware of.
How Exported Strings are Quoted
It’s not documented, so here you go:
WooCommerce CSV Import Suite will export all fields surrounded by double-quotes (“). That is all fields, whether they need it or not. Any quotes found in the strings being exported, will be escaped by doubling-up the quote (“”)
All fields are separated by commas. All lines end with a newline (n), the Unix-style line ending.
No other escaping is done. Commas are not escaped, because they are within quoted strings. That works great for OpenOffice Calc and LibreOffice, but can throw Excel at times.
Excel also does not cope well with multi-line fields. Even though the line endings within multi-line fields are all within the context of quoted string, and should be parsed as being within the string, Excel does not always get that right. I suspect it has something to do with the line endings within fields sometimes being DOS-style (rn), depending on where the data came from, and the line endings of records always being in Unix style (n); Excel just goes bluugh and breaks the record into multiple records.
A fix for this could be for the exportor to replace all DOS and Mac style line endings within multiline fields with a single n. That would change the nature of data though, which may not be desirable.
Hi, thanks for the great article.
I am also having trouble with this plugin. My problem however, is that it won’t export at all. Upon trying the export I get the following error:
“C:UsersRICHAR~1AppDataLocalTempABVQ8lVA.csv.part could not be saved, because the source file could not be read. Try again later, or contact the server administrator.”
I have tried everything I can think of to diagnose this. Woo Support say it’s a problem with my ‘hosting environment.’ My host have done everything Woo has told them to try and fix the problem including increased max_execution_time and memory_limit limit. However the same problem persists. After trying and failing several times Woo are now refusing to help me anymore as their view is that it’s the host not the plugin. This may be true but without their help my host doesn’t have anything else to go on and so I’m stuck…. I’m not impressed at all with this plugin or the quality of support from WooThemes.
Hi Rich,
That’s a strange one. It looks like you are using Windows and IIS to host the shop. I have never mixed IIS and PHP, so don’t know whether there is something Windows-specific that is causing this problem, i.e. an assumption made in the exporter that it will be running on Linux.
Can I confirm a few things:
Firstly, it is the exporter from the WooCommerce CSV import Suite that you are running, and not the built-in WooCommerce exporter? I only ask because I didn’t think the CSV Import Suite wrote its CSV data to a temporary file before sending it, as it appears to be doing here, but I could be wrong.
How many products do you have in the shop, and how many are you trying to export in one go? If you try to export just ten products, does the same thing happen?
What is your memory limit set to? We have set 256Mbyte (yes, quarter of a gigabyte) and can export 4000 products at a time, but not many more than that.
IIS error logs – any clues in there? I’m assuming you are running under IIS – perhaps you are running Apache? Either way, do the error logs report anything at all when an export fails?
I’m having the same issue as Rich (op) is … I’m on LAMP (dedicated) and cannot figure the issue out. Running latest v1.1.6 of CSV Import Suite.
Rich … did you find a solution to this?
I wonder if it is a timeout issue? Perhaps IIS is tidying up temporary files quicker than they can be delivered to the browser connection, or the file is too large and blows a limit so gets discarded by IIS.
I still suspect the system logs will give some additional clues.
This is quote/copy of my developer’s email to me about the download not working that might shed some light on it (LAMP system, not IIS) for others:
“… Yeah, nothing in error logs.
So, sort of resolved.
1. copied your php.ini file to /home/********/public_html/php.ini and made modifications. Notably, specified error log as /home/********/public_html/wp-content/php-error.log. Turned up error-reporting to E_ALL. No fatal errors in error log.
2. Updated wp-config and enabled WP_DEBUG. guess what? this fixes the problem. Disabled it because it’s not good for a production site. Returned to broken status (not allowing downloaded exports for CSV Import Suite plugin).
3. Looked for “why does WP_DEBUG fix this weird problem and nothing else” — nothing, it just enables some debug stuff.
4. Modified export script to also create file in wp-content: /home/********/public_html/wp-content/products.csv
So, note that there’s a new file (php.ini) in the root of ********.com and note that there’s a new products.csv file in wp-content.
Note that if you really want to download the file, change the WP_DEBUG line in the wp-config file to true. The other settings can remain false. WP_DEBUG is supposed to create a log file in wp-content. But it isn’t. Anyway, enabling WP_DEBUG made that download work (I don’t know why).
Hmm, you know what it might be? It might have something to do with that weird setting on your server (that I kinda like but kinda hate) where apache won’t parse php files that are owned by root, there’s a strange permission thing that happens. Example: in the process I tried to create a phpinfo page to find the .ini file and log settings, so created p.php. When it was owned by root, it would 404 out. Chowning it to the account/domain username made it work correctly. …”
The permission thing sounds like one of the modes that PHP can run in – suexec I believe. It will only execute php scripts that are writeable by the FTP user, i.e. the main user of the account. If it finds any files writeable by other users, such as having write permission on the group, then it will not run the PHP script as an entry point.
Personally I believe that mode is deeply flawed, because it forces you to make all files writeable by the FTP user that is ALSO the apache user, and so once a hacker gains access to your application, they can run rampant over the whole thing.
On shared hosting, it protects the different sites from each other, but increases the potential for damage to any particular site that may be compromised.
So, make sure group write permission is taken off all the PHP scripts that are entry points to the application. I’m not sure whether WooCommerce uses a separate entry point for an export – I’ll check.
The CSV export just seems to run as a POST through the admin.php entry point:
POST /wp-admin/admin.php?page=woocommerce_csv_import_suite&action=export
so I doubt it is the file permission preventing execution of scripts that is the problem.
Debug mode could be catching errors and diverting them to a log file, rather than letting them get in the way of export processing.
Hi Jason
Thank you for the help. It turned out to be a host problem. Despite them advising me it was definitely not at their end, after I tried a hundred different things I moved host and guess what- problem solved!
My site also has around 20k products. Importing them all took a while and I had to split up the files into chunks of 10 or so CSV files.
Now I would like to update these product’s stock quantities, but am running into issues. I can’t see to merge/update 500-1k items at once. Are you running into the same issue? How many items in your CSV file can you merge successfully?
When I merge with 1k items in my CSV file the script craps out after several minutes. The page ends up loading a 404 page after the several minutes. And I am only uploading 4 columns (sku, manage_stock, stock_status, stock)
Any help would be appreciated
Hi Ryan,
It is unfortunate how it works. The import loads up each product in turn, updates it, then saves the result. Because of the way WP works, most of that loaded data stays in memory. It makes no difference whether you are updating one field or twenty, the same amount of memory is needed.
Whether that is considered clever cacheing or an in-page memory leak, is up for debate, but suffice to say, updating (i.e. “merging”) products will eat into memory rather quickly, and more quickly than creating them in the first place.
I started putting a library together a while ago to try and work around this. The library has a class that keeps track of memory on each record updated, and if it reaches a certain level (e.g. 90% of memory used or within N-iterations of running out of memory) then the process can cleanly close, but not until starting up another process to take over.
That library would need a controller process that can run a series of partial updates until it is complete, or perhaps use a queuing system so the updates can all operate in the background. The latter method is probably cleaner, as it can integrate nicely into an automated update process, but will involve more components to set up on the server.
That library has been paused due to other commitments, but it’s here if you are interested:
https://github.com/academe/memcheck
So in short – sorry – no hints on how to get around the memory issue on updates, except to use even smaller chunks than when inserting the products in the first place. Or by-passing WooCommerce with custom code – perhaps it needs a “stock synchroniser” plugin? Maybe linking to something like OrderHarmony through APIs to keep the stocks in sync in real time, without having to touch CSV files?
https://github.com/academe/orderharmony
Thank you for the articles on the CSV Importer/Exporter. I was wondering what your solution was to being able to automatically update stock quantities and such? We utilize multiple vendors and get a daily csv data feed of stock quantities available. We’d like to be able to feed these into a system have the skus matched up and then update quantity. Do you have any recommendations for products that work well with WooCommmerce?
TIA for your time and input.
The CSV Import Suite will allow you to update the stock quantities to an absolute value, which may be okay if that is what you are looking to do. What it does not support is increment and decrements of the stock.
For example, if you had multiple channels with stock sold online and in a shop perhaps, you may want to update the online stock quantity according to what was sold in that shop that day. The Import Suite will not really support that.
However, WC 2.x has an API. I have not explored the API at all yet, but if you cannot set and change stock levels through that, I would be surprised. You would need to write a script to take your feeds then apply the API updates.
Do you have multiple sales channels? Is that why you have stock quantities coming from multiple sources?
Hi Jason
I’ve just come across your interesting articles. I wonder whether you have encountered this particular problem with exporting to a csv?
My SKUs are in the EAN13 barcode format, which is useful because I can run a cash register off the website using a barcode scanner with WooPOS.
Unfortunately, upon exporting a csv and opening with Excel, barcodes are read as very large numbers and formatted as eg 5 x 10 E12, with apparent loss of accuracy.
This makes export/edit/import impossible. It is also awkward for exporting and then uploading products to Amazon FBA. We would like to make the website the authoritative central database for our product data, to replace our current master spreadsheet, and again this would be tricky.
I am probably missing something basic and would be interested to know whether you have experience of this problem.
Andrew
Hi Andrew,
That’s a hard one to solve, as there is no standard accepted CSV format that you can use to tell the spreadsheet, “no matter what this field looks like, it is a *string*”. That is what is happening here – the spreadsheet is seeing a number and so treating it as a number, and that involves stripping off leading zeroes as well as changing the format as observe.
One way around this is to save the export to a file, then import it into your spreadsheet. When you import a CSV file, you can tell it how to interpret each column and so can tell it the SKU is a string. That is a bit cumbersome though.
Using Libre Office may help, as that formats numbers in a longer format, but you still lose leading zeroes.
There is a technique here for Excel that I’ve not seen before, but looks interesting:
http://superuser.com/questions/318420/formatting-a-comma-delimited-csv-to-force-excel-to-interpret-value-as-a-string
An extension would probably need to be written to modify the CSV output of the export. That is something which may be worth exploring.
Otherwise, you may just have to keep the export as a CSV file and not take it through a spreadsheet stage. That may or may not be practical, depending on what modifications you need to make to the data before sending it to Amazon.
A more complete solution may be one that spits out ODS or XLSX spreadsheets directly, with all the formatting pre-defined. I’ve done that on custom applications using some great open source PHP libraries, and it ought to work for WooCommerce. But again, that would need a custom plugin to be written.
I’ll also add that there is an API in WC now, so maybe a simple application can be written to pull out the data you need and format it exactly as it needs to be for the destination system.
I’d be surprised if such a custom export has not already been written – let me know if you can’t find one, and perhaps we can put one together. I need a good excuse to play with the WC API 🙂
Hello!
I’m having a strange problem. When the CSV import suit ends uploading the csv file and before showing the “matching fields”, the screen only shows the menu bar and the Import Product title, and all the rest of the screen is empty.
The version of the plugin is 1.6 and the Woordpress and wooCommerce are in their last version.
Thanks a lot for your help
Hi, thanks for the article!
I would like to change quite a few products’ title… I was thus thinking of exporting my csv, modify the first column “tilte” and import back…
However this does not work. At the end of the import it says successful but no titles have been changed..
Do you have any ideas?
Thanks,
Cedric