How to bulk edit a WordPress site with Excel

01 Apr '21 by Antonio Nakić-Alfirević

Introduction

Editing a WordPress site can be a pain if you need to make many changes across many pages. But suppose you could view and edit the entire contents of your WordPress site inside Excel. No more navigation, searching and waiting for pages to load. In this article/video, I show you how to do this for your website.

How it works

Here’s an example. I work on a project called QueryStorm for which I have this WordPress site (the one you are reading right now). For a bit of background, QueryStorm is an addon for Excel that lets developers use modern programming languages in Excel.

To make it easy for myself to edit this site, I’ve made an editor for it in Excel with the help of QueryStorm. And you can easily use what I’ve built to edit your WordPress site, as long as you have the connection string to the MySQL database.

Anyway, when I want to change a piece of text in the website, I search for the text I want to change by entering it in the search cell. As soon as I do, the two tables below populate with all of the matching rows from the “posts” and “posts_metadata” tables in the WordPress database.

I can then make changes to these rows inside Excel. When I’m done, I click “Save” to save my changes to the database.

Benefits

There are several nice things about this:

  1. It’s quick and convenient

When I edit my website, I don’t have to click through WordPress admin pages and wait for them to load. I just open the workbook and when I search only the texts are sent from the server to my machine, not entire html pages.

  1. Full text search

I can easily find all occurrences of a piece of text, anywhere on the website. I can even use wildcards, the kind that the SQL LIKE operator uses.

  1. Bulk editing

Excel’s UI is fantastic for bulk editing. You can make a change in a single cell and easily apply the updated value across many rows.  You can also use Excel’s find and replace functionality to replace text in many pages at once.

  1. Backing up content

Here’s an interesting thing you can do with this. Put a wildcard in the search cell to get all texts. Save the workbook to a new file. We now have a full backup of all texts on your website.

We can apply this snapshot at any time by opening the workbook and clicking the Save button. Any new fields that were added to the website in the meantime will be left intact.

Editing multiple sites

The file can be used to make edits across multiple sites. To set this up, enter the connection strings to the MySQL databases to all of the sites you want to manage in the Config sheet. You can toggle the sites on/off with the Enabled column.

How to use this

If I’ve managed to sell you on the benefits, let me show you how to start using this:

  • Step 1: Download the Excel workbook.
  • Step 2: Download and install the QueryStorm runtime. The runtime is a free 4MB add-on for Excel that enables workbooks that are built using QueryStorm.
  • Step 3: Open the workbook and adjust the connection string to your WordPress database in the config sheet. If you don’t want to store your database credentials in the excel file, use placeholders instead and you will be prompted to enter your credentials when needed.
  • Step 4: Enter some text in the search cell to make sure it works.

I should note that this is entirely free. I would also advise using it cautiously, as you can’t undo changes to the database unless you’ve made a backup.