Page 1 of 1

Nucleus to Wordpress Content Transfer

#1 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,582
  • Joined: 12-June 08

Posted 08 November 2012 - 04:31 PM

Topics covered:
mysql table creation
mysql insert statements
mysql select statements
concat functions

Tools required:
Nucleus CMS v3.64
Wordpress Version 3.4.2

I am in the process of moving an old blog from Nucleus CMS to Wordpress and was having a difficult time trying to find an easy way to dump four years of blog posts from the former and importing them into to later. There was not a solid plugin or walk through to take care of the process so I forged ahead and just did a bit of data conversion.

I did a quick scout out and noticed many other people having a rough time at this - mostly they were looking for an easy 'press this button and go' sort of solution and were very uncertain about mucking around in the tables behind the scenes. This will help guide you through exporting data and importing it into the right tables with minimal fuss. I also only imported the three crucial tables to get you up and running. There are some mappings and data you may want to move, but for me this was all I needed.

The gist is copy the nucleus table and data from your existing blog, re-hydrating that table in your wordpress database and just doing a select/insert across to the wordpress tables. For some this is easy hat, but for others it is a pretty daunting move.

So fore-go the plugins and let's go.

A mention up front - this works best with a fresh and empty install of wordpress (no comments, no pages - nada, zip, zero). Having a new install means you can keep the blog post ids and not worry about renumbering them. If you are trying to fold data into an existing post numbering scheme you may run into duplicates and will want to just renumber the id columns from the last wordpress post on. That gets complicated because you will then need to renumber any comments on your posts, etc. Best to start fresh before you add too many things into wordpress.

First create a folder on your desktop to hold all your scripts.

Next log into your mysql 'phpmyadmin' on the Nucleus account. You should have a databse called '_ncls1' - this holds your entire CMS. Click on the database and open it up to see the tables.

Attached Image

Of all the tables there the most important are:
nucleus_item
nucleus_category
nucleus_comment

Attached Image

These will correspond with wordpress's:
wp_posts
wp_terms
wp_comments

Let's get to exporting!

1. Click on nucleus_category.
You will see a series of rows that should be your nucleus categories.
2. Click on the 'export' button at the top.

Attached Image

3. It will present you with the 'quick' method. Keep all the options as is and click 'go'.

Attached Image

4. Save the file to a folder on your desktop's folder.
5. You should have a file called "nucleus_category.sql".

Repeat this process for nucleus_comment and nucleus_item.

Pretty painless so far, right?

Now log into the phpmyadmin for your wordpress site. I happen to be transferring my posts from a public hosted site to my localhost test site.

Attached Image

You will see a database called 'wordpress'. Click on that.
Here is all the tables that make up your wordpress back end. Pretty impressive.

The plan is to recreate your nucleus tables and have them filled with data from your scripts, and bulk import that into your word press tables.

From your word press tables view - click 'import'.

Attached Image

Click 'browse' and select the 'nucleus_category.sql'.
Keep all the options as is and click 'go'.

Attached Image

You should see a green message indicating 'Import has been successfully finished' and your 'nucleus_category' table appear in the list.

Attached Image

Click 'browse' and repeat this process for the other two tables.

You should three nucleus tables at the top of your wordpress tables. Feel free to click on the tables and browse the data to make sure it is all in the right columns.

Attached Image

Here's the critical part of the tutorial - importing the data from one table to another.

Let's start easy with moving the 'nucleus_category' data into the 'wp_terms' table. The match up here is simple:

Quote

nucleus_category.catid -> wp_terms.term_id
nucleus_category.cname -> wp_terms.name
nucleus_category.cdesc -> wp_terms.slug


nucleus_category.cblog is not needed.

At the top of the page click the 'sql' tab.
Copy the insert statement into the query window.
Click 'go'.

The insert statement:
INSERT INTO wp_terms(term_id, name, slug)
SELECT catid, cname, cdesc 
FROM nucleus_category


Click on the 'wp_terms' and verify your data is there. Score!

We are going to repeat that same process for the items and the comments.

For the comments here's how the fields line up:

Quote

nucleus_comment.cnumber -> wp_comments.comment_ID
nucleus_comment.cbody -> wp_comments.comment_content
nucleus_comment.cuser -> wp_comments.comment_author
nucleus_comment.cemail -> wp_comments.comment_author_email
nucleus_comment.citem -> wp_comments.comment_post_ID
nucleus_comment.ctime -> wp_comments.comment_date
nucleus_comment.cip -> wp_comments.comment_author_IP


Not used:
cmail
cmember
chost
cblog

The insert statement:
INSERT INTO wp_comments (comment_ID, comment_content, comment_author, comment_author_email, comment_post_ID, comment_date, comment_author_IP)
SELECT cnumber, cbody, cuser, cemail, citem, ctime, cip	
FROM nucleus_comment


The tricky one is going to be the posts. First - for some reason wordpress uses character strings where I would have used small ints and bits. For example the 'post_status' column. Instead of 1 = publish, 2 = draft, 3 = inherit they used the whole words. A bit less efficint to join off of, but it happens.

The second reason is nucleus has a defined "ibody" and "imore" if you want to have a 'continue reading' link. Wordpress doesn't so we need to combine those two columns.

The third - I am going to import all of the comments under my account - user 1. That's why the author will be '1' in the query.

The important columns to line up:

Quote

nucleus_item.inumber -> wp_posts.ID
1 -> wp_posts.post_author
nucleus_item.itime -> wp_posts.post_date
nucleus_item.ibody, nucleus_item.imore -> wp_posts.post_content
nucleus_item.ititle -> wp_posts.post_title
nucleus_item.idraft -> wp_posts.post_status


Not used: everything else.

The insert statement:
INSERT INTO wp_posts
(ID 	,post_author 	,post_date 	,post_content ,post_title ,post_status)
SELECT `inumber`,1,`itime`,concat(`ibody`, `imore`),`ititle`,CASE when idraft = 1 THEN 'draft' 	else 'publish' end 
FROM nucleus_item


The final bit of business is to set up the relationship between your posts and your terms.

Run this query in the SQL window and you will insert the relationship between the term id and your post for wordpress.

insert into `wp_term_relationships` (`object_id`, `term_taxonomy_id`)
SELECT `inumber`,`icat` 
FROM `nucleus_item`


That should wrap things up. You can view your wordpress site and see it filled with posts, comments, and categories. You should drop the nucleus tables by click on the table name and then clicking on 'drop. Be very certain you are not dropping a wordpress table. If you do that you'll need to recreate the table through a new install or by hand.

Is This A Good Question/Topic? 2
  • +

Page 1 of 1