Importing and Exporting WooComemerce Products from the Database

Published by John on August 19, 2020 Under Wordpress

While launching a website hosting clients website, it was necessary to import some WooCommerce data from their old website to their new website. Unfortunately, the people who designed their site did not make a copy of the database first and use that when creating the site and instead just created a brand new WordPress site. As a result, none of the product or order data was present on the new site they created.

The product import was easy, as WooCommerce includes a product import/export function under the products section: Products -> All Products -> Import and Export Buttons at top.

However, they do not include a tool to export WooCommerce orders, so you would have to use a plugin, most of which are either paid or freemium pricing models.

My initial thought was that since WooCommerce Orders are a Custom WordPress Post Type, that I could just use the WordPress Import/Export tool. After a closer look of how data is stored by WooCommerce though, specifically the wp_woocommerce_order_itemmeta and wp_woocommerce_order_items tables that are used to store product/order information, I realized this was unlikely to work, as the Post ID would likely change after import. So, while the orders would import fine, the supplemental data in those two tables would likely no longer match up to the Post ID.

I was about to write a script to import/export them, but decided to explore just doing a database dump of the orders and relevant data. The biggest issue with this, which is described below, is if there are any collisions between the existing Post IDs and the Orders you are importing, this will not work. Fortunately for me, this was a brand new website so only had 200 records in the wp_posts table, while the old store was well established and the orders did not start until 3000. However, had this not been the case, this method would not work as described.

Please review the warnings first steps before attempting this process.

First Steps / Warnings

• Before you do anything else, backup your current website database and your new website database. Failing to do this may result in data loss!
• If you are not comfortable restoring your database from a backup, you should not continue.
• If you are using Cpanel to generate your dump files, make sure to UNCHECK the ‘Add DROP TABLE / TRIGGER statement’, ‘Add CREATE TABLE statement’, ‘Add CREATE VIEW statement’, ‘Add CREATE PROCEDURE / FUNCTION / EVENT statement’, and ‘Add CREATE TRIGGER statement’ buttons under Export -> Custom -> Object creation options section.
• If at all possible, do this on the same database server, as you can skip actually generating export files and just use a query to import/export between different databses.
• You may want to make backups at each step, so if you mess up or get an error, you can revert without having to start over.

When this method will not work

This method relies on the wp_posts.ID matching the post_id in the wp_woocommerce_order_items and wp_woocommerce_order_itemmeta table.

When you do the import, you will be importing the orders from the old site using the same ID, so if you have an existing post on the new site with the same ID as an order, you will get an error.

1: Backup your databases

No really, please backup both databases and be prepared to restore if you get an error!

2: Export and Import the Orders

First, lets import and export your orders. Woocommerce orders are compromised of two types of posts: ‘shop_order’ and ‘shop_order_refund’

If you are using cpanel, you can run the below query

You will need to export these posts:

SELECT * FROM wp_posts WHERE post_type = 'shop_order_refund' or post_type = 'shop_order_refund';

Or if you wanted to use mysqldump:

mysqldump -u wordpress_user -p wordpress_database wp_posts --where=" post_type = 'shop_order_refund' or post_type = 'shop_order_refund' " --no-create-info > /tmp/wp_posts.sql

Or if the databases are on the same server:

insert into new_wordpress_database.wp_posts SELECT * FROM old_wordpress_database.wp_posts WHERE post_type = 'shop_order_refund' or post_type = 'shop_order_refund';

Next, import these into your database.

2: Export and Import the Order Metadata

Since these are wordpress posts, you will also need to export/import the metadata.

However, it is more likely that you will run into a collision here with the meta_id, so when you do the export do NOT include this column.

You will need to export/import this post metadata:

SELECT meta_key, post_id, meta_value FROM wp_postmeta left join wp_posts on wp_posts.ID = post_id where post_type = 'shop_order_refund' or post_type = 'shop_order_refund';

Or if the databases are on the same server:

insert into new_wordpress_database.wp_postmeta (meta_key, post_id, meta_value) SELECT meta_key, post_id, meta_value FROM wp_postmeta left join wp_posts on wp_posts.ID = post_id where post_type = 'shop_order_refund' or post_type = 'shop_order_refund';

It may be possible to do this via mysqldump too, although off the top of my head, I don’t know if you can limit by query this way. If not, you could create a temporary copy of the meta table and then delete the meta_id column to achieve a similar affect.

3: Export and Import the WooCommerce Order Items and Metadata

Next we need to export/import the WooCommerce Order Items and Metadata from the wp_woocommerce_order_items and wp_woocommerce_order_itemmeta tables.

Assuming your new website does not have any orders, you can just wipe this data.

You will need to export/import this data:

SELECT * FROM wp_woocommerce_order_items;
SELECT * FROM wp_woocommerce_order_itemmeta;

Or using mysqldump:

mysqldump -u wordpress_user -p wordpress_database wp_woocommerce_order_items wp_woocommerce_order_itemmeta --no-create-info > /tmp/wp_woodata.sql

Or if your databases are on the same MySQL server:

insert into new_wordpress_database.wp_woocommerce_order_items SELECT * FROM old_wordpress_database.wp_woocommerce_order_items;
insert into new_wordpress_database.wp_woocommerce_order_itemmeta SELECT * FROM old_wordpress_database.wp_woocommerce_order_itemmeta;

4: Optional Cart Sessions and Square Customers

If you are using Square, you may also want to use the same process for the wp_woocommerce_square_customers table as described above.

The wp_woocommerce_sessions table contains cart sessions, so if you have active users you may want to also import data from this table. In both of these cases, you should be able to just wipe these tables first, then import.

5: Wrapping Up

If all went well, you should now see orders populated with order items and relevant info.

No Comments |