In this post, I will explain how to migrate a blog running on Word Press (Self Hosted) to BlogEngine. But before I start let me say, that Word Press simply rocks. The reason why I plan to switch my blog is customization. Since I am a dotnet geek, I really have no great idea of what I can make out of Word Press using PHP and when it comes to Blogging in .net, I guess I made a very right decision to use BlogEngine. It is open source and included all the necessary blogging utilities.
The main thing which I want to migrate is as follows
- Post
- Categories
- Tags
- Comments
The moment I start, I was thinking to get some export / import tool. Then I came to know about BlogML. A format that is created to interchange content between different bloging engines. Natively, Word Press don’t support BlogML but Robert McLaws did great job on wiring this tool. Unfortunately, that tool didn’t work for me, for some reason it is keep giving me error.
Finally, I tried it in my own way. Since that blog was self hosted, I have access to mysql database engine through phpMyAdmin. Hence, I decided to export SQL of my related tables and data in MSSQL (TSQL) format.
- After the login into phpMyAdmin, go to the table list by selecting the databases comes at left.
- From the tab at the top select export.
- In the export group, select SQL and tables in my case it is wp_comments, wp_posts, wp_term_relationships, wp_term_taxonomy, and wp_term
- Now from the SQL Compatibility Mode, Select MSSQL. Save the file and your are complete.
Your selection screen should like like below.
Now open the generated SQL in MSSQL and before you run you might need to fix some column names and some data type issues of the table. But believe that is pretty easy. To help you more, please see the table creation script below
CREATE TABLE [dbo].[wp_comments](
[comment_ID] [bigint] IDENTITY(1,1) NOT NULL,
[comment_post_ID] [int] NOT NULL,
[comment_author] [varchar](200) NOT NULL,
[comment_author_email] [varchar](100) NOT NULL,
[comment_author_url] [varchar](200) NOT NULL,
[comment_author_IP] [varchar](100) NOT NULL,
[comment_date] [datetime] NOT NULL,
[comment_date_gmt] [datetime] NOT NULL,
[comment_content] [text] NOT NULL,
[comment_karma] [int] NOT NULL,
[comment_approved] [varchar](20) NOT NULL,
[comment_agent] [varchar](255) NOT NULL,
[comment_type] [varchar](20) NOT NULL,
[comment_parent] [bigint] NOT NULL,
[user_id] [bigint] NOT NULL,
[comment_subscribe] [varchar](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[comment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[wp_posts](
[ID] [bigint] NOT NULL,
[post_author] [bigint] NOT NULL,
[post_date] [datetime] NOT NULL,
[post_date_gmt] [datetime] NOT NULL,
[post_content] [text] NOT NULL,
[post_title] [text] NOT NULL,
[post_category] [int] NOT NULL,
[post_excerpt] [text] NOT NULL,
[post_status] [varchar](20) NOT NULL,
[comment_status] [varchar](20) NOT NULL,
[ping_status] [varchar](20) NOT NULL,
[post_password] [varchar](20) NOT NULL,
[post_name] [varchar](200) NOT NULL,
[to_ping] [text] NOT NULL,
[pinged] [text] NOT NULL,
[post_modified] [datetime] NOT NULL,
[post_modified_gmt] [datetime] NOT NULL,
[post_content_filtered] [text] NOT NULL,
[post_parent] [bigint] NOT NULL,
[guid] [varchar](255) NOT NULL,
[menu_order] [int] NOT NULL,
[post_type] [varchar](20) NOT NULL,
[post_mime_type] [varchar](100) NOT NULL,
[comment_count] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[wp_terms](
[term_id] [bigint] IDENTITY(1,1) NOT NULL,
[name] [varchar](200) NOT NULL,
[slug] [varchar](200) NOT NULL,
[term_group] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[term_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[wp_term_relationships](
[object_id] [bigint] NOT NULL,
[term_taxonomy_id] [bigint] NOT NULL,
[term_order] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[object_id] ASC,
[term_taxonomy_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[wp_term_taxonomy](
[term_taxonomy_id] [bigint] IDENTITY(1,1) NOT NULL,
[term_id] [bigint] NOT NULL,
[taxonomy] [varchar](32) NOT NULL,
[description] [text] NOT NULL,
[parent] [bigint] NOT NULL,
[count] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[term_taxonomy_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Please note that it is just the schema script. All you data will be included in mysql generated SQL File which we have created before.
Now, we have all the required tables with data imported from Word Press to Blog Engine Db but we will fill Blog engine tables to show imported data. Lets first start with category.
Category:
There is no such table in wp_categories in word press instead it uses wp_term and wp_term_taxonomy to store categories where as in Blog engine we have a table called be_categories which hold categories information. So following query will dump the data from wp_terms , wp_taxonomy to be_categories.
INSERT INTO [dbo].[be_Categories]
([CategoryID]
,[CategoryName]
,[Description]
,[ParentID]
,[Slug])
SELECT
NEWID(),
w1.[name],
('Posts in ' + w1.[name]) as Description,
NULL, -- as I am not going to make any parent child relation now ...
w1.[slug]
FROM [BlogEngine].[dbo].[wp_terms] w1
INNER JOIN wp_term_taxonomy w2 on w1.term_id = w2.term_id and w2.taxonomy = 'category'
GO
Posts:
Now lets deal with posts, a very easy query because we have the post table in both the blogging engines. In Word Press it is wp_posts where as in Blog Engine it is be_post
INSERT INTO [dbo].[be_Posts]
([PostID]
,[Title]
,[Description]
,[PostContent]
,[DateCreated]
,[DateModified]
,[Author]
,[IsPublished]
,[IsCommentEnabled]
,[Raters]
,[Rating]
,[Slug])
SELECT newid(),
post_title,
post_excerpt,
post_content,
post_date,
post_modified,
'username',
1,
1,
0,
0,
post_name
from wp_posts where post_type ='post'
Post Category Relation:
Now its time to set, which post have which categories. The table which is repsonsible for saving this information is called wp_term_relationship in Word Press and be_PostCategory in Blog Engine. See the following query.
select wp.post_title ,wtr.name into #temp1 from wp_term_relationships wr inner join
wp_term_taxonomy wt on wr.term_taxonomy_id = wt.term_taxonomy_id and wt.taxonomy = 'category'
inner join wp_terms wtr on wt.term_id = wtr.term_id
inner join wp_posts wp on wr.object_id = wp.ID and post_type = 'post'
INSERT INTO [dbo].[be_PostCategory]
([PostID]
,[CategoryID])
select (select postId from be_Posts where Title= convert(nvarchar(max),t.post_title)),
(select CategoryID from be_Categories where CategoryName = t.name) from #temp1 t
drop table #temp1
GO
I guess this query might need some explanation. See, in the top query I am getting the title of posts and name of categories and storing it to temp table.
Now come to the second part, here I insert new reords in be_postcategory based on the category names and post titles we filled before.
Tag:
In Blog Engine we have a table called be_PostTag which manage all the tags related stuff but in wordpress again involve all the tables containing wp_term. So, I write the following query which get the data from those tables and store it in Tags.
INSERT INTO [dbo].[be_PostTag]
([PostID]
,[Tag])
SELECT
(select postId from be_Posts where Title= convert(nvarchar(max),wp.post_title)) ,SUBSTRING(w1.[name], 1, 50)
FROM [BlogEngine].[dbo].[wp_terms] w1
INNER JOIN wp_term_taxonomy w2 on w1.term_id = w2.term_id and w2.taxonomy = 'post_tag'
inner join wp_term_relationships wr on wr.term_taxonomy_id = w2.term_taxonomy_id
inner join wp_posts wp on wr.object_id = wp.ID
Comment:
This one is comparatively easy. We have table called wp_comments in Word Press and be_postcomment in Blog Engine to manage the comments. Here is the final query.
INSERT INTO be_PostComment]
([PostCommentID]
,[PostID]
,[ParentCommentID]
,[CommentDate]
,[Author]
,[Email]
,[Website]
,[Comment]
,[Country]
,[Ip]
,[IsApproved])
SELECT
newId()
,(select postId from be_Posts where Title= convert(nvarchar(max),wpp.post_title)) as PostID
,(select postId from be_Posts where Title= convert(nvarchar(max),wpp.post_title)) as PostID
,[comment_date_gmt]
,[comment_author]
,[comment_author_email]
,[comment_author_url]
,[comment_content]
,NULL
,[comment_author_IP]
,1
FROM [wp_comments] wpc
INNER JOIN BlogEngine.dbo.wp_posts wpp on wpc.comment_post_ID = wpp.ID
where wpc.comment_approved = '1'
Note : You might see some /r/n between some post and comments. Don’t get afraid of this, just replace “/r/n” with “<br/>” on effected using Replace function of TSQL.
That’s how you can import all the data from Word Press to Blog Engine and this is fairly a huge issue why people don’t move their blogs. I have tried to explain the method by making it more simple, if you still face any issue please feel free to contact me.
