Migrating legacy data to WordPress

With an increasing number of users migrating to WordPress, moving data from an existing CMS to WordPress has become an essential process in the transition. Although migrating small databases from another CMS can be done in a few hours, large databases can take days or even weeks.

In one of my recent endeavors of the same, I had to migrate a relatively small user database from an old CMS to WordPress. Although a small task, some points tripped me and took a little longer than planned. Below are some of the points and recommended tools to consider when migrating existing databases to WordPress.

Preparing data for WordPress

Whenever importing data into a WordPress database (or any other CMS for that matter), a couple of important points needs to be considered.

a. The datatype of the columns: Does the data types of the old schema match with WordPress, or is any necessary conversion required.

b. Column lengths: Will the old data fit in the new WordPress table columns without any truncation thus losing precious data.

In regard to the first point, one of the common mismatch that can occur is with temporal datatypes. The legacy temporal data may be in a TIMESTAMP (or varchar) format, while the new table needs the data in MySQL DATETIME format. In this case you will need to programmatically change the TIMESTAMP format to DATETIME. One way to do this in PHP is with the DateTime object or by using the date function.

date("Y-m-d H:i:s", $unixTimestamp);

Note that MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for DATETIME.) See here.

You can convert between timezones using the MySQL CONVERT_TZ function.

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','US/Central');

For example to convert the current time from the current timezone to a different one.

SELECT CONVERT_TZ(NOW(), @@session.time_zone, 'US/Central');

Note that you need to make sure that the MySQL zone data tables are loaded in the system tables else the above will return a NULL. Check this link here on how to load zone tables in MySQL.

The second point has to do with data loss due to field length restrictions. This is usually easy to rectify by setting the appropriate length in the new tables.

Filtering data

Filtering existing data before importing into WordPress is essential as some table columns have restrictions on the characters accepted. For example WordPress password may only contain the following characters:

  • Any lowercase/uppercase alphanumeric characters
  • These symbols: . ? / ‘ ” ; : | ] } [ { = + – _ ) ( * & ^ % $ # @ ! ~
  • Whitespace

CSV conversion

In most of the cases it will be easier to first convert any legacy data to a CSV format and later import it into WordPress. My favorite tool in this regard is HeidiSQL. It has a nice CSV import tool which also allows you to map each CSV field with a particular database table column.

Another tool is CSVFix, a command-line tool to manipulate CSV data. This is a comprehensive tool which I’ll probably cover in another post. Just to give you a gist what it can do, take the example below.

Let us say we have the following data in our CSV file named ‘names.csv’ and we need to add a sequence number to each row.

Charles,Dickens,M
Jane,Austen,F
Herman,Melville,M
Flann,O'Brien,M
George,Elliot,F
Virginia,Woolf,F
Oscar,Wilde,M

We can easily do that using CSVFix with the following command.

D:\test>csvfix sequence -n 100 -p 3 names.csv
 
"100","Charles","Dickens","M"
"101","Jane","Austen","F"
"102","Herman","Melville","M"
"103","Flann","O'Brien","M"
"104","George","Elliot","F"
"105","Virginia","Woolf","F"
"106","Oscar","Wilde","M"

The following for example orders the fields in the CSV file.

D:\test>csvfix order -f 3,1,2 names.csv
 
"M","Charles","Dickens"
"F","Jane","Austen"
"M","Herman","Melville"
"M","Flann","O'Brien"
"F","George","Elliot"
"F","Virginia","Woolf"
"M","Oscar","Wilde"

Using WordPress import plugins to migrate data

If manual data migration frightens you, than there are some really good plugins that can make it easier to import existing content to WordPress. ‘Wp All Import‘ is one such tool that enables you to import CSV or XML data to your WordPress database. WpAllImport also allows you to filter XML data using XPath. Other tool such as ‘WP Ultimate CSV Importer‘, specialized to import only CSV data, can also help you during your migration process.



2 thoughts on “Migrating legacy data to WordPress

Comments are closed.