Convert – Import A Drupal 6 Based Website To WordPress v2.7

Drupal 6 To WordPress 2.7

Recently we had to convert a Drupal 6 based website Themestand over to WordPress v2.7 due to the amount of Database resources Drupal 6 was consuming on each page load, even for a pretty simple site we found Drupal very heavy on resources especially when users were logged in as the page cache is then inactive. This left us with a simple choice of do we try to further optimize a Drupal system thats already been heavily optimized or do we switch to a less MYSQL hungry system that we can modify to perform the same functions? Our chosen solution was to convert the entire site’s content from Drupal 6 to WordPress 2.7.

Their are various reasons as to why you may wish to convert a Drupal 6 based website to WordPress, maybe you want to convert a blog originally built upon Drupal to WordPress, it could be that you want a system thats a little easier to code themes for, or maybe you just prefer WordPress over Drupal, for us the reason was simply server load due to the amount of MYSQL queries drupal was consuming for what is not really a complicated website.

After looking around the web reading various articles on the subject of Drupal to WordPress conversion most of which are unfortunatley now outdated and with some trial and error we eventually figured out the MYSQL code and process needed to acheive a basic Drupal 6 to WordPress 2.7 site migration. In this article we will describe the steps needed to import your Drupal 6 website into a WordPress v2.7 install.

The actual database conversion from Drupal 6 to WordPress should take around 40 – 60 minutes, then you will have theme wordpress etc and check all the posts, categories and tags so all in all it may take you a few hours to complete this process in full.

Checklist

  1. Make sure you have 40 – 60 minutes that will be free from distractions, wife, kids, phone, dog, twitter etc.
  2. Do not under any circumstances perform this task on a live website, install WAMP or similar on your PC to use as a development server.
  3. Completely backup your Drupal 6 website files and database included.
  4. Have a fresh install of WordPress v2.7 installed and running, backup this also as it easier to just upload the DB file if you mess up during conversion instead of starting again from the beginning.
  5. Grab a coffee, tea, beer or personal weapon of choice and begin.

Step One

Install a fresh copy of WordPress v2.7 and it’s database to your development server our created database name was titled “tempdb” title yours the same if you do not want to edit any of our supplied SQL queries, this is the install we will be importing Drupal 6 into so check that wordpress is fully functional before continuing. Goto phpMyadmin and backup this database, also take note of which table have been installed by WordPress these tables always begin with wp_ as you will need to recognize these for later. Below is a list of the basic WordPress install tables within a fresh install.

  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_ratings
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

Now grab the SQL file backup of you Drupal 6 based website, goto phpMyadmin or similar and upload that database into the same database where you installed WordPress. This is very important as for conversion/import we need both the Druapl6 and WordPress 2.7 databases to be combined to begin with.

Once that step is complete we can begin to run the SQL queries with phpMyadmin that will import the data from your Drupal 6 database into the WordPress 2.7 database.

Drupal 6 To WordPress SQL Queries

This part of the process is carried out using phpMyadmin as we will be running SQL queries to import data from the Drupal 6 database into the WordPress v2.7 database.

phpmyadmin-sql

Goto the page above in your database as this is where we will be pasting the SQL queries to that are listed below.

First off we need to clean out any data that may reside within certain WordPress tables, copy, past and run the query below in phpMyadmin to do this.

IMPORTANT: the “tempdb.” that is within the code if your database is not named “tempdb” you will need to change all instances of  “tempdb” to the name of your actual database. So for example if your database is called “wordpress” it would look like this “wordpress.wp_comments;” etc.

Clean WordPress Before Import

1
2
3
4
5
6
7
TRUNCATE TABLE tempdb.wp_comments;
TRUNCATE TABLE tempdb.wp_links;
TRUNCATE TABLE tempdb.wp_postmeta;
TRUNCATE TABLE tempdb.wp_posts;
TRUNCATE TABLE tempdb.wp_term_relationships;
TRUNCATE TABLE tempdb.wp_term_taxonomy;
TRUNCATE TABLE tempdb.wp_terms;

Import Taxonomy Terms

This next query will import taxonomy terms.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO tempdb.wp_terms (term_id, `name`, slug, term_group)
SELECT
 d.tid, d.name, REPLACE(LOWER(d.name), ' ', '-'), 0
FROM tempdb.term_data d
INNER JOIN tempdb.term_hierarchy h
 USING(tid)
;
 
INSERT INTO tempdb.wp_term_taxonomy (term_id, taxonomy, description, parent)
SELECT
 d.tid `term_id`,
 'category' `taxonomy`,
 d.description `description`,
 h.parent `parent`
FROM tempdb.term_data d
INNER JOIN tempdb.term_hierarchy h
 USING(tid)
;

Import Post Content

This query will import Drupal post content to WordPress.

1
2
3
4
5
6
7
8
9
10
INSERT INTO
    tempdb.wp_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 tempdb.node n, tempdb.node_revisions r
WHERE n.vid = r.vid

Post and Category Relations Query

This query relates imported posts to their categories.

1
2
INSERT INTO tempdb.wp_term_relationships (object_id, term_taxonomy_id)
SELECT nid, tid FROM tempdb.term_node;

Category Count Updating

1
2
3
4
5
6
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 Query keeping unapproved comments hidden

1
2
INSERT INTO tempdb.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 tempdb.comments;

Update comments count on wp_posts table query

1
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

1
UPDATE tempdb.wp_posts SET post_content = REPLACE(post_content, '', '');

Fix images in post content

1
UPDATE tempdb.wp_posts SET post_content = REPLACE(post_content, '"/sites/default/files/', '"/wp-content/uploads/');

If all went smoothly all your Drupal 6 content should have been imported over to WordPress, you can now delete the Drupal database tables from your database leaving the wp_ tables intact.

Now instead of having a possible 100+ sql tables with Drupal and it’s Modules you have a small and compact eleven or so with WordPress that includes exactly the same content as was contained within your Drupal site.

Final Steps

Upload the content/images from your /sites/default/files folder that you backed up with Drupal to your WordPress /wp-content/uploads folder otherwise images for imported posts will not work.

The final steps of the procedure is to browse through your content checking, categories, tags etc looking for any errors misplaced posts. Fix any you come across then you can theme your new site up and release it to the world.

I hope this may help some by cutting out the many hours of research into this procedure that we needed to do before our conversion and any questions, feedback, suggestions, improvements you have after testing this method please leave a comment.

If you enjoyed this post, make sure you subscribe to my RSS feed!

Article Details

#

Author: on May 20th, 2009

Category: Drupal, Planet Drupal, Wordpress

Tags: , , ,

  1. Peter says:

    Thanks for this article, really helpful. I needed to transfer Users as well, here’s my code:

    Bring users over:
    INSERT IGNORE INTO wp_users SELECT NULL AS ID, NAME AS user_login, SUBSTRING(MD5(RAND()) FROM 1 FOR 30) AS user_pass, NAME AS user_nicename, mail AS user_email, ” AS user_url, FROM_UNIXTIME(created) AS user_registered, ” AS user_activation_key, 0 AS user_status, NAME AS display_name FROM drupal_users;

    Assign authorship:
    UPDATE wp_posts JOIN drupal_node ON title = post_title JOIN drupal_users ON drupal_users.uid = drupal_node.uid JOIN wp_users ON drupal_users.name = wp_users.user_nicename SET wp_posts.post_author = wp_users.ID;

    My field mapping was:
    Wordpress < Drupal
    ID < autoincrement
    user_login < name
    user_pass < generate something random, hash algorithms are different, can't transfer
    user_nicename < name
    user_email < mail
    user_url < ''
    user_registered < created, convert int(11) to datetime w/ FROM_UNIXTIME
    user_activation_key < ''
    user_status < 0 [depricated by wordpress: http://wordpress.org/support/topic/65971%5D
    display_name < name

    Cheers

  2. I don’t comment on blogs too often, but I like this post and theme – just wanted you to know that!     
    Thanks! 
    Thank You Note Wording

  3. xaviar xerexes says:

    Thanks for showing your work. If you do import users from Drupal into Wordperfect then you’ll want to add that to your post imports so authorship doesn’t get lost:

    INSERT INTO
    tempdb.wp_posts (id, post_author, post_date, post_content, post_title,
    post_excerpt, post_name, post_modified)
    SELECT DISTINCT
    n.nid, n.uid, FROM_UNIXTIME(created), body, n.title,
    teaser,
    REPLACE(REPLACE(REPLACE(REPLACE(LOWER(n.title),’ ‘, ‘-‘),’.’, ‘-‘),’,’, ‘-‘),’+’, ‘-‘),
    FROM_UNIXTIME(changed)
    FROM tempdb.node n, tempdb.node_revisions r
    WHERE n.vid = r.vid

    Still working on moving over the URL_alias from Drupal to WP slugs and images from Drupal’s IMAGE module to WP.

  4. Mike says:

    Hi,

    This looks to be exactly what I’m looking for. Unfortunately the sql queries result in errors – net result nothing imported into wordpress.

    Using PHPAdmin – I followed direction – took the drupal backup into my new tempdb with not problems.

    SQL errors:

    1. Import taxonomy terms
    error – tempdb.term_data does not exist (true – it does not).

    2. Import post content and Post&Category Relations query
    error – tempdb_node does not exist.

    3. Comments query keeping unapproved comments hidden
    error – tempdb_comments doesn’t exist.

    I’m trying to accomplish this on a Drupal 6.9 and a WordPress 2.9.1 installation – perhaps it will not work with WP 2.9.1.

    Cheers!

    Mike

  5. Mike says:

    Tried it with WordPress 2.7 – still the same errors.

    In PHPMYAdmin – I have for example – drupal_users and wp_comments

    Baffled.

    Cheers.

    Mike

  6. Ken Thomas says:

    This is like converting from a Saturn to a Yugo. The db usage of a normally configured Drupal site is noticeable, but not unusual– and I’ve certainly seen WP sites go far higher. Shame!

  7. Jason says:

    Is there anyone in NYC that has enough mysql experience to transfer a drupal 4 database over to wordpress (or ideally wordpress mu?).

    We ran the popular darcy norman script which got all the articles over but none of the images, but also encountering constant database freezes.. something glitchy in the articles that causes a white screen of death or server 500 timeout.

    In drupal/wordpress nightmare land, so would love to hire somebody who could execute this migration without a hitch.

    Thank you!!

  8. Patrick says:

    @Jason – You may contact Gloscon folks – http://www.gloscon.com – They have deep expertise in Drupal and may be of help. Although they are also moving away from Drupal to Ruby on Rails to my knowledge but still have a sizeable team that can help you with quick migration.

  9. Excellent stuff, my old website was Drupal based and now i’m in the process of moving to WordPress, this guide is going to cut a hell of a lot of frustrated swearing! 🙂 😛

  1. Blog von Drupal nach Wordpress migrieren | mmd42.de