Drupal to WordPress migration
Posted on October 2, 2010
Updated: 8/19/2013 – 9seeds does not offer Drupal to WordPress conversion as a stand-alone service. If you need to convert from one CMS to another, we suggest you check out https://9seeds.com/cms2cms
A client came to us this week with a problem; he had a Drupal site with over 1000 articles that he wanted to convert to WordPress. So we headed to Google for some help. There are several tutorials out there on how to migrate Drupal content to a WordPress site, but many of them are really outdated. After kicking the tires on a few, this post by Mike Smullin seemed to be the best jumping off point.
Mike is right up front in his instructions with the fact that you’ll need to do a bit of tweaking to his set of instructions to get it to work for your setup. After a few failed attempts, we came up with an updated set of instructions. We’ve included the updated version along with notes about changes made.
These instructions are a set of SQL statements. It assumes you have a database named WordPress using ‘wp_’ as the prefix and another database named Drupal.
This should go without saying, but I’ll say it anyway; do not move forward without backing up your databases FIRST!
Clear all existing WordPress content
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;
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);
We added ‘article’ to the array on the last line to solve an issue with the way Drupal had categorized the posts.
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', 'article');
Turn articles in to posts
This will turn the ‘articles’ from the previous step in to ‘posts’ in WordPress.
update wordpress.wp_posts set post_type='post' where post_type='article';
Add post to category relationships
INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id) SELECT nid, tid FROM drupal.term_node;
Update category count
UPDATE wordpress.wp_term_taxonomy tt SET `count` = ( SELECT COUNT(tr.object_id) FROM wordpress.wp_term_relationships tr WHERE tr.term_taxonomy_id = tt.term_taxonomy_id);
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 comment counts
After fighting with the original syntax, I added the ‘use wordpress’ step to get around it.
use wordpress; 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/');
If your posts have any images you’ll need to move them from your ./files directory to the ./wp-content/uploads directory. The final step above should take care of fixing any image calls in your pages and posts, but you still may need to manually update them if you are having issues.
Download a copy/paste friendly version of the Drupal to WordPress instructions here.
if you have any questions, leave us a comment below.
16 CommentsLeave Comment
Excellent write up John. Saving this for the future cause you never know :)
Thanks, Chuck! Hope it saves you some time!!
I'm getting this error after running the Import posts/pages query: #1062 - Duplicate entry '6' for key 'PRIMARY'
Wow, thanks! Worked like a charm after correctly naming the databases. Thanks!
What version of drupal did you migrate from and what version of wordpress did you migrate to? Thanks!
Hey Greg, I'm not 100% sure which Drupal version, but I believe it was 5. As for WordPress, it was version 3.0
Worked well from drupal 6 to wp 3.1. had to remove the where clause in the post/pages query, and modify the "article to posts" to say where post type="blog". Great article. Helped me bunches.
Thanks for sharing your scripts! There are two aspects I don't see you mentioning: - Migration of users (so that posts and comments get linked to the same users that created them in Drupal) - How to managed to keep the same URLs Any suggestions on this?
Both good questions. On the user side, I wish I had a better answer for you than "no idea." We've done a couple Drupal->WP conversions, but it's definitely not something we do on a regular basis. The projects we've done haven't needed user migration, so I didn't have to dig in to that area. As for the URL structure, you may need to create some re-write rules to sort that bit out.
Thanks for your share. I have an error. I cannot migrate comments from drupal to wordpress. It just has content posts. How to fix it?
Unfortunately, I don't have an answer for you. We don't do Drupal to WordPress migrations on a regular basis, so it's not something we are continually learning more about. Hope you are able to find the help you need!
hi, thanks for posting this. Import posts and pages are not correct. 17 WHERE n.TYPE IN ('post', 'page', 'article'); must look like this: WHERE n.TYPE IN ('post', 'page', 'article', 'story');
As WP and Drupal change, the code needs to be tweaked. Thanks for the note.
Hi, It is not working for drupal 7.12
It is really a nice and helpful piece of info. I’m glad that you simply shared this helpful info with us. Please keep us informed like this. Thank you for sharing.
Hi and please accept my gratitude for sharing this info to everyone just like that! I don't know whether it would work for me or not, cause after several failed attempts I quit any coding efforts with my site and only entrust it to pros. recently, i stumbled upon a tool which promises to migrate Drupal to Wordpress not only quickly, but also for free. It's https://9seeds.com/cms2cms Do you think it is possible? I would like to know your opinion since you appear to know everything Drupal related.