Note: This will work for converting up to the latest version of Drupal to the latest version of WordPress, as of January 1, 2015.
After hunting down some tutorials and finding some missing important parts, no explanations, and being disappointed, I had to do some experimenting. As a developer, I do not mind solving a challenge and putting puzzles together. There were some decent tutorials, however, I found that once I completed my own Drupal to WordPress conversion, I needed to share the process in the easiest way possible.
Please note that that this tutorial to convert Drupal to WordPress is not giving you the goods on how to convert your stat counters over, if you need to make sure your Disqus comments match up (in the case you use Disqus instead of the content management system’s native comments), or special modules that are storing information in the database. You will need to seek building a extended custom plan for you to convert Drupal to WordPress.
Some of the issues I ran into were questions that other developers had with whether tables could be converted over as well as users. In fact, a lot of the questions I saw were over the user conversion from Drupal to WordPress. Also, this was a problem that was not addressed in some of the tutorials to convert Drupal to WordPress. Some of the scripts did not properly work, or did not have anything about converting over users.
I also found issues with the categories and tags not being translated over to the correct taxonomy when doing my conversion. In fact, they were all slopped into the tags in most cases. My solution is before converting to make sure in the original Drupal install that these taxonomies are correctly labeled. Tags are non-hierarchical and Categories are hierarchical.
Another big problem was running into duplicate errors when feeding the SQL through the conversion process. The solution for this was to check the last known table that was converted over and only convert from after that table. It was tedious, especially since my conversion was for 17,000 posts and over 2,500 users. What a way to break in figuring out how to convert Drupal to WordPress, right?
This post goes over how to convert Drupal to WordPress, mostly the core the database (like posts, post meta, taxonomies, and post types, users, and comments), and a little bit about converting the theme.
Convert Drupal To WordPress: The Database
1. Export the Drupal SQL file from the old host.
2. Install a fresh new WordPress site. You need to clear the tables with the TRUNCATE command by going into your database’s SQL queries tab in phpmyadmin. The code is:
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;
Replace the wordpress before the period with the database table name before executing the query. In the case you are doing the query from that WordPress database directly, you do not need the wordpress and the period in the code when executing the script.
3. If you have multiple users to convert over, you will need to use the following code.
DELETE FROM wordpress.wp_users WHERE ID > 1; DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;
Again, replace the wordpress before the period with the database table name before executing the query.
4. The following query will convert over tags:
REPLACE INTO wordpress.wp_terms (term_id, `name`, slug, term_group) SELECT DISTINCT d.tid, d.name, REPLACE(LOWER(d.name), ' ', '_'), 0 FROM drupal.term_data d INNER JOIN drupal.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.term_data d INNER JOIN drupal.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 ) ;
5. The following converts over posts.
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 `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)
# Add more Drupal content types below if applicable.
WHERE n.type IN ('post', 'page', 'blog')
;
Please note that if your Drupal installation has several post types, you will want to add the name of the post type into the to the line that says WHERE n.type IN (‘post’, ‘page’, ‘blog’). If you do not add all post types, then not all will be converted over.
6. In the case you want to combine post types in WordPress. Say you wanted post and blog together, you would put the following code:
UPDATE wordpress.wp_posts
SET post_type = 'post'
WHERE post_type IN ('blog')
;
7. The following code defines the post/ tag relationship
INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id) SELECT DISTINCT nid, tid FROM drupal.term_node ; # Update tag counts. 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 ) ;
8. The following code is for 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(timestamp), comment, thread, name, mail, homepage, ((status + 1) % 2) FROM drupal.comments ; # 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` ) ;
9. If you are keeping your Drupal images and filed in the same place, you do not have to do anything. However, if you are FTP-ing your files to the uploads folder in your WordPress wp-content folder, you will want to use the following script to fix the URLs to the images.
UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, '"/files/', '"/wp-content/uploads/');
10. The following code is suppose to help fix taxonomy… that is if it was set up correctly in the original Drupal site.
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 ;
11. Author roles for your users:
INSERT IGNORE INTO wordpress.wp_users
(ID, user_login, user_pass, user_nicename, user_email,
user_registered, user_activation_key, user_status, display_name)
SELECT DISTINCT
u.uid, u.mail, NULL, u.name, u.mail,
FROM_UNIXTIME(created), '', 0, u.name
FROM drupal.users u
INNER JOIN drupal.users_roles r
USING (uid)
WHERE (1
# Uncomment and enter any email addresses you want to exclude below.
# AND u.mail NOT IN ('test@example.com')
)
;
This sets the author role’s permissions.
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
SELECT DISTINCT
u.uid, 'wp_capabilities', 'a:1:{s:6:"author";s:1:"1";}'
FROM drupal.users u
INNER JOIN drupal.users_roles r
USING (uid)
WHERE (1
# Uncomment and enter any email addresses you want to exclude below.
# AND u.mail NOT IN ('test@example.com')
)
;
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
SELECT DISTINCT
u.uid, 'wp_user_level', '2'
FROM drupal.users u
INNER JOIN drupal.users_roles r
USING (uid)
WHERE (1
# Uncomment and enter any email addresses you want to exclude below.
# AND u.mail NOT IN ('test@example.com')
)
;
You may want to uncomment (remove the number sign) before the line that has the email address and put your own in so your role remains as an administrator.
12. Assign and give administrator status.
UPDATE wordpress.wp_usermeta
SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
WHERE user_id IN (1) AND meta_key = 'wp_capabilities'
;
UPDATE wordpress.wp_usermeta
SET meta_value = '10'
WHERE user_id IN (1) AND meta_key = 'wp_user_level'
;
13. The following code helps assign authors to the posts they wrote.
UPDATE wordpress.wp_posts
SET post_author = NULL
WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users)
;
14. Depending on how the original Drupal site has been set up, sometimes the URLs are funny, try feeding the following query. It does not hurt to try.
UPDATE wordpress.wp_posts SET post_author = NULL WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users) ;
15. The following is some extra clean up for the editor so your posts do not look too funky from the conversion.
UPDATE wordpress.wp_posts
SET post_name =
REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1))
;
There are quite a few other options, but I found these the most necessary for my install. In the case you want to look through for more options, you can check out the Drupal to WordPress Migration at the Underdog of Perfection.
I did run into one last thing that was a problem to convert Drupal to WordPress. Once I had everything over, I went to click on the individual posts and pages. I did already set the permalinks to postname. However, the links kept redirecting back to the home page. I had to update the slugs.
To do so, you need to put the following code in the wp-config.php file before the ending PHP tag. Once you have, click save and then reload the front of your site. The script will run and should not take long at all. Once the front of your site loads, go back into the wp-config.php file and remove the code.
// We prepare a variable to hold arrays with post titles so we don't accidentally make duplicates, that would be bad
$slug_done = array();
// Run a query to grab all the posts, we only want posts/pages though
$posts = $wpdb->get_results( "
SELECT
`ID`,
`post_title`
FROM
`" . $wpdb->posts . "`
WHERE
`post_type` = 'page'
OR
`post_type` = 'post'
" );
// Loop through results
foreach( $posts AS $single )
{
// Generate a URL friendly slug from the title
$slug_base = sanitize_title_with_dashes( $single->post_title );
$this_slug = $slug_base;
$slug_num = 1;
// Check if the slug already exists, if it does, we add an incremental integer ot the end of it
while (in_array( $this_slug, $slug_done ) )
{
$this_slug = $slug_base . '-' . $slug_num;
$slug_num++;
}
$slug_done[] = $this_slug;
// We are happy with our slug, update the database table
$wpdb->query( "
UPDATE
`" . $wpdb->posts . "`
SET
`post_name` = '" . $this_slug . "'
WHERE
`ID` = '" . $single->ID . "'
LIMIT 1
" );
}
Convert Drupal To WordPress: The Theme
If you are familiar with, HTML, PHP, CSS, WordPress theming, and Drupal theming, great! Most people usually write about how to convert Drupal to WordPress, and not talk about the theme conversion, especially for those who love their theme and just want to switch the content management system. You should know that the concepts of theming in WordPress are similar in Drupal. You design the theme, and in the parts that you want WordPress to do the work, like pages, posts, and widgets, you place the code with the coordinating loop and dynamic sidebar PHP call to those areas. If you are not familiar with theming, you can refer to my podcast presentation that includes a slideshow on PSD to WordPress, which delves a little deeper into WordPress theme templates.
You can literally remove all the codes for modules and such from the Drupal theme templates and replace with the WordPress code.
I hope this helps explain the steps to convert Drupal to WordPress. Please use at your own risk and always have a back up before you remove anything for good.
Great informative site, i appreciate you…I am a new reader on this site, am happy to read many post…Thank you so much..
Hi Nile,
thank you very much indeed for these easy-to-use steps and clear explanations. To be honest, I am fascinated with your ability to find simple words for explaining these complicared issues. Thank you very much once again.
Thanks for giving useful steps for conversion .I have found many useful tips here
Wow..i just can say that these kind of explanations are very very helpful. I work with WordPress and I was wondering if there was a way to convert WordPress to Drupal. Like you said, there are some tutorials but this explanation is very good and i like to hear it from you if it is possible. Tnx
Hi Aleksandar! I am pretty sure there is a way to do the opposite and much of it would most likely be using the same code above, but opposite on the query commands. However, I normally convert sites to WordPress, because it is much easier for people.
I heard that drupal was alo a big deal in the blogosphere, but I never used it, I won’t really be needing this since I rarely find look for any drupal themes
Oh My Gosh!
I wouldn’t wish this on anyone. I don’t know nothin’ bout no Drupal and hope I don’t ever have to convert it to WordPress!
But Thanks for sharing anyways!
LOL!
~ Jupiter Jim
(please delete the parentheses — Nile, don’t worrry, this ain’t my comment for the week. Just had to share this thought!)
I think with this tutorial you’ll make a lot of drupal users happy.
Hmmm….converting from drupal to wordpress. The first thing you must do is BACKUP your drupal site before you try this. This is because something unpredictable could happen during conversion.
I believe I covered up that in the article.
Hello,
Thanks for the informative post. I will try the same and if will need any further guidance will approach you.
Thanks and Regards,
Nishant Desai | SEO Expert India
I like the method you used to explain this from one step to another. I really appreciate your good work here.
Thank you so much for this article! I right now use both Drupal and WordPress in their own separate worlds, but if I ever need to convert, I know where to go to! Bookmarked! Thanks again!!
Wow! Thank you so much.. you’re a real lifesaver. I had a client today who came to me with his Drupal site and a wanted me to edit the site for him, but I was totally clueless about Drupal. Got it converted to WordPress thanks to you! Keep on rocking 🙂
Kind of a lengthy process, but worth it in the end. Still a lot easier than what it was in the 90’s. That time was pure chaos for web developers.
Not familar with Drupal, looks like a nightmare doing this though. Some sites we have are Joomla sites and I’m not a fan. It’s more sophisticated but not as simple :-p
Very useful and informative post about how to convert the drupal to wordpress.
Ahhhh!!! you are so professional!!! Check my site and give your uneasy so i can fix it!
Whoa! Thanks a lot a great deal.. you’re a true lifesaver. I’d a customer at the moment whom located anyone together with his Drupal web site and a noticeably necessary anyone to switch the website meant for him or her, web hosts on the other hand I has been totally clueless concerning Drupal. Started using it turned into WordPress on account of somebody! Proceed rocking.
This was a LIFE SAVER. I was at the brink of going crazy trying to figure this out. Thank you so much!
thanks for sharing this info! It’s awesome! the fact that you really share this instead of just keep it to yourself or make money…
The truth is, I am not capable of doing anything more than publishing content on my Drupal site, cause I am afraid to break something (which happened several times already) But I’ve found a tool at http://www.cms2cms.com that migrates Drupal to WordPress like without any efforts on my site and for free (this is the weirdest thing:)
Do you think I can try it?
Sorry, but your wonderful script is something too complex for me to use…
I am sure you can try it out. It seems to be in beta version. If you do try it out and it works great, let me know. 🙂 Just make sure to have a backup of your original Drupal site before trying the migration. 🙂
The script is one that is open source and worked for me from scripts that had some missing areas. I am just sharing my experience and hope it helps.
That is no longer free. I tried it and it converted about 10 posts/users. Then asked me to pay $50 to convert more. After doing some research online someone else had to pay over $100.
They just entice you to use the software, and then pressure you to pay after you get the conversion done at 25% or less.
So Nile’s method is good to use as a free option, though I do know some people feel scared to even look or touch code.
I also have a converter that does everything for you no coding skills required.
I’m not allowed to post the link here, but you can always search google. If you know some code or feel okay with it then go with Nile’s route.
You did it again Nile! Great Post! Thank you!
As wordpress provides all the features without any head ache of coding. I don’t have any idea about Drupal but I think wordpress is best at this time.
I would like to say you nothing more than a Thanks. I was looking for a tutorial to covert one of my website from Drupal to WordPress and Got it. Thank you.
Great informative site, i appreciate you…I am a new reader on this site, am happy to read many post…Thank you so much..
hello Nile..
I never used drupal yet ,But I learn converting procedure for my future.May be this will be very helpful to me in future.Thanks for nice sharing.
I haven’t used drupal as I am always connect with wordpress. As it is the nice interface for blogging. Thanks for this tutorial for converting drupal to wordpress.
I am a new user to this blog. so informative. i learned few methods from your site. Drupal is another CMS like wordpress. but i am completely new to this. anyway it will be useful in future. i will practice with this. thank you
Arghh… I wish I had come across this before moving my site over. I’ve already populated a lot in WordPress, but I still want to import my older Drupal articles into WP.
Hi – I am a newbie to wordpress. I’m happy to have found this tutorial but I have a really basic question. I want to make sure I follow the steps correctly.
1) So When I export the Drupal database, I’m assuming that I I then import it to the fresh wordpress install? Or I make the truncation changes on the database before importing it to wordpess?
You will want to have the drupal tables on the same web hosting account as your WordPress install before converting over… that is why you have to label your database tables properly in order to do the conversion. You don’t have to have the Drupal database in the same exact database as WordPress… just the same web hosting account’s MySQL. So if i were converting here, I’d more than likely put my drupal database in blondish_drpdb and convert it over to blondish_wrdp as an example.
Thank you for the clarification. You are extremely helpful. But just to be sure…
1) copy the original database and place it in the wordpress site.
2) Does the fresh wordpress install have to happen AFTER putting the database there? I already installed WordPress.
3) make of copy of that original database and make the changes to the new database as listed above – so that there is an original drupal database and a modified/converted wordpress database on the same site.
Please excuse my “blondness” 🙂 (I use to be anyway…)
Hi Heather!
You do not have to put the Drupal database into the same database as WordPress. You can create a new mySQL database and import the database through MySQL.
However, if you do want to convert the Drupal database in the same one as WordPress, just import the sql or compressed sql file of the Drupal database into the WordPress one. Now, once you are done converting, you can select the Drupal tables and drop them. You may need to take notes on what tables to actually drop so you don’t accidentally delete a WordPress table.
You don’t have to have a fresh install of WordPress when you are done converting, but like I said above, and recommend… remove the Drupal tables if you are indeed working in the same database account as your WordPress installation. I hope that helps.
Yes, that helps. Thanks again. Okay… so here I go…! Wish me luck. I’ll report back soon. 🙂
Wait – so the first part – deleting the users greater than 1, doesn’t make sense to me. Why would I do that? Sorry… but I have the users already listed just fine so I don’t understand why I should delete them…
This is to remove the users. If you have users in drupal, you will need to remove any that you have in WordPress except for 1, which is your admin. If you don’t have more than one, you can skip this step.
The proper answer here is that you DO have to delete users >1. Why? because if you have a user with the user ID 2 in WordPress already and your drupal database has another user with a user ID 2. Then when you port over the drupal user2 it will either overwrite the wordpress user2 or it will not port that user over. In this case you now have a problem…..
That’s why you need to delete the users on the wordpress end or better yet start WP from a fresh install. That way you’ll only have 1 user with the user ID 1.
Thank you so much! This was exceptionally helpful.
I can call myself an expert in Drupal and PHP, but I haven’t worked on any conversions yet, as I do have a separate team working on stuffs like that.
I will pass over this info to my team, might be of use in the future. But most of my customers come in to get converted to Drupal and not otherwise. But I loved reading this post as you are so precise with your steps in conversion.
Hi there,
I used your SQL to import the “posts” and “pages” into WordPress. When I go into wp-admin, everything is listed under pages, and nothing is under posts. In the wp-db, post_type correctly contains all the types from drupal, “blog, image, story, etc” any thoughts?
Thanks!
also, any tips for getting images into the media library?
Thanks!
You could move the images to your uploads folder and change the links to direct the path there as per one of the steps. The only issue is that you will not be able to manage them in the Media Library unless you load them from the WordPress admin.
You have to either register a custom post type in WordPress, OR, you will have to change in your Drupal database the line for the post type. blog and story are normally for post.
Hi thanks for replying. But it doesn’t explain why everything is listed as a page in wp. What is the correct value for a “post” and “page”? In your code example above it might be useful to indicate that “WHERE n.type IN (‘post’, ‘page’, ‘blog’)” will certainly pull the content, but it won’t correctly produce posts in wp. wp expects post_type=’post’ to display as post and you’ll have to convert everything else to ‘post’ or register the custom post_types.
Additionally, images are stored as:
post_type=”attachment”
guid=”URL”
post_mime_type=”image/jpeg”
Thanks for the article, it definitely helped jump start the process. 🙂
If you don’t want blog, don’t put it in your MySQL. post_type for posts are ‘post’ and ‘page’ for pages. If you want other post_types like image or other, like blog, you can still carry those over, BUT… you are going to have to create a custom post type (register it in your themes functions.php or install the Custom Post Types UI plugin) for blog or story, or anything else.
Thank you – the sql excerpts were fantastic.
Hi,
Looks like this is exactly what I need. However, I am not sure at what stage I should bring in the actual data from the exported Drupal .sql? Should this be the last step after all the SQL statements are run? If yes, shouldn’t this be pointed out in the article?
On the third step I get “#1142 – SELECT command denied to user ‘my_user_name’@’localhost’ for table ‘term_data’ “.
Thanks for your help.
Hi,
Did you find a way to import the media attachments via SQL?
There are some scanners in WordPress that will grab from the original site and import. Right now, the Image Scanner plugin available in WordPress is old and only works on individual posts, and not the mass one. I’d like to see someone fork it over and work with it. I may try to fork the plugin over and hopefully it could help.
Thanks so much for the article.
I’ve attached an updated sql query from inserting posts & pages from drupal 7 –> wordpress 3, for posterity’s sake.
You’ll need to change one of the IF statement in the select clause to make sure the correct drupal node type turns into a post (our node type was “article” in 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`,
rb.body_value `post_content`,
n.title `post_title`,
rb.body_summary `post_excerpt`,
IF(SUBSTR(a.alias, 11, 1) = ‘/’, SUBSTR(a.alias, 12), a.alias) `post_name`,
FROM_UNIXTIME(n.changed) `post_modified`,
IF(n.type = “article”,”post”,n.type) as `post_type`,
IF(n.status = 1, ‘publish’, ‘private’) `post_status`
FROM drupal.node n
INNER JOIN drupal.node_revision r
USING(vid)
INNER JOIN drupal.field_revision_body rb ON rb.entity_id = r.nid
LEFT OUTER JOIN drupal.url_alias a
ON a.source = CONCAT(‘node/’, n.nid)
# Add more Drupal content types below if applicable.
WHERE n.type IN (‘article’, ‘page’)
;
Hmm… may try it but the tutorial worked on the last Drupal 7 to WordPress 3.8.1.
Thank you very much. Your code worked for me. I had only replace your quotation marks 😉
Hey,
This is a great post, I am about to convert a very very heavy website to WordPress, I hope I can do it. Is there any way I can be in direct contact with your via Email or something like that.
Hi Abdul! If you’re asking me to consult with you even through email for your Drupal to WordPress conversion, I do charge per hour – $50/hour flat rate. You’re welcome to use my contact form to ask, but I’m no longer answering questions via email for free because I’ve had to give up time in the past.
Awesome! Works for me: Drupal 6 to WordPress. Now that old dog can finally sleep!
Hi,
I imported my drupal 7.25 directly into my wordpress 3.9 install. the wordpress wp is actually 5ma. The drupal tables have nothing in from of the names. I keep getting this error:
#1054 – Unknown column ‘n.body’ in ‘field list’
Please help!
Here is my sql. accessed from phpmyadmin:
INSERT INTO 5ma_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`,
n.body `post_content`,
n.title `post_title`,
n.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 node n
INNER JOIN node_revision r
USING(vid)
LEFT OUTER JOIN url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
# Add more Drupal content types below if applicable.
WHERE n.type IN (‘post’, ‘page’, ‘blog’)
;
your name is wrong for your database table, which you are missing…. and you need the period before it. Usually if your converting, your converting to a fresh WordPress install with default tables
drupal.term_data not found in drupal 7
It’s because you didn’t put in your database table’s correct name.
So I decided to code a converter after finding none out there. It’s pretty indepth since I was doing it for a clients website.
I posted more details here if anyone is interested.
I removed your link as it was posted in a forum. You need to put a website up. The directions in this tutorial are complete and still work even on Drupal 7.
thanks for the awesome tutorial. i am having a problem with converting the tags. whenever i run the queries i get the following error:
Duplicate entry ’77-post_tag’ for key ‘term_id_taxonomy’
any help is greatly appreciated.
HI Philip. If the database your’re converting from is a duplicate of the original (I hope it is), go to the Drupal database and remove the tag giving the problem. Then run the query through again. This is like the issue I had with the posts giving a problem with the primary key. I’m thinking something got corrupted. You should still have the taxonomy, but you just need to remove it now in order to proceed.
thanks for the help. unfortunately, i’m still having problems. but it’s okay, the client is only worried about the last 6 months worth of posts (which isn’t a lot) so they will just manually convert the tags.
i did notice something, the SQL for steps 13 and 14 are the same. just thought you should know.
thanks again for the help.
Hello Nile,
I’m a designer from Miami, FL,
I want to thank you for this post very illustrative and useful!! Thanks a lot!!
I wonder if you could recommend me some effective guide for theming wordpress.?
Hi there, I’m trying to do this but I am having the HARDEST time getting it to work. Hoping you can please help me out…
This is what I’ve done….
1.) exported drupal database
2.) set up a wordpress site
3.) in myphp admin, Iv’e imported the drupal sql database
now this is where I get stuck…
4.) I’ve pasted the TRUNCATE tables (with this):
# Empty previous content from database.
TRUNCATE TABLE .wp_comments;
TRUNCATE TABLE .wp_links;
TRUNCATE TABLE .wp_postmeta;
TRUNCATE TABLE .wp_posts;
TRUNCATE TABLE .wp_term_relationships;
TRUNCATE TABLE .wp_term_taxonomy;
TRUNCATE TABLE .wp_terms;
5.) when I try to paste the step 4 of your tutorial (even when removing the “wordpress” in front of the periods), I get the following error: MySQL said: Documentation
#1142 – SELECT command denied to user ‘wordpress_5f’@’localhost’ for table ‘term_data’
Can you please let me know what might be going wrong? Would be amazing to get this to work…
It should work. Hopefully you didn’t add the drupal tables into the same database you were converting over, or it won’t work right.
Hmm. maybe that’s where I’m confused… since what I’m trying to do is set up a wordpress site on a server that’s DIFFERENT than the already live and hosted drupal site…
I have access to the live site’s drupal database though which I’ve exported – So, from here, am I supposed to set up a SEPERATE drupal database on the dev server, called “drupal”, and another wordpress database, where in my phpadmin it knows to look for the “drupal” database and port things over? Sorry if that’s confusing…
I thought the process involved importing the drupal database tables into the wordpress ones as well… which is incorrect?
Thanks for replying so quickly.
Mike,
Did you ever figure this out? I know it’s been almost a year since your comment, but I’m running into the same issue & I’m not sure exactly where I’m supposed to import the Drupal database if not into the same database as the WP tables.
You import it into a fresh WordPress database.
——–
You will have a backup sql of Drupal.
You will have a database of that Drupal install for reference.
You will have a fresh copy/ database of WordPress to work with.
You only need to make sure to add your database name in the parts that are drupal., for your drupal database, and wordpress., for your WordPress. You will be working in that WordPress installs SQL tab.
Thanks, Nile! I had left the drupal. in there & it was causing the error message. All set now. Great article & very nice of you to continue to respond to those of us hitting our heads against the proverbial wall.
Not a problem. Better to reply in the comments and I can go back to edit the article in the future for anything you all have had issues understanding. 🙂
“Another big problem was running into duplicate errors when feeding the SQL through the conversion process. The solution for this was to check the last known table that was converted over and only convert from after that table. It was tedious…”
Could you elaborate on this, I too am getting stuck here. I have several post types but even if I pull almost all of them out, I always get a “#1062 – Duplicate entry ‘14405’ for key ‘PRIMARY’ ”
I’m not sure I know what you mean about the “last known table” and start after that. As far as I can see nothing ever gets converted, so not sure what the last known table would be in the scenario.
thx so much, this blog post is amazing.
It is happening when I try to copy the posts over. Once I get the error, I select from wp_posts and it is still empty so it must just rollback out.
Like I said in the other comment, you need to search for the line that had the error and drop that line.
Look up the entry and see if it did convert. Then, go back to the Drupal copy that you’re converting and delete that table. It’s also a possibility that if it didn’t convert, it’s corrupted, so make a notation and after the conversion, you WILL have to manually insert the post into it. It’s strange when it does happen, but it’s nothing to panic over unless the data in the original Drupal is not there at all.
Is there any WordPress plugin to do automatic Drupal to WordPress migration service for free? Steps described by you in above post are too technical and hard to do.
There are no plugins at this point in time.
I’ve used your notes to migrate a Drupal site to a temporary WP site (http://whitesalmonevents.com/wsnetdrupal2wp) and it did surprisingly well.
However, I’m only seeing 1 post in the dashboard while the database shows 595 rows in the wp_posts table. I’m not an experienced database person so I don’t really know what to do to get the wp_posts to register on the site.
Suggestions???
There’s a possibility you didn’t pay attention to the post types when you did the database. If you had blog, story, news, etc… and you needed all of them to be posts, you needed to include them in when you brought in the database. After that, you needed to do the step to turn blog to posts, story to posts, etc so that the post format was correct.
However, if you intend to keep those post formats, then you need to use the Custom Post Types UI plugin and create the blog and story post types… the slugs MUST be the same as in Drupal.
Thanks,
I did the import step with all the different content types,but in my ignorance I didn’t convert each of the types to posts. Once you pointed that out it came over fine.
Unfortunately, all the drupal categories are rendered as tags rather than WP categories. That’s something I can fix manually, but I’m wondering if I could have populated the categories with what are now tags?
Thanks again for the help, Lloyd
Still checking the transfer site and find there are also 57 rows in wp_terms but no Categories in the Dashboard.
Also, I FTPed the image files/folders to wp-content>uploads. I ran the script to change the image URLs in the posts but they aren’t registered in Media. Did I do this wrong?
Sorry to be so needy, this is a new stretch for me and I really appreciate your post.
You’re going to have to redo the categories as all taxonomies in Drupal will lop themselves into the tags taxonomy. There is a plugin called Simple Tags that can help – http://wordpress.org/plugins/simple-tags/
You won’t see the images in the media library to be able to preview… kind of sucks. I know there is a plugin called Image Cache that if the existing website is still up, you can import. However, mass importing would be bad. The plugin is free and searchable in the WordPress plugin director, but it is old.
Thanks again,
Simple tags made conversion of tags to categories easy, and it kept the assignments to the posts.
For bulk uploading images and files to Media the “Add From Server” plugin is working well, except it doesn’t maintain the image links to the posts and pages.
Thanks again, Lloyd
I’m at step 5 and I keep getting term_data table not found. I checked the drupal database and there’s no table with such name in it. I have Drupal 7.31. Can you please help?
Hi! I’d only be able to help if hired. It’s hard to tell unless I can run it through and produce the same results.
Thank you for this, it has been a great help.
I had to modify a number of queries as my drupal set up was different to yours, but it provided a great starting point.
Thanks.
Hi, I’m new around PHP MyAdmin and I’ve been trying to copy/paste your code into the queries tab for some time and with no result. When I click the queries tab, I do so after having clicked on the database title (it’s called wordpress). So far I’ve had 0 results. Surely, I must select the appropriate tables in the queries tab before submitting my query. I’m really at a loss and I’ve been trying to follow these instructions for a week.
Hi JP!
Are you sure you put the information in correctly and adjusted the code to put in your wordpress and drupal database names?
Hi, so in the end I see I must enter these queries in the MYSQL console instead of the queries tab in phpmyadmin. I was just about to remove my post!
I’m still facing issues with queries not being recognized such as: INNER JOIN drupal.node_revisions r (my table here would be drupal.node_revision) but when i remove the s I get another error. Another example is my inexistant drupal.term_data table which makes even the second query impossible! I’m not out of the woods yet…
replace drupal of drupal.node_revisions and other similar queries with the database name from the database you’re converting from. same thing with WordPress.
My database names are drupal and wordpress (I named them according to the names given in the instruction in the hope it would save me some tweaking). I guess we can diagnose that the table names still aren’t recognized.
Migrated from Drupal 6 (single author) to WordPress 4.1.1, it just worked. The only thing i had to modify was the drupal content type which, in my case, was called story instead of post. Thanks a lot!
Awesome! I’m glad it helped. Yep, sometimes you do have to add the new post types into the SQL query. 🙂
There’s alloy of good stuff here, and I’m sure that in time I’ll greatly increase my understanding of the Drupal and WP databases, but the Drupal version these scripts address seems very different from my D7 one.
First, all of my taxonomy stuff is in tables called taxonomy_term_%, and I assume my equivalent of term_node is taxonomy_term_data. Next the SQL in step 5 refers to r.body. My node_revisions table does not have a field called body.
I’ll probably still get there in time, and your stuff does help greatly, so thank you.
I have a client who has a drupal site and a wordpress site. The old developer does not want me ftping into the host server so they sent me the drupal database backup and all the files from the server folders….one for the wordpress site and one for the drupal site.
If I was to place that into a folder on my bluehost server do you think it would work after creating a new database and using the backup file to populate it? My thinking is that after I have full access to the drupal site and database converting it over to wordpress might be easier. Your thoughts please?
You need to create 2 backups of the Drupal database first… that is my recommendation. Load one up to your development site’s database. Use the other as a just in case you need it.
If the database is over, 55MB, you will need to load the sql file to your web host and ask BlueHost to restore the database for you so you can work on it. ALWAYS keep your Drupal copy and your WordPress on SEPARATE databases.
Your client will still need to give you FTP details eventually, and there is a way to log in to phpmyadmin without having access to everything.
Hi,
I felt lucky finding this article to convert our Drupal site to WP. I was able to execute all queries.
But stuck at the script in last script. I don’t see any closing PHP tag and hence I pasted it at the end of wp-config.php and now I am getting error. Is there anything need to be removed/replaced like $wpdb?
Is there SQL version for this script, so it can be easily execute. May be as a stored procedure.
Hi Subba!
You need to add the code before the last ?>
Thanks for providing all this info, Nile. You’ve obviously learned a lot and you’re very gracious to provide it to everyone.
I have another question and I wonder if you would know the answer. My client’s site has a number of products on it and I’m going to be putting them into WooCommerce on WordPress. Woo has a post type of “products” obviously, so I’m wondering if it was possible to take what post type Drupal has set up as “products” and convert them over to the WP version? Would that mean I’d need to set up a script run that does just that set?
I’m assuming I would just modify example 6 above to do just that. Better to ask than assume, you know. 🙂
Thanks so much Nile!! Your code worked beautifully.
A couple of other points for anyone else following along …
I had problems with bringing over the taxonomy where there were duplicate tags (eg nested in other non-duplicate parents) … I ended up manually changing them to be unique over at the drupal site, it was easier than messing with the code. Also, your code brings over the taxonomy all as post_tag, I changed that to category and it worked fine.
USERS – I made 3 changes
1. Bringing over ALL users, not just authors
2. I notice your code didn’t bring over the passwords. Apparently drupal and wordpress both use MP5, so it IS possible to copy over the passwords.
3. Your code sets the login name to be the email address – I wanted to use the login name they’d had initially, so I used REPLACE(u.name, ‘ ‘, ”) to remove the spaces
The code I ended up with is:
INSERT IGNORE INTO wordpress.wp_users
(ID, user_login, user_pass, user_nicename, user_email,
user_registered, user_activation_key, user_status, display_name)
SELECT DISTINCT
u.uid, REPLACE(u.name, ‘ ‘, ‘.’), u.pass, u.name, u.mail,
FROM_UNIXTIME(created), ”, 0, u.name
I then made sure there were no duplicate user names (not sure if Drupal allows them, just thought I’d be safe):
SELECT user_login, COUNT( user_login )
FROM wp_users
GROUP BY user_login
HAVING COUNT( user_login ) >1
Thanks again for the guide!!
Julie
Thank you for all your work on it. An excellent job. I would rather have individual sections like you did it which can be tested before moving on.
I have 5 years work on a Drupal 6 that I want to convert to WordPress. Your code worked well as stated.
Going forward I need to deal with custom post types
The custom post types went into wp_posts as did the regular ones. I did add my special ones in #5 and they came through
# Add more Drupal content types below if applicable.
WHERE n.type IN (‘post’, ‘page’, ‘blog’)
However, there are special fields that I think would be placed into postmeta. For example my site is a church site so under sermons content type i have items like a special image field for art related to the sermon, scripture, lectionary reference, etc Of course you have to define the custom post type to do this.
I need to investigate how to make that conversion. However, your work has gotten me most of the way there and I thank you for that. Blessings.
Thanks for this procedure.
A drawback is that the images won’t come in the WordPress media library.
Another solution is to use a migrator plugin like FG Drupal to WordPress that will move your Drupal data automatically to WordPress and will store the images in the WordPress media library.
https://wordpress.org/plugins/fg-drupal-to-wp/
Hi Fred!
Importing images into the media library are a lot easier than say 2012, when I first wrote this article. It is possible to with some of the import them from another folder into the uploads folder so it shows in the media library.
However, from experience, for extremely old websites, the website owners aren’t concerned about the older stuff.
P.S. – Next time leave a link in your Comment form to your website, instead of the plugin directory.
Thank you – the sql excerpts were fantastic.
Hi Nile,
It is a great information. I was looking for converting the drupal website to wordpress. Got to your website from one of the mentions in wordpress. Great info.
Thanks!