Importing legacy table to Drupal database

Question

Hi, I have legacy database and tables that I would like to try to import in Drupal. Here's an example table structure :

Table : Projects
 ProjectID
 ProjectName
 CountryID
 TypeID

ProjectID is primary key, CountryID and TypeID are foreign keys which point to Countries and Type tables , respectively.

I think I would make a Projects content-type first, reflect the fields present in the legacy tables using CCK.. my only problem is to import the data.. Is there anyway to automate this?

Thanks!

Answer 1

The Migrate module handles importing from tables. Migrate has hooks for doing more complex imports, but you should be able to get your data simple enough that you don't need those hooks (which aren't very well documented) by creating a new table from a join of your existing tables. Something like this (untested):

CREATE TABLE combined SELECT * FROM Projects p 
LEFT JOIN Country c ON c.CountryID = p.CountryID
LEFT JOIN Type t ON t.TypeID = p.TypeID

If you do decide you want to keep things more separated, with countries and types in separate content types, a coworker of mine wrote a pretty good tutorial on using migrate hooks.

Answer 2

If you can get the data into CSV/TSV format, Node Import should do the trick, and is geared towards site maintainers rather than developers.

Answer 3

Node import is fairly good if you just export the data as csv and import the foreign keys first. It works with complex fields like node references.

Otherwise you can write a basic module which goes through the database row by row and inserts the records into nodes. Some really basic pseudo code:

$node->title = $row['projectName'];
$node->type = 'project';
$node->country_field[0]['value'] = $row['country_name'];
if(save_node($node)) {
 set_message('Imported node');
}

Drupal has database switching so you can switch between the databases.