I recently created a Book Shop on my WordPress website using WooCommerce. The next logical thing I wanted to do was to get an extract of all my orders/invoices that have been sold through the shop into Excel. I couldn’t find a way to do this in the WordPress UI or via a free plug in, and then it occurred to me that maybe I could extract the data from the underlying WordPress Database using Power Query – and indeed you can. This is how I did it.
Enable Remote Access to your Database
Log into cPanel for your website and go to “Remote mySQL”
By default there should be one IP address set up for access – this is your web server. You need to add your home IP address to the list (or the IP address from where ever you are located and want to access the DB). You can find out your IP address at http://www.whatsmyip.org/. If you have a static IP address, you may want to use a service like Dyndns or similar http://lifehacker.com/the-best-free-alternatives-to-dyndns-1561556205 to maintain a static URL. This is not essential; the other approach you could use is to just update your IP address each time you need to.
Connect to the Database with Power Query
Open Excel, go into Power Query and commence the import process
Enter your Server Name and Database Name. For the server, you can either use the web site IP address you found when you first opened cPanel (not your home IP address) or you can use your main hosting website address. If in doubt, contact your ISP and ask them. The database name is the name of the WordPress DB you set up when installing your account. You can find this by going into cPanel and selecting “mySQL Databases”
The first time I did this, I got an error saying I didn’t have the right drivers.
I just followed the link in the error, downloaded the drivers and installed them.
After installing the drivers, I tried to log in – and it worked fine.
Find the tables you need and import them
You can use this approach for extracting any data from your WordPress DB. In my case, I was looking for the WooCommerce transaction tables so I could get a list of my invoices. I downloaded the following tables
Build a Data Model in PowerPivot
I joined the tables using the Order ID and order Item ID as shown below
I then built some simple DAX measures for Quantity, Price etc as follows
Total Products := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_qty”)
Product Val Tax := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_line_tax”)
Product Val ex Tax := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_line_subtotal”)
Product Val incl Tax :=[Product Val Ex Tax]+[Product Val Tax]
In WooCommerce, I set up the invoice number to be the Order ID with a prefix ‘s’. I just repeated that same logic in is a calculated column in DAX that follows the same rules.
And I ended up with a Pivot Table Report like this.
Hope that helps someone experiment with accessing WordPress Data using Power Query.
If you want a comprehensive lesson on how to use Power Query, check out my training course here https://exceleratorbi.com.au/power-query-online-training/
