A basic intro on how to manually migrate field data from old content_type_ Drupal 5 CCK tables to Drupal 7

Zyxware default image3

When migrating from Drupal 5, the normal path would be to migrate everything (core/modules/themes) to the latest Drupal 5 versions, and then to the latest Drupal 6 versions, and then over to the Drupal 7 versions. How fortunate would have been the developers assigned the job if everything was as simple as that!

Unfortunately, everything is not as simple as that. There are modules that have fallen over time, those whose maintainers found it no longer as entertaining as before to keep releasing compatible versions for Drupal 7. There are custom modules for which there have been no history of updates. All these are just the tip of the iceberg when you set out to get your good Drupal 5 site upgraded.

You might not be able to get everything migrated over a simple update.php run. And when the site has quite some data to be brought over to the new land (Drupal 7) after the migration, CCK is one of your biggest concerns, being one of the most data-intensive modules in Drupal.

You should first try using the Content Migrate module. That should help you bring over most of the data. What you should set out next is to get a set of SQL queries to safely and reliably bring the data not brought over by Content Migrate. Drupal 7 uses two tables each to hold each field's data:field_data_field_fieldname and field_revision_field_fieldname. In Drupal 5, fields used by a single content type would have the data in the content_type_ table. For fields used by multiple content types, the data would be presented in content_field_ tables.

The surest way to go would be to dig into the code and find out how the module stores the data. But that could be quite time consuming when compared to the method mentioned below:
A quick and simple strategy to identify table mappings would be to try adding a new content of the type under which the field appears. Make sure you enter some easily searchable and recognizable value that might be unique. For eg., for string fields, a value like 'testing-data-mapping-0001', and for numeric fields, a value like 1234567, should be somewhat unique enough.

Now using something like phpMyAdmin, search the entire database for those values to identify where Drupal 7 stores those data. This shouldnt take much time (usually a matter of 1-5 minutes for a less-than-30GB DB). As simple as that!

Do the same thing on the Drupal 5 version of the site.

Now that you know where to put the data into, and from where to get the data, just write some SQL to bring the data over. Here's something to start with that:

INSERT INTO d7_table_name (
 column1,
 column2,
 column3,
 ...
)
SELECT
 d5table1.column00x as column1,
 d5table1.column00y as column2,
 d5table2.column001 as column3,
 ...
)
FROM d5table1
 LEFT JOIN d5table2 ON ...
 WHERE ...;

This should help you get going.

(Let us know if you still need help with this, or if you would like to get us to migrate your Drupal 5.x / 6.x site over to Drupal 7).

Happy migrating!