Importing data from another database on Drupal 7

08 May 2013

Here’s a quick tutorial on how to import data into a Drupal installation using a secondary database. I’ve done this by using an external PHP file located at the root of the Drupal installation and bootstrapping it. You can call it import.php.

All the magic happens by using db_set_active(‘secondary_database’), which connects Drupal to another database. Calling db_set_active() will switch you back to the default one. This can be configured in our settings.php file.

Let’s start with our settings file.

 1 $databases = array(
 2   'default' => array(
 3     'default' => array(
 4       'database' => 'drupal_db',
 5       'username' => 'root',
 6       'password' => 'root',
 7       'host' => 'localhost',
 8       'port' => '',
 9       'driver' => 'mysql',
10       'prefix' => '',
11     ),
12   ),
13   'secondary_database' => array(
14     'default' => array(
15       'database' => 'secondary_data',
16       'username' => 'root',
17       'password' => 'root',
18       'host' => 'localhost',
19       'port' => '',
20       'driver' => 'mysql',
21       'prefix' => '',
22     ),
23   ),
24 );

Let’s do some testing first with our import.php.

 1 <?php
 2 // we need to bootstrap Drupal first
 3 require_once 'includes/bootstrap.inc';
 4 define('DRUPAL_ROOT', getcwd());
 5 drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
 6 print "Importing Entries\n";
 7 // connect to a different DB defined on your settings file.
 8 db_set_active('secondary_database');
 9 // let's get all the entries we need for now.
10 $query = db_select('tablename', 's')->fields('s', array());
11 $results = $query->execute()->fetchAll();
12 print_r($results);
13 ?>

To run this, we can use:

1 php import.php

This should output an array of objects coming from your second database.

Importing the data

Now that we have tested our connection, we can finalize our script:

 1 <?php
 2 
 3 // we need to bootstrap Drupal first
 4 require_once 'includes/bootstrap.inc';
 5 define('DRUPAL_ROOT', getcwd());
 6 drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
 7 
 8 print "Importing Entries\n";
 9 // connect to a different DB defined on your settings file.
10 db_set_active('secondary_database');
11 // let's get all the entries we need for now.
12 $query = db_select('tablename', 's')->fields('s', array());
13 $results = $query->execute()->fetchAll();
14 
15 // back to the default database and building nodes
16 db_set_active();
17 
18 foreach ($results as $res) {
19     $node = new stdClass();
20     $node->title = $res->name;
21     $node->language = LANGUAGE_NONE;
22     $node->type = 'body';
23     $node->uid = 1;
24     $node->status = 1;
25     $node->body['und'][0]['format'] = 3;
26     $node->body['und'][0]['summary'] = $res->description;
27     $node->body['und'][0]['value'] = $res->description;
28     node_save($node);
29 }
30 ?>

And that’s it, now you should be able to import entries from other databases by bootstrapping Drupal and connecting to it temporarily. All the new content will be stored in a Drupal way.

Note: This is a cross post from my personal blog.

- Ivan Soto

Hybrid Global Inc.

Importing data from another database on Drupal 7

Importing the data