a blog on technology, music and geek culture from room34

Main menu

Post navigation

How I migrated a client’s blog from Drupal to WordPress 3.0

I just finished migrating a client’s blog from Drupal to WordPress 3.0. When I took on the project, I assumed there was a straightforward way to migrate content from Drupal to WordPress, but I was mistaken. Fortunately, there is a way, but it involves directly mucking about with the Drupal and WordPress databases. Never fear! There are some tools to help.

After a bunch of research, I found the most comprehensive and up-to-date solution on Mike Smullin’s blog. It wasn’t that up-to-date, though, having been written over a year ago and targeted at WordPress 2.7. Some helpful comments on that post offered a few refinements, and I added a few of my own to accommodate the particular quirks of this client’s site.

Since Mike Smullin (and D’Arcy Norman and Dave Dash before him) was so kind to share his work, I thought I should do the same. And so, I present my further refined SQL script for migrating Drupal data to a WordPress 3.0 site.

# If you're not bringing over multiple Drupal authors, comment out these lines and the other
# author-related queries near the bottom of the script.
# This assumes you're keeping the default admin user (user_id = 1) created during installation.
DELETE FROM wordpress.wp_users WHERE ID > 1;
DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;

# Set all pages to "pending".
# If you're keeping the same page structure from Drupal, comment out this query
# and the new page INSERT at the end of this script.
UPDATE wordpress.wp_posts SET post_status = 'pending' WHERE post_type = 'page';

# Set category names to title case (in case term already exists [as a tag] in lowercase).
UPDATE wordpress.wp_terms SET name = 'First Category' WHERE name = 'first category';
UPDATE wordpress.wp_terms SET name = 'Second Category' WHERE name = 'second category';
UPDATE wordpress.wp_terms SET name = 'Third Category' WHERE name = 'third category';

# VIDEO – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# If your Drupal site uses the content_field_video table to store links to YouTube videos,
# this query will insert the video URLs at the end of all relevant posts.
# WordPress will automatically convert the video URLs to YouTube embed code.
UPDATE IGNORE wordpress.wp_posts p, drupal.content_field_video v
SET p.post_content = CONCAT_WS('\n',post_content,v.field_video_embed)
WHERE p.ID = v.nid
;

# IMAGES – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# If your Drupal site uses the content_field_image table to store images associated with posts,
# but not actually referenced in the content of the posts themselves, this query
# will insert the images at the top of the post.
# HTML/CSS NOTE: The code applies a "drupal_image" class to the image and places it inside a <div>
# with the "drupal_image_wrapper" class. Add CSS to your WordPress theme as appropriate to
# handle styling of these elements. The <img> tag as written assumes you'll be copying the
# Drupal "files" directory into the root level of WordPress, NOT placing it inside the
# "wp-content/uploads" directory. It also relies on a properly formatted <base href="" /> tag.
# Make changes as necessary before running this script!
UPDATE IGNORE wordpress.wp_posts p, drupal.content_field_image i, drupal.files f
SET p.post_content =
CONCAT(
CONCAT(
'<div class="drupal_image_wrapper"><img src="files/',
f.filename,
'" class="drupal_image" /></div>'
),
p.post_content
)
WHERE p.ID = i.nid
AND i.field_image_fid = f.fid
AND (
f.filename LIKE '%.jpg'
OR f.filename LIKE '%.jpeg'
OR f.filename LIKE '%.png'
OR f.filename LIKE '%.gif'
)
;

# Fix post_name to remove paths.
# If applicable; Drupal allows paths (i.e. slashes) in the dst field, but this breaks
# WordPress URLs. If you have mod_rewrite turned on, stripping out the portion before
# the final slash will allow old site links to work properly, even if the path before
# the slash is different!
UPDATE wordpress.wp_posts
SET post_name =
REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1))
;

# Miscellaneous clean-up.
# There may be some extraneous blank spaces in your Drupal posts; use these queries
# or other similar ones to strip out the undesirable tags.
UPDATE wordpress.wp_posts
SET post_content = REPLACE(post_content,'<p> </p>','')
;
UPDATE wordpress.wp_posts
SET post_content = REPLACE(post_content,'<p class="italic"> </p>','')
;

# NEW PAGES – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# MUST COME LAST IN THE SCRIPT AFTER ALL OTHER QUERIES!
# If your site will contain new pages, you can set up the basic structure for them here.
# Once the import is complete, go into the WordPress admin and copy content from the Drupal
# pages (which are set to "pending" in a query above) into the appropriate new pages.
INSERT INTO wordpress.wp_posts
(`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`,
`post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`,
`post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`,
`post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`,
`post_mime_type`, `comment_count`)
VALUES
(1, NOW(), NOW(), 'Page content goes here, or leave this value empty.', 'Page Title',
'', 'publish', 'closed', 'closed', '',
'slug-goes-here', '', '', NOW(), NOW(),
'', 0, 'http://full.url.to.page.goes.here', 1, 'page', '', 0)
;

A few notes and suggestions:

Use at your own risk. I’m just sharing the code I used. In fact, it’s not even exactly the code I used, since I had to generalize a few things for mass consumption. I haven’t even tested this modified version. I make no guarantees as to its reliability or applicability to your particular implementation.

Download the script below; don’t just copy-paste the code from the block above. I had to apply some HTML formatting to make it look right, and this might result in bad characters that would break the script if run as-is above.

Always work with a test copy of your database before altering live site content. In theory this script can be run over and over again cleanly, but it will erase whatever data was already in the WordPress database, and there will be no way to get it back unless you have a backup.

Read through the code carefully, especially all comments before running the script. Most likely you will need to make some changes to the code.

Good luck! If you find bugs or develop further improvements, please comment below!