WooCommerce CSV Import Suite – Exporting Large Numbers of Products

We are using the WooCommerce CSV Import Suite to manage 22,000+ products on a shop. This premium (meaning “non-free”, though still GPL) plugin supports both import and export.

We need to be able to export the entire shop, so we can go through the products in a a preadsheet, look for anomalies such as missing shipping classes, and them re-import corrected sections of that spreadsheet to update the products. The problem is, memory. An export will just about manage 4,000 products before the PHP process runs out of memory, and that is with a memory limit of 256Mbyte set.

I have tried running the export through an internal loop, doing the fetches in batches of 1000 records, but that does not help much. PHP does little memory cleanup after itself, so it is almost impossible to clean up as you go along.

So another approach: export six CSV files, products 1 to 4000, 4001 to 8000, 8001 to 12000 and so, then merge them all together. Simple!

Well, no it’s not that simple. The export function will include only the attributes and metadata that it finds in the batch that it exports. So some batches may not have a shipping class column at all, if all those products happen not to have it set. This applies to other metadata added to products by other plugins – what gets exported is a bit random.

And to add to that, the order of the columns is also pretty random. The order of the metadata columns is in the order in which the exporter happened to encounter them as it went through the products.

So you an imaging, merging spreadsheets like this – with different columns, and in random orders – is a little cumbersome.

So, how to solve this problem?

Merging (i.e. appending) CSV spreadsheets is quick and easy if all the columns match up. So I the approach I took was to ensure all CSV exports had the same columns.

I tried fetching products and metadata, in batches, to inspect the fields and quickly ran into memory problems again. So instead, the idea was to fetch these directly from the database. I wasn’t concerned with the values of the metadata, just the keys, so fetching all unique metadata keys for shop products was simple enough:

A few more conditions ensure this is limited to just the product post statuses we are interested in.

The product attributes are fetched like this:

Over 20,000 products that only brought back half a dozen product attribute combinations, which could then be sifted through in PHP to get the unique list of all possible attributes.

With these two queries added to the export, it all worked great, and now every product export we do is guaranteed to have the same column headings and in the same order.

If you are using the WooCommerce CSV Import Suite v1.30, this update contains my fixes:

product-csv-import-1_30-JDJ.zip

Extract product-csv-import-1_30-JDJ.php then copy it to this location, overwriting the original:

wp-content/plugings/woocommerce-product-csv-import-suite/product-csv-import.php

Here is the original file for v1.30 of the plugin, if you want to see exactly what has been changed, so you can apply it to later versions (assuming this does not make it into the core plugin in some form):

product-csv-import-1_30.zip

I hope this is of some help to anyone running a large WooCommerce shop.

Here are the two functions, as they could be handy for other applications:

The post_status parameter is an array of post statuses. It would be great if the WordPress $wpdb->prepare() method was able to accept arrays of values as a parameter to feed into IN() statements, without the messing around I’ve had to do with repeating bind variables.

3 Responses to WooCommerce CSV Import Suite – Exporting Large Numbers of Products

  1. Gerry 2013-04-29 at 04:34 #

    Hi there,

    What about when it comes to already existing products, which have 3 attributes which appear in the additional information tab on the product page.

    When importing new attributes via the csv import suite, about 9 new attributes to be exact, it is impossible currently to import and have them appear in the chose order on the front end.

    In the back end, the attributes are in order, if you hit update on the backend product edit… The front end is then fixed.

    Having a store with 2000+ products, and trying to work through this bug, and virtually ZERO support via the WOO_FAIL ticket system…

    LOL!

    Any suggestions on how to fix this?

    Any tips would be much appreciated.

    Regards,

    Gerry

    • Jason Judge 2013-04-29 at 08:58 #

      Do you have a sample URL you can send?

      So the ordering you are wanting to apply, is that in the values for a specific attribute (the taxonomy terms), or is it the ordering of the attributes (the taxonomies) themselves?

      I think for both, there is no real ordering built into WP or WC to support that. If you are getting an order through from the edit page to the front end, I suspect it is more coincidental/by accident, than it is a functional feature of WP, so is not something you can rely on.

      The way we would handle it, is to put things into the order we need in the theme. Within the theme, any of the WC templates can be over-ridden and customised.

      — Jason

  2. Murrance 2014-01-11 at 10:42 #

    Hi there.

    Our client is using the Woocommerce CSV Import suite plug in.
    Initially he asked us to investigate why products were visible to customers, but having no stock available. So they could browse the product page, but were unable to select a size to purchase.
    After some testing we discovered the products stock status is ignorant of actual stock qty on hand. I have just completed fix where we sweep through all Variable products, calculating the total stock qty of all children, and SQL updating the stock status to be either instock or outofstock dependant of whether the total children stock qty is greater than zero.
    Now you can add a column to the import csv file headed stock_status, that allegedly updates the Inventory-stock status. But testing showed it did not work for Variable products. We assumed it would work for Simple products. Just tested this today and found it also useless and inneffective for simple product merging.
    Has anyone else come across this major short coming with the plug in?
    Generally it is a good tool, merging 22,000+ products in just over one hour.
    But I was so surprised to find the stock status completely independent of the underlying stock qty, be it a simple or variable product.

Leave a Reply