CiviCRM Community Forums (archive)

For our new civicrm implementation, we will import existing data from the ms-sql database of our old system. For this, we use the civicrm_migrate Drupal module. I experienced some troubles with importing addresses, so I just wanted to share parts of our code, maybe it is helpful for someone else.

I based our migration on the example code of the migrate module. I suggest you reading the code in beer.inc first, to get started.

I will now show you the relevant parts of our migration code:

class KipContactMigration extends DynamicMigration {

  /**
   * Constructor.
   */
  public function __construct() {
    parent::__construct();
So we inherit from DynamicMigration. In the constructor we define a map for tracking the relationship:

    $this->map = new MigrateSQLMap($this->machineName,
        array(
          'AdNr' => array(
            'type' => 'varchar',
            'length' => 255,
            'not null' => TRUE,
            'description' => 'primary key in source database',
          ),
        ),
        MigrateDestinationCiviCRMContact::getKeySchema()
      );
The relevant part is the MigrateDestinationCiviCRMContact::getKeySchema().

I used the dblib database driver so I could use the Drupal-database-API for generating the source query. This way, we could use an ordinary MigrateSourceSQL query as source. This is not necessary, you can use a MigrateSourceMSSQL source as well. But if you do use dblib, like us, make sure you set map_joinable to false,otherwise migrate will try to join your source table with the mapping table in the destination data base, which will obviously not work.

   /***create your $query here ***/

   $this->source = new MigrateSourceSQL($query, array(), NULL,
      array('map_joinable' => FALSE));

    // Set up our destination.
    $this->destination = new MigrateDestinationCiviCRMContact();
Notice the destination, which is a MigrateDestinationCiviCRMContact.

Now you have to define mappings. Most will be obvious; here are some examples:

    $this->addFieldMapping('external_identifier', 'AdNr');
    $this->addFieldMapping('last_name', 'Naam');
    $this->addFieldMapping('first_name', 'Voornaam');
    $this->addFieldMapping('birth_date', 'GeboorteDatum');
    $this->addFieldMapping('contact_type')->defaultValue("Individual");
But if you want to import addresses as well, you have to be careful:
    $this->addFieldMapping('city__1', 'Gemeente');
    $this->addFieldMapping('postal_code__1', 'PostNr');
    $this->addFieldMapping('street_address__1','Straat');
    $this->addFieldMapping('state_province__1')->defaultValue("");

    /*** more mappings ***/
 }

There are two tricky parts:

• Without the __1-prefix, it won't work. This __1 tells the migration that the location type of the address is the Home address, which is location type 1. Of course you can also specify another location type, but you do have to specify one.
• Your address should contain all four fields: city, postal_code, street_address and state_province. If one is missing, it won't work. That's why I explicitly use an empty string for the province. (I will eventually derive the province from the postal code, which isn't hard in Belgium. I might do this with a hook, but I'm not sure yet. For the moment, it is just empty.)

I had to do some preparation as well:

  /**
   * Convert gender-ID
   */
  public function prepare($contact, stdClass $row) {
    $contact->gender_id = 3 - $row->Geslacht;
  }

  /**
   * Prepare row
   */
  public function prepareRow($row) {
    foreach ($row as $key => $value) {
      if (!is_numeric($value)) {
        // Convert to utf8. There are probably better ways to do this
        $row->$key = utf8_encode($value);
      }
    }

    $row->Straat = $row->Straat.' '.$row->Nr;
  }

In our source database, the gender-ID's are the other way round, so I change them in prepare.I also have to concatenate street and number from our source database, which I wanted to do in prepare as well. But this does not work, because civicrm_migrate does something with the adresses before prepare is called. That is why I moved this manipulation to prepareRow.

As you can see, I also do a dirty trick in prepareRow to convert the source data to UTF-8 encoding. As the comment is saying, there are probably better ways to achieve this.

But hey, this works for me. I hope it will work for you as well. 

Thanks for sharing this.

I have always used addresses as the destination when importing addresses so I'm not familiar with the reasons you chose to use contact or the tricks you used to make it work.

This is just because it is only the first time I actually use drupal migrate. If you retrieve a contact using the civicrm API, it returns address information, and so I thought I could use these address fileds for migration as well. This seemed to be easier than importing to address, because I don't know yet how to find out to which destination contact a source address has to be assigned to. This is probably somewhere in the docs, but I am rather new to this.

Anyway, I looked in the code of civicrm_migrate, and that way it was not very hard to find out that I had to append __1.

I'm not sure why civimigrate would do something to the address data before prepareRow is called.

The handling of the addresses of an imported contact happens in a prepare function of the contact migration of civicrm_migrate. I guess this prepare function is called after my prepare function.

Oh right - I misread  - I thought you were using civimigrate

http://civicrm.org/blogs/eileen/another-migration-story-using-civimigrate-2-drupal-6-or-7

Interesting; I was not aware of that one. I wonder what the differences are.