Monthly Archives: January 2012

Migrating back to WordPress with regex and ‘tr’

I decided that I didn’t really feel like trying to manually upgrade ChintzyCMS to the latest version, so my personal site is now back running WordPress. I wasn’t interested in migrating comments, so if you’ve said something witty in the past you’ll need to look forward for new material.

There were a few tools that helped in the PostgreSQL to MySQL and import process. I’m still a bit upset that WordPress doesn’t support Postgres natively, but such is life.

  • First, I used phppgadmin to export the posts table as XML, which gives a <column> and <row> style output from the table. I deleted the header and column description  tags from the beginning and end of the file.
  • Using the guide at WordPress Codex – Importing Content, I performed the following search and replace operations based on the Importing from [X]HTML instructions:
    • For each column tag that needed to be replaced, I searched for the regex
      <column name="title">([^<]*)</column>

      and replaced it with the appropriate tag pair, such as

      <title>\1</title>
    • For each column that didn’t have an equivalent match in the WordPress database, I searched for the same regex and replaced it with an empty string.
    • I saved the resulting file out as posts.xml.
    • Using the UNIX tr utility described here, I removed all newline characters in the file:
      tr -d '\n' < posts.xml > posts_no_newline.xml

I then was able to import the posts_no_newline.xml file using the WordPress RSS Importer plugin.

There are still some posts that contain remnants from an ASCII to UTF-8 conversion (the new MySQL database stores content in UTF-8) which I’ll likely fix programmatically; when that happens, I’ll update with how that was performed.