WooCommerce CSV Import – Some More Notes

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.