Moving From Drupal 7 to WordPress 3.3

I’ve been running this site on Drupal for quite a while. Back in March I decided to move the site to Drupal 7. While I really like Drupal, there are some things that I would like to do, but just can’t seem to be able to get worked out.

One of the main things I’ve wanted to do was use Windows Live Writer, or my phone to post to the blog. I’ve messed with Blog_api to no avail. While there has been some work in that area, it seemed like there wasn’t enough interest, so the only way I could really post was to go to the website….which I didn’t do very often.

After trying out the WordPress app on my Android phone I realized my best option was to move the site to WordPress. That is _much_ easier said than done. So, for those of you who want to move your Drupal site to WordPress, I want to outline the _basic_ steps. I do this knowing full well that my site isn’t completely functional. I will get to that in a moment.

The best article I’ve seen about moving to WordPress from Drupal is here: http://blog.room34.com/archives/4530 The only problem is he was moving a Druapl 6 site to WordPress. Of course the table names, structures, etc. are different in Druapl 7. So, I took his script (you can get it from the URL above), and had to tweak it a bit.

Basically I ran his script section by section. Since the script doesn’t touch the Drupal database, you can run it over and over again and your Drupal site will remain in tact. I did not run all of his script, just the things I needed.

To use these scripts you’ll have to replace “drupal” with your drupal DB name and wordpress, with your WordPress DB name.

TAGS

The tag section attempts to fill the wp_terms, wp_term_relationship, and wp_term_taxonomy tables. I had to comment out the INNER JOIN because I was having issues with it and couldn’t remember how INNER JOINs worked exactly. I was too lazy to look it up.

# TAGS
# Using REPLACE prevents script from breaking if Drupal contains duplicate terms.
REPLACE INTO wordpress.wp_terms
(term_id, `name`, slug, term_group)
SELECT DISTINCT
d.tid, d.name, REPLACE(LOWER(d.name), ‘ ‘, ‘_’), 0
FROM drupal.taxonomy_term_data d
INNER JOIN drupal.taxonomy_term_hierarchy h
USING(tid)
#    INNER JOIN drupal.term_node n
#        USING(tid)
WHERE (1
# This helps eliminate spam tags from import; uncomment if necessary.
# AND LENGTH(d.name) < 50
)
;

INSERT INTO wordpress.wp_term_taxonomy
(term_id, taxonomy, description, parent)
SELECT DISTINCT
d.tid `term_id`,
‘post_tag’ `taxonomy`,
d.description `description`,
h.parent `parent`
FROM drupal.taxonomy_term_data d
INNER JOIN drupal.taxonomy_term_hierarchy h
USING(tid)
#    INNER JOIN drupal.term_node n
#        USING(tid)
WHERE (1
# This helps eliminate spam tags from import; uncomment if necessary.
# AND LENGTH(d.name) < 50
)
;

The next thing I did was run the script for the posts. I think I had to play around with this script a bit to get it to finally do what I wanted. I’m sorry, but I can’t remember for sure if this is what I landed on or not. NOTE: The code below is not functional. You will receive an error if you try to run it. If I get a chance I’ll figure out what I did to get the posts into Drupal.

INSERT INTO wordpress.wp_posts
(id, post_author, post_date, post_content, post_title, post_excerpt,
post_name, post_modified, post_type, `post_status`)

SELECT DISTINCT
n.nid `id`,
n.uid `post_author`,
FROM_UNIXTIME(n.created) `post_date`,
r.body_value `post_content`,
n.title `post_title`,
r.body_summary `post_excerpt`,
n.type `post_type`,
r.entity_id `rid`,            <- There is an issue here. Haven’t had a chance to fix this yet.

IF(n.status = 1, ‘publish’, ‘private’) `post_status`
FROM drupal.node n, drupal.field_data_body r
WHERE n.vid = r.entity_id
;

This SQL creates the posts to tag relationships:

# POST/TAG RELATIONSHIPS
INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
SELECT DISTINCT nid, tid FROM drupal.term_node
;

SQL to create the tag counts:

# Update tag counts.
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
)
;

 

Next I ran the SQL for the comments:

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 DISTINCT
nid, FROM_UNIXTIME(created), comment_body_value, thread, name,
mail, homepage, ((status + 1) % 2)
FROM drupal.comment, wordpress.field_data_comment_body
;

 

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

I cannot remember if I ran this next SQL statement or not:

# Fix taxonomy; http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-wordpress-27/#comment-27140
UPDATE IGNORE wordpress.wp_term_relationships, wordpress.wp_term_taxonomy
SET wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_taxonomy_id
WHERE wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_id
;

A couple of problems I ran into:

Because I had Drupal setup to do aliases, my URLs were all off. My main concern was with the traffic I was getting from Google. I tried to setup WordPress to do aliases in the same way, which it will do, however it uses the variable postname. However, in the database table wp_posts the post_name was blank. So I had to take the post_title and try to replace the spaces with dashes, and strip out all the other stuff I didn’t want in a URL. The result was mixed. I had to run a series of REPLACE commands replacing various characters I would use in a title (such as “?”, “!”) with nothing.

Again, this post is simply a guide. If you want to convert your Drupal 7 site to WordPress you need to take the information from the Drupal 7 database and shove it into the WordPress database. The scripts from the URL above will give you a good start. Hopefully some of the SQL statements I used will help as well.

Leave a Reply

    • That’s a great question. Since I wasn’t worried about users (I didn’t have any), I didn’t do anything with the user databases. The issue, I believe, would be with the user password. WP has a users table while Drupal has a users and users_roles table and it would be pretty simple to get the username from Drupal and push it into WP.

      In order to get the password from Drupal and push it into WP it would have to be encrypted. If the encryption scheme between WP and Drupal are the same, then I _believe_ you could just insert the data from Drupal into WP. I doubt that’s the case. That means you would have to pull the username, password out of Drupal, unencrypt it using Drupal’s scheme, then encrypt it using WP’s scheme and then insert it into WP.

      I don’t know SQL well enough to know if you can unencrypt/encrypt the data…so it would probably have to be done in a PHP script.

      Sorry I’m not much more help. If I find some type I might play around with it some and update this page.

  1. When I run the posts part I keep getting #1109 – Unknown table ‘n’ in field list.

    Any idea what’s up there?

    I’ve tried pulling out all of my mods to see if one of them was the problem, but doesn’t seem to be.

    • The problem would be the line:

      FROM drupal.node n, drupal.field_data_body r
      WHERE n.vid = r.entity_id

      If I remember correctly, the “FROM drupal.node n” part would make a reference “n” to drupal.n. drupal would be the name of your database so drupal.node would be the table named “node” within the database named “drupal.” After issuing “FROM drupal.node n” you can refer to the table drupal.node by simply using the letter “n.”

      Hope that helps…but to give a fuller example:

      If my drupal database is called myDrupal I should have a table called node. So the command would be: “FROM myDrupal.node n”

      Also remember that this line is _one_ command even though there are empty lines.

      INSERT INTO wordpress.wp_posts
      (id, post_author, post_date, post_content, post_title, post_excerpt,
      post_name, post_modified, post_type, `post_status`)

      SELECT DISTINCT
      n.nid `id`,
      n.uid `post_author`,
      FROM_UNIXTIME(n.created) `post_date`,
      r.body_value `post_content`,
      n.title `post_title`,
      r.body_summary `post_excerpt`,
      n.type `post_type`,
      r.entity_id `rid`,

      IF(n.status = 1, ‘publish’, ‘private’) `post_status`
      FROM drupal.node n, drupal.field_data_body r
      WHERE n.vid = r.entity_id
      ;

      • Hmm… with:

        INSERT INTO noaharney_com_1.wp_ik3hys_posts
        (id, post_author, post_date, post_content, post_title, post_excerpt,
        post_name, post_modified, post_type, `post_status`)
        SELECT DISTINCT
        n.nid `id`,
        n.uid `post_author`,
        FROM_UNIXTIME(n.created) `post_date`,
        r.body_value `post_content`,
        n.title `post_title`,
        r.body_summary `post_excerpt`,
        n.type `post_type`,
        r.entity_id `rid`,

        IF(n.status = 1, ‘publish’, ‘private’) `post_status`
        FROM drupal_noaharney.node n, drupal_noaharney.field_data_body r
        WHERE n.vid = r.entity_id

        I now get:

        #1136 – Column count doesn’t match value count at row 1

        • Sorry about that Noah, I was trying to show how to reference a table in the FROM statement.

          The error you received is because the number of items you are INSERTing into your database is different than what you are SELECTing. It looks like the full command got truncated (see the comma after “r.entity_id ‘rid’,” I believe there should be something after that.

          I believe I still have my drupal database hanging around, but I’ve already converted the wordpress one, so right now I’m not in a position to test out the SQL. The thing that has to happen is to make sure that the number of items being INSERTed matches the SELECT statement. I hope that makes sense and is helpful.

          Again, sorry for the confusion and the bad code. I hadn’t noticed it earlier. I’ll have to make a note in the post.

          • After monkeying around a bit I got this to work:

            # POSTS
            # Keeps private posts hidden.
            INSERT INTO noaharney_com_1.wp_ik3hys_posts
            (id, post_author, post_date, post_content, post_title, post_excerpt, post_type, `post_status`)
            SELECT DISTINCT
            n.nid `id`,
            n.uid `post_author`,
            FROM_UNIXTIME(n.created) `post_date`,
            r.body_value `post_content`,
            n.title `post_title`,
            r.body_summary `post_excerpt`,
            n.type `post_type`,

            IF(n.status = 1, ‘publish’, ‘private’) `post_status`
            FROM drupal_noaharney.node n, drupal_noaharney.field_data_body r
            WHERE n.vid = r.entity_id
            ;

    • The key to my problem was adding in
      n.title `post_name`,
      FROM_UNIXTIME(n.created) `post_modified`,

      and removing
      r.entity_id `rid`,

      Not sure if this is going to break it, but it’s going ok so far.

      • It’s good to hear that you are making progress. I liked your phrase “monkeying around” because sometimes that is what you have to do. As long as you are just doing selects from your Drupal database and inserts into a new and unused wordpress database you can get things by trial and error.

        It would really be nice if someone could write a script that works…but doing it yourself means you can customize it the way you want!

  2. I noticed a few things as I was tinkering this for my migration. My `post_content`s weren’t lining up with the correct row. I modified one particular section and it worked fine after that.

    WHERE n.nid = r.entity_id

    instead of using n.vid, which was throwing them off for me.

    I also don’t understand why this line in the original wasn’t working for `post_status`
    IF(n.status = 1, ‘publish’, ‘private’) `post_status`
    I had to add a quick UPDATE to get that field corrected.

    Additionally, the Drupal `post_type` is ‘article’ where in WP it is ‘post’. Added in an UPDATE for that as well, and now it looks good.

  3. Pingback: Migración de Drupal 7 a Wordpress 3.4 | Soliverez

  4. Here’s the query for users I used
    INSERT INTO blog2.`wp_users`(`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_registered`, `display_name`)
    SELECT DISTINCT –no ID to not break auto increment
    d.name `user_login`,
    d.name `user_nicename`,
    d.pass `user_pass`,
    d.mail `user_email`,
    FROM_UNIXTIME(d.created) `user_registered`,
    d.name `display_name`
    FROM blog.users d
    WHERE 1;

    The problem with autoincrement (I thought that later) would be solved if we set the autoincrement counter max(ID) +1 before running this query (so adding d.user_id `ID` )

  5. Hi, David! thanks for taking your time and sharing this information! I appreciate it. however, even looking at the code makes me feel sick- I am not really technically savvy, so I don;t think I’ll ever be able to convert to WP myself (no matter how much ‘monkeying around’ I do:)

    There;s one site which offers help to users like me http://www.cms2cms.com. It says. they can move the content over to WordPress for no charge (since the tool is new, I think). What do you think, is it worth trying? To me, the offer is very attractive, but who knows what it may turn out to be.

    Have a great day,
    TJ

    • Yea, the code is kind of pieced together. It is only helpful if you know what you are doing.

      I think the cms2cms would be worth trying. If you are able, I would create a duplicate of the source site and have cms2cms convert that to a new one. That way if anything “bad” happens, you know that your source site is fine. Although, they say they don’t make any changes to the source, so if you trust them, you can try it.

      I did see that it did not copy over user passwords (since they are encrypted) and it doesn’t look like it does media yet. Given how difficult it has been to convert from Drupal 7 to WordPress, I would at least check it out. If I get time I might try transferring my Joomla site. Probably won’t be until the beginning of next year. I’ll make sure to report back!

      If you try it, report back here. I’m sure others would like to know how it worked out! – Peace, David.

      • Yep, I had to reset the passwords.
        Then I approved existing comments
        UPDATE blogh2.`wp_comments` SET `comment_approved`=1;

        Another thing was that the conversion didn’t preserve the urls, all the posts where in format /date/id instead of /date/article.

        Manually changed all 😀

        • URLs…I did see they had something in the FAQ about the URLs probably changing. This is from their FAQ:

          “Will the URL structure of the original site be maintained on the new site after migration?
          The URL structure will change, because each CMS uses its own way to build URLs for pages . If you don’t have too many posts, you can use permanent redirects to avoid 404 errors on your original URLs.”

          So, it sounds like it worked out, for the most part. Looks a lot easier than what I (and others) have had to do! Glad they have the tool and that it is free while in beta.

  6. Pingback: Ännu mer Drupal | Tankeskärvor och bildfragment

  7. I spent quite a bit of time with custom SQL conversion scripts that I found through Google searches. All were unsuccessful.
    I scoured the net for conversion of these and, in the end, opted to use http://gconverter.com/drupal-to-wordpress/ services. They were certainly the cheapest and I found them very informative and useful. You don’t pay until you’re happy as well which is a big plus.
    They converted all data and kept my URLs the same, I have not any problem with permalink. Also they cared about my custom content types and custom fields. I got identical WordPress site with identical the same Theme.
    Also they migrated my Drupal Forum to bbPress.
    Good luck with it though, I’m sure every one’s requirement are different to mine but I’m pretty happy now.

    • Thanks. After reading your comment, I realized another potential issue with the scripts on this page is that WordPress might change some of the database structure. Nevertheless,these scripts will help get some started. Finding a company that does the migration would be simpler, although I couldn’t find any when I did my conversion.

      Thanks for the input!

  8. hello, when you migrated the site from drupal to wp, were you able to keep the urls in tact? i appreciate any help you can provide.
    God bless you 🙂
    Robyn

    • It was a mixed bag with the URLs. I can’t remember exactly what the issue was, but in the post I wrote,

      “Because I had Drupal setup to do aliases, my URLs were all off. My main concern was with the traffic I was getting from Google. I tried to setup WordPress to do aliases in the same way, which it will do, however it uses the variable postname. However, in the database table wp_posts the post_name was blank. So I had to take the post_title and try to replace the spaces with dashes, and strip out all the other stuff I didn’t want in a URL. The result was mixed. I had to run a series of REPLACE commands replacing various characters I would use in a title (such as “?”, “!”) with nothing.”

      It was over a year ago that I moved things from Drupal to WordPress, so I really don’t remember specifics other than what I wrote. I do remember that it was a pain because I had to do quite a few SQL REPLACE commands on the database to fix the titles of the articles. The URLs are dynamically created based on title, date, etc. In the end, I think most of the URLs ended up being the same, but I’m not sure if all of them did.

  9. Pingback: Moving from Drupal to Wordpress | Tangible Signs

    • Someone else mentioned that site. I do hope it works well because working with the scripts and the database is not the best approach for most people. Thanks for the link!

  10. I have to change two things:

    to posts

    INSERT INTO wordpress.wp_posts
    (id, post_author, post_date, post_content, post_title, post_excerpt,
    post_type, `post_status`)
    SELECT DISTINCT
    n.nid `id`,
    n.uid `post_author`,
    FROM_UNIXTIME(n.created) `post_date`,
    r.body_value `post_content`,
    n.title `post_title`,
    r.body_summary `post_excerpt`,
    n.type `post_type`,
    IF(n.status = 1, ‘publish’, ‘private’) `post_status`
    FROM drupal.node n, drupal.field_data_body r
    WHERE n.vid = r.entity_id
    ;

    to relationships

    INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT nid, tid FROM drupal.taxonomy_index
    ;

    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 );

  11. hi david
    david when i run script on wamp server show this error how can i fix it
    Drupal2Wordpress-master is run on pc and wam server
    if yes where do of file chang to it i change

    //Database Host Name
    $DB_HOSTNAME = ‘localhost’;

    //Wordpress Database Name, Username and Password
    $DB_WP_USERNAME = ‘wordpress’;
    $DB_WP_PASSWORD = ‘wordpress’;
    $DB_WORDPRESS = ‘wordpress’;

    //Drupal Database Name, Username and Password
    $DB_DP_USERNAME = ‘drupal’;
    $DB_DP_PASSWORD = ‘drupal’;
    $DB_DRUPAL = ‘birah’;

    //Table Prefix
    $DB_WORDPRESS_PREFIX = ‘wp_’;
    $DB_DRUPAL_PREFIX = ”;

    //Create Connection Array for Drupal and WordPress
    $drupal_connection = array(“host” => “localhost/x/”,”drupal” => $DB_DP_USERNAME,”root” => $DB_DP_PASSWORD,”” => $DB_DRUPAL);
    $wordpress_connection = array(“host” => “localhost/s/”,”jadidfa” => $DB_WP_USERNAME,”root” => $DB_WP_PASSWORD,”” => $DB_WORDPRESS);

    but no answer of this service

  12. Even though I’m a seasoned Drupal developer, even authored a book on Drupal 7 Media, and presented the topic on a local Drupal conference, I decided to migrate Drupal 7 to WordPress. Drupal is suitable for many web applications, although it does require quite an effort to maintain and setup in order to fit it to your needs, while with WordPress most of the blogging capabilities are available out of the box with almost no hassle, and for a good reason – WordPress was primarily developed as a blogging platform.

    You can read more on my blog post about converting/migrating Drupal 7 to WordPress 3.9 – http://enginx.com/blog/migrate-drupal-7-to-wordpress-3-9-kickoff/

  13. I presently have my website under Drupal and would like to transfer/redevelop the content under WordPress while keeping the same domain name. Is this possible?
    Cheers,

  14. Thanks for sharing! Importing Drupal to WordPress is really a big check, especially for web novices (like me:)). I made use of CMS2CMS service and all my website content was transferred to WordPress in a fully automated way. There occurred some technical problems, mainly because my Drupal site had a lot of customized things. Anyway, this method is much cheaper and quicker than hiring a programmer.