in X-Geek |

Drupal To WordPress Migration Script

I’ve done some tweaking of the Drupal to WordPress migration SQL script that’s been bouncing around. I’ve put in SQL to migrate the user table. I’ve also used the REPLACE MySQL extension to change old links to my blog, as well as change the ‘–break–‘ tags to ‘–more–‘ tags.

Here’s my copy, where

  • wp_ has been replaced by mt_ (for my mt.net database tables)
  • mtdotnetMigration is the name of my imported Drupal migration database
  • www.markturner.net is my website address (to convert references in the content to mt.net posts)

If you use this script, you should change these values accordingly.

# this script is modified after the recipe provided at
# http://spindrop.us/2006/05/19/migrating-from-drupal-47-to-wordpress
#
# updated 2007/05/28 with the comment count updater script from
# http://www.brendanloy.com/2007/02/wordpress-21-upgrade-problems.html
#
# this assumes that both wordpress and drupal are in separate databases. The wordpress database is called “wordpress” and the Drupal database is called “mtdotnetMigration”

# first, nuke previous content in wordpress database
use mtdotnet;
delete from mt_categories;
delete from mt_posts;
delete from mt_post2cat;
delete from mt_comments;

# categories
INSERT INTO
mt_categories (cat_ID, cat_name, category_nicename, category_description, category_parent)
SELECT term_data.tid, name, name, description, parent
FROM mtdotnetMigration.term_data, mtdotnetMigration.term_hierarchy
WHERE term_data.tid=term_hierarchy.tid;

# posts
INSERT INTO
mt_posts (id, post_date, post_content, post_title,
post_excerpt, post_name, post_modified)
SELECT DISTINCT
n.nid, FROM_UNIXTIME(created), body, n.title,
teaser,
REPLACE(REPLACE(REPLACE(REPLACE(LOWER(n.title),’ ‘, ‘_’),’.’, ‘_’),’,’, ‘_’),’+’, ‘_’),
FROM_UNIXTIME(changed)
FROM mtdotnetMigration.node n, mtdotnetMigration.node_revisions r
WHERE n.vid = r.vid;

# category –> post relationships
INSERT INTO mt_post2cat (post_id,category_id) SELECT nid,tid FROM mtdotnetMigration.term_node ;

# category count updating
UPDATE `mt_categories` SET `category_count` = (SELECT COUNT(`post_id`) FROM `mt_post2cat` WHERE `mt_categories`.`cat_ID` = `mt_post2cat`.`category_id`);

# comments
INSERT INTO
mt_comments
(comment_post_ID, comment_date, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url)
SELECT
nid, FROM_UNIXTIME(timestamp),
comment, thread, name, mail, homepage
FROM mtdotnetMigration.comments ;

# update comments count on mt_posts table
UPDATE `mt_posts` SET `comment_count` = (SELECT COUNT(`comment_post_id`) FROM `mt_comments` WHERE `mt_posts`.`id` = `mt_comments`.`comment_post_id`);

# fix post slugs. first we have to remove the duplicate _____ chars, then replace that with a single – char
UPDATE mt_posts set post_name = REPLACE(post_name, ‘__’, ‘_’);
UPDATE mt_posts set post_name = REPLACE(post_name, ‘__’, ‘_’);
UPDATE mt_posts set post_name = REPLACE(post_name, ‘__’, ‘_’);
UPDATE mt_posts set post_name = REPLACE(post_name, ‘__’, ‘_’);
UPDATE mt_posts set post_name = REPLACE(post_name, ‘_’, ‘-‘);

# migrate users
INSERT INTO
mt_users
(ID, user_login, user_pass, user_nickname, user_nicename, user_email, user_registered)
SELECT
uid, name, pass, name, name, mail, FROM_UNIXTIME(created)
FROM mtdotnetMigration.users
WHERE uid > 1;

# turn all –break– tags into –more– tags
UPDATE mt_posts set post_content = REPLACE (post_content, ‘–break–‘,’–more–‘);

# turn all example.com?q=node/1234 links to example.com?p=1234 links
UPDATE mt_posts set post_content = REPLACE (post_content, ‘www.markturner.net/?q=node/’,’www.markturner.net/?p=’);
UPDATE mt_posts set post_content = REPLACE (post_content, ‘www.markturner.net/?p=’,’www.markturner.net/?p=’);
UPDATE mt_posts set post_excerpt = REPLACE (post_excerpt, ‘www.markturner.net/?q=node/’,’www.markturner.net/?p=’);
UPDATE mt_posts set post_excerpt = REPLACE (post_excerpt, ‘www.markturner.net/?p=’,’www.markturner.net/?p=’);

If your browser Nifonged that text you can download the script here.

Use at your own risk. If it breaks, you get to keep both pieces. Don’t run with scissors, etc., etc.

Published