Migrating from Drupal to Wordpress
I finally decided to move my greek blog from drupal to wordpress. Since there was no migration script, I wrote a couple of sql statements that moved all posts, comments and categories from my drupal tables to the (new) wordpress 1.5 tables.
Here is the proccess in short:
WARNING!!! This may delete your DATA!!! Make sure you backup EVERYTHING before starting the procedure!!!
1. setup a fresh wordpress installation.
2. make sure term_data term_hierarchy node term_node comments (drupal tables) are in the same DB you use from WP.
3. Run the following SQL statements:
delete from weblog_wp_categories ;
delete from weblog_wp_posts;
delete from weblog_wp_post2cat ;
delete from weblog_wp_comments ;
insert into weblog_wp_categories(cat_ID,cat_name, category_nicename, category_description, category_parent) select term_data.tid, name, name, description, parent from term_data, term_hierarchy where term_data.tid=term_hierarchy.tid ;
INSERT INTO weblog_wp_posts(
ID, post_date, post_content, post_title, post_excerpt, post_name, post_modified
)
SELECT nid, FROM_UNIXTIME(created), body, title, teaser, concat('OLD',nid), FROM_UNIXTIME(changed) FROM node WHERE type='blog' OR type='page' ;
INSERT INTO weblog_wp_post2cat (post_id,category_id) SELECT nid,tid FROM term_node ;
INSERT INTO weblog_wp_comments (
comment_post_ID, comment_date, comment_content, comment_parent
)
SELECT nid, FROM_UNIXTIME(timestamp), concat('',subject, '<br />', comment), thread FROM comments ;
You should now have all your posts and comments and categories in WP. Go to the admin interface and make sure everything is in place…
Notes: This is not the perfect way to migrate. Comments are not nested in the right way. A lot of things may not work. On the other hand if, like me, made a really simple use of Drupal, this should move most of your data to WP…
You can modify the comment part of the SQL statements so that the commenter’s name, email and home page are retained instead of entering each as anonymous. Change to this:
INSERT INTO wp_comments (
comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_date, comment_content, comment_parent
)
SELECT nid, name, mail, homepage, FROM_UNIXTIME(timestamp), concat(’‘,subject, ‘
‘, comment), thread FROM comments ;
I KISS YOU!
Absolutely wonderful, works flawlessly (well, ok, after I poke thing to allow for the fact that I have more than one database, and that I didn’t want the ‘page’ nodes imported. But wow, lovely. Thank you.
I’m trying to decide between Drupal and WordPress right now for blogging. Why are you converting to WordPress? Why is it better than the older Drupal? I’d really like to know! Thank you.
Very, very cool. Thanks for posting this, my friend. If you’re ever in Ann Arbor, MI, look me up and I’ll buy you a beer, or several beers.
Thanks very much for publishing this. It saved me hours of time trying to figure out how to do this on my own. In my case, the Drupal blog was running out of a different database than the WP blog, so I had to create the Drupal post table (I hadn’t been categorizing the posts), but that was simple enough to do.
Thanks again! Dan
Thanks very much for this. I’m in the process of testing a migration from Drupal to Xoops where I’ll be using the WP plugin. Posts and categories went through with no problem. Comments import are throwing sql errors, so I’ve got a bit of figuring to do yet.
Here’s a cleaned up and more complete set of queries. My drupal database is called “blog” and my wordpress database is called “wordpress”. Also, I’m at -0800 to GMT.
–Wipe out existing content
DELETE FROM wordpress.categories;
DELETE FROM wordpress.posts;
DELETE FROM wordpress.post2cat;
DELETE FROM wordpress.comments;
DELETE FROM wordpress.users
WHERE ID > 1;
–Copy users
INSERT INTO wordpress.users (ID, user_login, user_pass, user_nicename,
user_email, user_registered, display_name)
SELECT uid, name, pass, name, mail, FROM_UNIXTIME(created), name
FROM blog.users
WHERE uid > 1;
–Copy over categories
INSERT INTO wordpress.categories
(cat_ID, cat_name, category_nicename, category_parent, category_count)
SELECT term_data.tid, name, name, parent, count(term_node.tid)
FROM blog.term_data
INNER JOIN blog.term_hierarchy ON (term_data.tid=term_hierarchy.tid)
LEFT JOIN blog.term_node ON (term_node.tid = term_data.tid)
GROUP BY term_data.tid
–Copy over all the blog posts, pages and forum topics
–All will be posts in wordpress
INSERT INTO wordpress.posts
(ID, post_author, post_date, post_date_gmt,
post_content, post_title, post_excerpt, post_status, comment_status,
ping_status, post_name, post_modified, post_modified_gmt)
SELECT nid, uid, FROM_UNIXTIME(created), FROM_UNIXTIME(created + (60*60*8)),
body, title, teaser, ‘publish’, ‘open’, ‘closed’,
concat(’node-’,nid),
FROM_UNIXTIME(changed), FROM_UNIXTIME(changed + (60*60*8))
FROM blog.node
WHERE type IN (’blog’, ‘page’, ‘forum’);
–Copy the post to category
INSERT INTO wordpress.post2cat (post_id, category_id)
SELECT nid,tid
FROM blog.term_node ;
–Copy the comments and associate them with the top level blog post (threading lost)
INSERT INTO wordpress.comments
(comment_ID, comment_post_ID, comment_date, comment_date_gmt, comment_content, comment_approved, comment_parent)
SELECT cid, nid, FROM_UNIXTIME(timestamp), FROM_UNIXTIME(timestamp + (60*60*8)),
concat(”, subject, ”, comment), 1, CONVERT(thread, UNSIGNED)
FROM blog.comments;
ALWAYS GRATEFUL!
You saved me literally hours and hours of gruelling, repetitious work. Your work does not go unnoticed!
It worked like a charm, except for the comment count part.
Please use this
UPDATE wp_posts, node_comment_statistics SET wp_posts.comment_count = node_comment_statistics.comment_count WHERE wp_posts.ID=node_comment_statistics.nid;
Cheers
I rewrote some SQL lines in my blog (click my name) so that this will work for Drupal 4.7. I decided to convert as well.