Migrate/Convert/Import Drupal 5.x to WordPress 2.7

drupal-to-wordpressSo back in 2006 Drupal 5.x was introducing some cool new features to the open-source scene that had me wondering about the future of WordPress. Fast-forward to 2009, and WordPress has made some major changes since 2.5 that have made blogging fun again. Hooray! As trends continue to boom and bust, and creative masses are moving from technology to the next, the ability to take your content with you is invaluable. Here’s how I did it.

I debated whether I should post this because there are already at least five other guides at the time of this writing. But while D’Arcy’s was the most recent, a number of things had changed to where it still needed some tweaking to get right. I figured time is money and any time-saving tips are worthwhile. Hopefully this helps you at least for the next few versions of these platforms.

Note: While this gets you pretty darn close, you’ll still want to know a little about SQL, Drupal, and WordPress to customize this to fit your installation. Since Drupal was designed to be a CMS, not specific to blogging, there are multiple ways you could have configured the site to operate as a blog. Not knowing your setup ahead of time, I’m just going to show you mine. Here’s the SQL script to handle the import and conversion:


# Changelog

# 02.06.2009 - Updated by Mike Smullin http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/
# 05.15.2007 - Updated by D’Arcy Norman http://www.darcynorman.net/2007/05/15/how-to-migrate-from-drupal-5-to-wordpress-2/
# 05.19.2006 - Created by Dave Dash http://spindrop.us/2006/05/19/migrating-from-drupal-47-to-wordpress/

# This assumes that both wordpress and drupal are in separate databases. The wordpress database is called "wordpress" and the Drupal database is called "drupal"

# first, nuke previous content in wordpress database
TRUNCATE TABLE wordpress.wp_comments;
TRUNCATE TABLE wordpress.wp_links;
TRUNCATE TABLE wordpress.wp_postmeta;
TRUNCATE TABLE wordpress.wp_posts;
TRUNCATE TABLE wordpress.wp_term_relationships;
TRUNCATE TABLE wordpress.wp_term_taxonomy;
TRUNCATE TABLE wordpress.wp_terms;

# categories
INSERT INTO wordpress.wp_terms (term_id, `name`, slug, term_group)
SELECT
 d.tid, d.name, REPLACE(LOWER(d.name), ' ', '_'), 0
FROM drupal.term_data d
INNER JOIN drupal.term_hierarchy h
 USING(tid)
;

INSERT INTO wordpress.wp_term_taxonomy (term_id, taxonomy, description, parent)
SELECT
 d.tid `term_id`,
 'category' `taxonomy`,
 d.description `description`,
 h.parent `parent`
FROM drupal.term_data d
INNER JOIN drupal.term_hierarchy h
 USING(tid)
;

# posts; keeping private posts hidden
INSERT INTO wordpress.wp_posts (id, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, `post_status`)
SELECT DISTINCT
 n.nid `id`,
 FROM_UNIXTIME(n.created) `post_date`,
 r.body `post_content`,
 n.title `post_title`,
 r.teaser `post_excerpt`,
 IF(SUBSTR(a.dst, 11, 1) = '/', SUBSTR(a.dst, 12), a.dst) `post_name`,
 FROM_UNIXTIME(n.changed) `post_modified`,
 n.type `post_type`,
 IF(n.status = 1, 'publish', 'private') `post_status`
FROM drupal.node n
INNER JOIN drupal.node_revisions r
 USING(vid)
LEFT OUTER JOIN drupal.url_alias a
 ON a.src = CONCAT('node/', n.nid)
WHERE n.type IN ('post', 'page')
;

# post -> category relationships
INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
SELECT nid, tid FROM drupal.term_node;

# category count updating
UPDATE wp_term_taxonomy tt
SET `count` = (
 SELECT COUNT(tr.object_id)
 FROM wp_term_relationships tr
 WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
);

# comments; keeping unapproved comments hidden
INSERT INTO wordpress.wp_comments (comment_post_ID, comment_date, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url, comment_approved)
SELECT nid, FROM_UNIXTIME(timestamp), comment, thread, name, mail, homepage, status FROM drupal.comments;

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

# fix breaks in post content
UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, '', '');
# fix images in post content
UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, '"/files/', '"/wp-content/uploads/');

Things to be aware of:

  • Always backup first, and don’t try this on a live server.
  • Rename wordpress and drupal to the actual names of your databases.
  • Remember to move your images from ./files into the ./wp-content/uploads directory. My Drupal ./files directory followed the same ./YYYY/MM/DD/filename URL structure for blog post images and attachments but yours may need to be manually renamed.
  • You’ll likely need to use the WordPress Redirection plugin to preserve any link juice from your old permalink structure, where any changes occur.

19 thoughts on “Migrate/Convert/Import Drupal 5.x to WordPress 2.7

  1. Honal

    You might want to change the query to include blogs too to

    INSERT INTO startupmedia_wp.wp_posts (id, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, `post_status`)
    SELECT DISTINCT
    n.nid `id`,
    FROM_UNIXTIME(n.created) `post_date`,
    r.body `post_content`,
    n.title `post_title`,
    r.teaser `post_excerpt`,
    IF(SUBSTR(a.dst, 11, 1) = ‘/’, SUBSTR(a.dst, 12), a.dst) `post_name`,
    FROM_UNIXTIME(n.changed) `post_modified`,
    n.type `post_type`,
    IF(n.status = 1, ‘publish’, ‘private’) `post_status`
    FROM startupmedia_drupal.node n
    INNER JOIN startupmedia_drupal.node_revisions r
    USING(vid)
    LEFT OUTER JOIN startupmedia_drupal.url_alias a
    ON a.src = CONCAT(‘node/’, n.nid)
    WHERE n.type IN (‘post’, ‘page’,'blog’);

    and then add

    update startupmedia_wp.wp_posts set post_type= ‘post’ where post_type =’blog’

    this will make sure that the blogs are also copied

  2. Kin

    Thanks very much. I was importing from drupal 6 to wordpress 2.7 and I had to make some minor changes, for example, in drupal posts are saved as stories rather than posts. this meant the weren’t showing up. I just added this little find and replace to the end:

    UPDATE wp_posts SET post_type = REPLACE(post_type,’story’,'post’);

    I also had to make some changes to the comments import as it was throwing up some ‘database not selected errors’:

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

    Thanks again for the script, this was just what the doctor ordered :)

  3. Allen

    Thanks for updating/creating this script – for me it’s nearly perfect – two issues:

    1. the insert for the posts does not work – says duplicate id error – only way to get it to work was to change insert into to replace into

    2. the categories dont line up with the posts – is this because of the replace above?

  4. Vinnie

    this was a huge help, and better then the other posts i’ve come across (for instance, they didn’t get the comment counts working correctly).

    thank you.

  5. Pingback: Nils Schulte am Hülse

  6. fade

    In case category didn’t appear, use this in your wordpress database:

    update wp_term_relationships,wp_term_taxonomy set wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id where wp_term_relationships.term_taxonomy_id=wp_term_taxonomy.term_id;

  7. sangprabo

    Hi, thanks for such a great post. It really helps me. There is one thing I don’t understand:
    When we import drupal.comments into wordpress.wp_comments, approved comments become hidden/pending and vice versa. I think it’s because status field list in Drupal is set to 0 when comment is approved, but WordPress’s comment_approved is set to 1 when comment is approved.

    This sql command works for me
    UPDATE wordpress.wp_comments set comment_approved = (comment_approved + 1) % 2;

    Feel free to edit it as you wish. Ah, I’m using Drupal 5.7 and WordPress 2.8.6 by the way. Thanks again!

  8. Pingback: Revamping my website | Shufei Lei

  9. Aaron

    After scouring the net for hours, this is by far the definitive post on migrating Drupal 5.x to WordPress 2.7.

    In my case, I manage to move all my custom ‘article’ types from Drupal 5.2 to WordPress 2.9.2 with only a few little tweaks to this SQL.

    Thanks very much for sharing, and I hope this post makes it a little higher in the search engine to help others. Most other posts (once helpful I’m sure) deal with outdated versions.

    Cheers

  10. Pat J

    With the tweak from comment #3 (thanks Allen!), this worked for me, doing a Drupal 6.x -> WordPress 3.0 migration. Thanks a million!

  11. room34

    Thanks for this script! It was immensely helpful to me earlier this month as I migrated a client’s blog from Drupal to WordPress 3.0.

    It did require some changes, including a few things noted in the previous comments, plus a few updates of my own. I made a generic version of my own customization of the script, and posted it on my blog here:

    http://blog.room34.com/archives/4530

  12. Heinrich

    Finally after two days of deep searching i found this pretty SQL, in fact i’m writing a plugin to migrate from D6.x to WP3.x If anybody wants to contribute, just let me know in twitter @hnrch, if not, then wait until it appears in the wordpress plugin repository. Lot of thanks!!! :)

  13. Pingback: Drupal to WordPress migration | 9seeds, LLC

  14. Pingback: How I migrated a client’s blog from Drupal to WordPress 3.0 » underdog of perfection [ a blog on technology, music and geek culture from room34.com ]

  15. Pingback: Drupal to WordPress Migration | Smack Happy Web Design

  16. Keith Allen

    Thanks for this excellent script. we had several older Drupal sites that we converted to WP about a year or so ago.. and things basically went very smoothly. I meant to say thanks a long time ago, but was just going through my bookmarks and found your page again, so I figured I’d thank you now. – Keith

  17. Paula

    Hi guys, what about gConverter? My friend has asked them and they’ve converted his Drupal CMS to WordPress CMS and even Drupal Forum to Simple:Press forum plugin for WordPress, so he has got nice WordPress site with Simple:Press forum.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>