Fuzzy Thoughts of David

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.

Want to be notified when I post? Subscribe!


Posted

in

by

Tags:

Comments

33 responses to “Moving From Drupal 7 to WordPress 3.3”

  1. Grover Campos Avatar
    Grover Campos

    What about users?

    1. David Avatar
      David

      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.

  2. Noah Avatar
    Noah

    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.

    1. David Avatar
      David

      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
      ;

      1. Noah Avatar
        Noah

        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

        1. David Avatar
          David

          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.

          1. Noah Avatar
            Noah

            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
            ;

    2. Noah Avatar
      Noah

      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.

      1. David Avatar
        David

        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!

  3. Mathew Avatar
    Mathew

    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.

  4. […] posts, así que migrarlo manualmente no era una opción. Investigué varios links, en particular este, y encontré un conjunto de scripts para migrar, que tuve que modificar y adaptar a mi […]

  5. Stefano Avatar

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

  6. TJ Avatar
    TJ

    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

    1. David Avatar
      David

      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.

      1. Stefano Avatar

        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 :D

        1. David Avatar
          David

          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.

          1. Stefano Avatar

            Sorry, I was referring to your scripts :P

  7. […] How I migrated a client’s blog from Drupal to WordPress 3.0 Moving From Drupal 7 to WordPress 3.3 cms2cms Det här inlägget postades i Hemsidor, Personligt och märktes cms, Drupal, januari, […]

  8. Martin Avatar
    Martin

    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.

    1. David Avatar
      David

      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!

  9. robyn Avatar
    robyn

    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

    1. David Avatar
      David

      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.

  10. […] not much the other way around. After getting freaked out reading a number of posts which involved editing databases, I realised the easiest option was just to suck in the content on […]

  11. Alex Avatar
    Alex

    I spent a lot of time with that scripts. But recently i found tool that moves content with the help of establishing the connection bridge between Drupal and WordPress sites. cms2cms moved my content really fast. Here’s video that may help you to proceed with this migration http://www.cms2cms.com/supported-cms/drupal-to-wordpress-migration

    1. David Avatar
      David

      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!

  12. Wparena Avatar

    now the best option is to use non programming tool to migrate from Drupal to WordPress

  13. Sergio Avatar

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

  14. ali Avatar

    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

  15. Liran Tal Avatar

    Hi David,

    I just wanted to update that I ended up forking the github repository which is based on this article and improved it with supporting the migration of users, fixing Drupal blog posts importing and a bunch of other additions to it.

    My improved github repo if you’d like to review: https://github.com/lirantal/Drupal2Wordpress

    Liran Tal

  16. Liran Tal Avatar

    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/

  17. Claude Avatar

    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,

  18. winstontwin Avatar
    winstontwin

    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.

  19. Liran Tal Avatar

    There’s actually a Udemy course that I’ve made available and covers how to migrate a Drupal 7 website to latest WordPress 3 edition (3.9) with a quick script. Check it out: http://udemy.com/step-by-step-drupal-7-to-wordpress-39-migration/

Leave a Reply to DavidCancel reply

%d bloggers like this: