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:

SELECT DISTINCT pm.meta_key
FROM {$wpdb->postmeta} AS pm
INNER JOIN {$wpdb->posts} AS p
ON p.ID = pm.post_id
AND p.post_type = 'product

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:

SELECT DISTINCT pm.meta_value
FROM {$wpdb->postmeta} AS pm
INNER JOIN {$wpdb->posts} AS p
ON p.ID = pm.post_id
AND p.post_type = 'product'
WHERE pm.meta_key = '_product_attributes'

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:

            /**
             * Get a list of all the meta keys for a post type. This includes all public, private,
             * used, no-longer used etc. They will be sorted once fetched.
             * Added 2012-11-04 JDJ
             */
            function get_all_metakeys($post_type = 'product', $post_status = array()) {
                global $wpdb;

                $sql_statement = $wpdb->prepare(
                    "SELECT DISTINCT pm.meta_key
                    FROM {$wpdb->postmeta} AS pm
                    INNER JOIN {$wpdb->posts} AS p
                    ON p.ID = pm.post_id
                    AND p.post_type = %s"
                    . (!empty($post_status) ? " AND p.post_status IN ( %s".str_repeat(", %s", count($post_status)-1)." )" : ""),
                    array_merge(array($post_type), $post_status)
                );

                $results = $wpdb->get_col($sql_statement);
                sort($results);
                return $results;
            }

            /**
             * Get a list of all the product attributes for a post  type.
             * These require a bit more digging into the values.
             * Added 2012-11-04 JDJ
             */
            function get_all_product_values($post_type = 'product', $post_status = array()) {
                global $wpdb;

                $sql_statement = $wpdb->prepare(
                    "SELECT DISTINCT pm.meta_value
                    FROM {$wpdb->postmeta} AS pm
                    INNER JOIN {$wpdb->posts} AS p
                    ON p.ID = pm.post_id
                    AND p.post_type = %s"
                    . (!empty($post_status) ? " AND p.post_status IN ( %s".str_repeat(", %s", count($post_status)-1)." )" : "")
                    . " WHERE pm.meta_key = '_product_attributes'",
                    array_merge(array($post_type), $post_status)
                );

                $results = $wpdb->get_col($sql_statement);

                // Go through each result, and look at the attribute keys within them.
                $result = array();

                if (!empty($results)) {
                    foreach($results as $_product_attributes) {
                        $attributes = maybe_unserialize(maybe_unserialize( $_product_attributes ));
                        if ($attributes) foreach($attributes as $key => $attribute) {
                            if ( ! $key || ! isset( $attribute['position'] ) || ! isset( $attribute['is_visible'] ) || ! isset( $attribute['is_variation'] ) ) continue;
                            $result[$key] = $key;
                        }
                    }
                }

                sort($result);
                return $result;
            }

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.