Import legacy data into Magento (Historic customer records)

Question

I would like to ask for some advice on the best way to import historic customer data including usernames & passwords into Magento.

The legacy data contains some 3 million records so we imagine it will take some time.

The question is, what is the best way to approach this as the import is going into a live website. Should we run it in batches using a cron script? Also the legacy data has a different type of encryption for passwords that are different from Native Magento.

There are currently two methods that I can see for doing this. The first one is whether to do this with direct sql query or using Magento API? With SQL I believe it could be quicker but we run the risk of crashing the site (yes it is live) I think the API route would be quicker and cleaner.

This is what I have so far:

Api.php

<?php

class Company_Customermigration_Model_Api extends Mage_Api_Model_Resource_Abstract
{
    public function customerCustomerCreate()
    {
        /**
         * Database query to find the customer record in the flat table.
         */

        $table = $this->getTableName('Company_customermigration/customer');

        $sql = $this->getConnection('core_read')->select()
            ->from($table)
            ->where($table.'.str_email = ?', $this->_email);

        try {
            $results = $this->getConnection('core_read')->fetchRow($sql);
        } catch (Exception $e) {
            Mage::logException($e);
            return false;
        }


        if ($results) {
            foreach ($results as $key => $value) {
                $this->customer->$key = $value;
            }

            $this->_dataMap();

            return true;
        } else {
            return false;
        }
    }
    /**
     * Map the new keys from the import to the old keys
     */
    public function create($customerData)
    {
        try {
            $customer = Mage::getModel('customer/customer')
                ->setData($customerData)
                ->save();
        } catch (Mage_Core_Exception $e) {
            $this->_fault('data_invalid', $e->getMessage());
        } catch (Exception $e) {
            $this->_fault('data_invalid', $e->getMessage());
        }
        $data = array(
            'email'         => $this->customer->str_email,
            'prefix'        => null,
            'first_name'    => $this->customer->str_firstname,
            'middle_name'   => null,
            'last_name'     => $this->customer->str_lastname,
            'date_of_birth' => $this->customer->birthday_client ? $this->customer->birthday_client : $this->customer->birthday,
            'gender'        => null,
            'password'      => $this->customer->md5_passwd,
            'group'         => $this->customer->vip ? 'VIP' : null,
        );

        foreach ($data as $key => $value) {
            $this->customer->$key = $value;
        }
        return $customer->getId();
    }

    public function info($customerId)
    {
        $customer = Mage::getModel('customer/customer')->load($customerId);
        if (!$customer->getId()) {
            $this->_fault('not_exists');
        }
        return $customer->toArray();
    }

    public function items($filters)
    {
        $collection = Mage::getModel('customer/customer')->getCollection()
            ->addAttributeToSelect('*');

        if (is_array($filters)) {
            try {
                foreach ($filters as $field => $value) {
                    $collection->addFieldToFilter($field, $value);
                }
            } catch (Mage_Core_Exception $e) {
                $this->_fault('filters_invalid', $e->getMessage());
            }
        }

        $result = array();
        foreach ($collection as $customer) {
            $result[] = $customer->toArray();
        }

        return $result;
    }

    public function update($customerId, $customerData)
    {
        $customer = Mage::getModel('customer/customer')->load($customerId);

        if (!$customer->getId()) {
            $this->_fault('not_exists');
        }

        $customer->addData($customerData)->save();

        return true;
    }

    public function delete($customerId)
    {
        $customer = Mage::getModel('customer/customer')->load($customerId);

        if (!$customer->getId()) {
            $this->_fault('not_exists');
        }

        try {
            $customer->delete();
        } catch (Mage_Core_Exception $e) {
            $this->_fault('not_deleted', $e->getMessage());
        }

        return true;
    }
}

I have the records stored in a Flat table on the same DB what I need to do is fetch the data using a Cron and create new users in Magento.

Any advice from anybody who has had to deal with this type of task will be welcomed.

Answer 1

I haven't had experience doing a migration of the scale you describe, but since no one else has suggested an answer, I thought I'd share some ideas.

I'd start by cloning the store so that I could thoroughly test my migration (on the clone) before trying it on the live store.

If I were simply importing customer accounts, I'd be inclined to try to do it via SQL first for performance reasons. The wider the scope of the data import, the more I'd be inclined to use Magento's API as it eliminates the need to reverse engineer how Magento stores data.

If you'd rather not run 3 million+ Magento API calls on your live site, you might be able to use a combination of both the Magento API and SQL approaches.

• On the live site, set the auto-increment start numbers on relevant tables to allow enough numbers for the millions of records you'll be importing--this way, new activity on the live site won't conflict with data generated during the import.
• Clone the store to a local/development system.
• Run your Magento API-powered migration script on the clone.
• Take a database diff from the clone and apply it to the live site--but first test this process by running it on another clone copy.
• On the live site, merge any duplicate customers that now exist in the system as a result of the import.