Things to Look Out For When Migrating Data Between CMS's

While redeveloping the NAMAC site, I had some difficulty importing data from our ColdFusion CSM to a new Drupal CMS. There were many odd glitches in migrating the data. DON"T UNDERESTIMATE THE TROUBLE OF THIS.

You will need to budget a sizeable amount of time to make this happen.

I was given an XLS file which needed to be translated into a CSV file. I was also on a Mac and had to make sure the CSV was saved in a UTF-8 format for importing.

I used Drupal's Node Import module for this and had to break up the data across spreadsheets into a CSV with columns that Drupal could understand. My question that I came too was:

"How do I merge a multi-sheet ColdFusion database XLS file into a one sheet Drupal CSV database file?"

I posted it on LinkedIn and this was one of the answers I got courtesy of Steve Boynes, Senior Programmer/Analyst at Dolby Laboratories:

"Wow an XLS data source! Current norms for Coldfusion development are to use a SQL based data source so it sounds like you might be working with a legacy site. In any case, the task at hand is to map the data from your Coldfusion based web site into your new Drupal CMS based web site.

With an XLS datasource in Coldfusion each sheet is the equivalent of a Table in SQL. The first row in each sheet defines the Column names of the "table", and the subsequent rows represent the Row data.

You don't actually need to merge the multiple sheets of the XLS into a single CSV file, instead you need to create a individual CSV file for each sheet. Unfortunately this isn't as easy as going into Excel and doing a File:Save As and setting the type to CSV because of the formatting requirements of the Drupal PHP based CSV parser. So you'll want to write some Coldfusion code to create the CSV file for you. The elegant solution would be to have a single Coldfusion page that would query all of your "tables" and write out the individual files to the filesystem. But the quick and easy solution is to just query each table and output the fields as comma separated values directly to the screen, then just copy and paste this into Notepad and save it with a .CSV extension. Just make sure you follow the appropriate convention for the CSV: first row containing the column names, double quotes around strings with commas, and date values in valid UNIX timestamp format.

On the Drupal side you'll want to use the node_import module to get the data in to your CMS. You can use the wizard to map in the values from your CSV files. Attached is a link to a pretty good readme on this process."

Links:
http://dir.rabhiomar.com/node/129

There is also an excellent article written on data exchange formats by Peter Campbell, IT Director at Earth Justice. You can read it on the Idelaware Site here: http://www.idealware.org/articles/data_exchange_alpha_soup.php

Hope someone finds this helpful!

_morgan