Migrating your blog from PivotX to WordPress

Recently I converted my blog that ran on PivotX to WordPress. After nearly 10 years of using Pivot and PivotX, I’d noticed that PivotX seems end of life. WordPress now dominates the web with many different options, themes and plug-ins. Searching for ways to migrate my existing blog from PivotX to WordPress wasn’t really successful. Most information on migrating is old or fragmented, so I decided to write a short manual about exporting your data from PivotX to WordPress.

Step 1 (optional): get local WordPress installation
Install a local WordPress installation. While not essential, it’s nice to have your own WordPress installation on your own computer, so you can play around and eventually screw things up without creating serious problems. The internet is full of guides on how to install WordPress on your Windows, Mac or Linux machine so that shouldn’t be a problem. I installed WordPress 3.8 within an hour on my iMac.

Step 2: migrating your posts
Migrating your posts (without comments) isn’t really hard. You can get all entries using the following URL example.org/?feed=rss&c=*&n=10000. Obviously, replace this with your own domain name, and add a higher number if you have more than 10.000 posts. You’re browser downloads this document as an .xml file. Open the file with a text editor and remove the <![CDATA and ]]> that’s around every post with Find & Replace. Now go to your WordPress installation, and install the RSS importer. Follow the steps in the RSS importer and import the .xml file you’ve just downloaded.

Step 3: migrating comments
With a history of 10 years in PivotX, I also wanted to migrate the comments. This is a bit harder. As I don’t know anything about writing scripts, I figured out how to do that using Excel and phpMyAdmin (a tool that’s common when working with web servers). Get acces to the PivotX database with a tool like phpMyAdmin. Browse the different tables and locate the one with comments in it. Export the table as a .csv file. Now you have .csv file (that you can open with Excel) with all the comments, and the related posts in it.

Now get acces to your WordPress database, again, with phpMyAdmin. Study your WordPress database and you’ll find out that in the table with all the posts, all posts have their unique ID. I’d call that WordPress Unique ID. But not only the unique ID according to WordPress is displayed, also the unique ID from PivotX is displayed (table: wp_posts, column: guid). So you can basically match both unique ID’s. (I use Excel for that).

Now go to the table wp_comments in WordPress. Here you’ll find comments, combined to the WordPress unique ID. Cool: now we can match comments from the original Pivot installation to the WordPress installation. Download this table as a CSV, open it in Excel and use the VLOOKUP function in Excel to match all the comments, comment authors and their addresses to the right ID’s. Fill the stuff you don’t know about with NULL. Take care to use the right date formatting. Save and import this table into phpMyAdmin again.

Step 4 (optional): move your local files online
Having a local installation of WordPress made it easy to play around. Moving your installation online is easy: WordPress has a built in import/export feature. Export the complete stuff on your local installation, and import it online.

What wasn’t migrated
While I’m happy I was able to convert posts and comments, I wasn’t able to convert tags and images. I couldn’t find out how tags work in WordPress, it seems somewhat more complicated than in PivotX. I didn’t use tags that often, so I couldn’t be bothered that much.

On images: actually, I just forgot trying to take them with me. Now I’m stuck with hundreds of posts with broken links to images. To save the day: all images are on my hard disk, so I have a backup. Still, that’s a lot of work to manually replace them all. Now I’m waiting for a few rainy weekends with nothing else to do (which could mean waiting for a long time). I guess I should’ve tried to copy the pivotx/images folder to the new installation and see if that works.

Comments or questions on this guide? Add them below and I’ll update this short manual with most important remarks.