Hobsite Logo

You are here

Import Users to Drupal & Ubercart

You can manually migrate a 10 or 20 page website. The difficulty arises when you want to transfer numerous products and users into Drupal and Ubercart which involves data mapping. Developers have used different methods for the process of converting a website to Drupal from other systems like OsCommerce and CreLoaded.

We will create two CSV files populated with the user data. One file for the users table (Drupal) and the second file for the uc_addresses table (Ubercart).

I will share with you a PHP script which imports the user data from OsCommerce and puts it in a CSV file that can be exported to Drupal and Ubercart easily. I will not cover the file parsing and exporting, but the generated CSV file can be uploaded directly to MySQL via phpMyAdmin. You could also use the Feeds module with field mapping, but I have never needed to do this.

Despite there are modules built specifically for this purpose, a few years ago I came across a handy procedure for migrating products using a CLI/PHP script. Since then I have used the same script for moving products, and the credit goes to Drew for contributing this CLI script. I have also used the same principle for migrating users into Ubercart Addresses.

Below is the code that I have used to generate a CSV file for migrating thousands of users into Drupal in one go. The script will not generate a new password, after migration I just email all users and ask them to create a new password. Please note the script will not ensure each user has a unique username which is necessary but it is easy to implement.

#!/usr/bin/php

define ('CSV_OUTPUT', '/tmp/users.csv');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'osc_database');
define ('DB_USER', 'osc_database_username');
define ('DB_PASS', 'osc_database_password');
define ('LIMIT', 10000);

$query =

SELECT
c.customers_id,
c.customers_email_address,
c.customers_gender,
c.customers_firstname,
c.customers_lastname,
c.customers_password,
c.customers_telephone,
a.entry_company,
a.entry_street_address AS AddressL1,
a.entry_suburb,
a.entry_city,
a.entry_state,
a.entry_postcode,
co.countries_name
FROM
customers c
INNER JOIN address_book a ON c.customers_id = a.customers_id
INNER JOIN countries co ON a.entry_country_id = co.countries_id
GROUP BY
c.customers_id
ORDER BY
a.address_book_id ASC
EOQ;

$db = db_connect(DB_HOST, DB_NAME, DB_USER, DB_PASS);
$customers = db_query($query, $db);
$counter = 0;

// Construct the CSV file
$handle = fopen(CSV_OUTPUT, 'w');

$columns = array('uid', 'name', 'pass', 'mail', 'theme', 'signature', 'signature_format', 'created', 'access', 'login', 'status', 'timezone', 'language', 'picture', 'init', 'data');

fputcsv($handle, $columns);

while (($customer = db_object($customers)) && ($counter // print_r($customer);
$counter ++;

// create the username, get the part of the email address before @
$string_length = strlen($customer->customers_email_address); // not needed here but for reference, count no. of characters
$username = strtok($customer->customers_email_address,"@"); //isolates string before @
$username_lowercase = strtolower($username);

// ensure email address is lowercase
$email_lowercase = (strtolower($customer->customers_email_address));

// set user account created timestamp to migration time
date_default_timezone_set('United Kingdom/London');
$date = strtotime("now");

// get the user time zone. either London, New York or empty (the user can select her zone later)
if (($customer->countries_name) == 'United Kingdom') {
$zone = 'Europe/London';
}
else if (($customer->countries_name) == 'United States' && (($customer->entry_city) == 'New York')) {
$zone = 'America/New York';
}
else {
$zone = '';
}

$data_to_write = array(
$customer->customers_id, //preserves previous user id
$username_lowercase, //need to add function to ensure username is unique
'', // password leave empty or $customer->customers_password for old password or generate random password
$email_lowercase,
'', // theme
'', // signature
'filtered_html', // signature format
$date, // timestamp of when user account was created
'0', // timestamp user's last access
'0', // timestamp user's last login
'1', // user status - active = 1
$zone, // user's time zone - can be left empty
'', // user's language leave empty
'0', // user's picture - set to 0 for no picture
$email_lowercase,
'', // user sterelization field, leave empty
);

$data_to_write = array_map('trim', $data_to_write);

fputcsv($handle, $data_to_write);
}

fclose($handle);
echo "--------------- Created over $counter users\n --------------\n";
exit;

/** helper functions **/

function db_error($message) {
echo "db_error: $message\n" . mysql_error() . "\n";
}

function db_connect($db_host, $db_name, $db_user, $db_pass) {
$db = mysql_connect($db_host, $db_user, $db_pass) or db_error('Unable to connect to database');
mysql_select_db($db_name, $db);
return $db;
}

function db_query($query, $db) {
$result = mysql_query($query, $db) or db_error($sql);
return $result;
}

function db_object($result) {
return mysql_fetch_object($result);
}

This creates the CSV file with a table matching that of Drupal's users, and once the file is created you can just upload it directly to MySQL. Just make sure not to overwrite the Admin user row which is usually user id (uid) = 1 and the row with user id (uid) = 0 which Drupal needs it for anonymous users.

Now you will want to create another file for Ubercart Addresses. Here is the code:

#!/usr/bin/php

define ('CSV_OUTPUT', '/tmp/uc_addresses.csv');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'osc_database');
define ('DB_USER', 'osc_database_username');
define ('DB_PASS', 'osc_database_password');
define ('LIMIT', 10000);

$query =

SELECT
c.customers_id,
c.customers_email_address,
c.customers_gender,
c.customers_firstname,
c.customers_lastname,
c.customers_password,
c.customers_telephone,
a.entry_company,
a.entry_street_address AS AddressL1,
a.entry_suburb,
a.entry_city,
a.entry_state,
a.entry_postcode,
co.countries_name

FROM
customers c
INNER JOIN address_book a ON c.customers_id = a.customers_id
INNER JOIN countries co ON a.entry_country_id = co.countries_id
GROUP BY
c.customers_id
ORDER BY
a.address_book_id ASC
EOQ;

$db = db_connect(DB_HOST, DB_NAME, DB_USER, DB_PASS);
$customers = db_query($query, $db);
$counter = 0;

// Construct the CSV file
$handle = fopen(CSV_OUTPUT, 'w');

$columns = array('aid', 'uid', 'first_name', 'last_name', 'phone', 'company', 'street1', 'street2', 'city', 'zone', 'postal_code', 'country', 'address_name', 'default_shipping', 'default_billing', 'created', 'modified');

fputcsv($handle, $columns);

while (($customer = db_object($customers)) && ($counter // print_r($customer);
$counter ++;

// Sync country name to ISO code, add more to fit your need or leave empty
if (($customer->countries_name) == 'United Kingdom') {
$country = '826';
}
else if (($customer->countries_name) == 'United States') {
$country = '840';
}
else if (($customer->countries_name) == 'Canada') {
$country = '124';
} else {
'';
}

// print '

' .$country. '

';

$customers_firstname = ucfirst(strtolower($customer->customers_firstname));
$customers_lastname = ucfirst(strtolower($customer->customers_lastname));
$customers_fullname = $customers_firstname . ' ' .$customers_lastname;

date_default_timezone_set('United Kingdom/London');
$date = strtotime("now");
// $date = date('m/d/Y h:i:s a', time());

$data_to_write = array(
'', //$customer->customers_default_address_id,
$customer->customers_id,
$customers_firstname,
$customers_lastname,
$customer->customers_telephone,
$customer->entry_company,
$customer->entry_street_address,
'', // street 2
$customer->entry_city,
'', //entry_state (zone)
$customer->entry_postcode,
$country,
'Home', // you could leave empty for the user to add it
'1',
'1',
$date, //date created
'', //date modified
);

$data_to_write = array_map('trim', $data_to_write);

fputcsv($handle, $data_to_write);
}

fclose($handle);
echo "------------- Imported over $counter users\n and created the file ------------\n";
exit;

/** helper functions **/

function db_error($message) {
echo "db_error: $message\n" . mysql_error() . "\n";
}

function db_connect($db_host, $db_name, $db_user, $db_pass) {
$db = mysql_connect($db_host, $db_user, $db_pass) or db_error('Unable to connect to database');
mysql_select_db($db_name, $db);
return $db;
}

function db_query($query, $db) {
$result = mysql_query($query, $db) or db_error($sql);
return $result;
}

function db_object($result) {
return mysql_fetch_object($result);
}

This creates the CSV file with a table matching that of Ubercart Addresses (uc_addresses), and once the file is created you can just upload it through MySQL.

You could also use helpful PHP functions to clean the data by stripping white space, making all email addresses lowercase and making the first letter of all user names uppercase. You can do all these during migration, let's see some examples.

5 PHP Functions to Help You Migrate the Users

In the code above for generating the CSV file you have seen some functions for creating the username and for making the email address lowercase. Below I will discuss some of these functions and of course you can add your own.

1) Create Drupal Usernames during Migration

Drupal communicates with visitors by their usernames and OsCommerce does not keep a record of usernames. I prefer to extract the first part of the user’s email address and use it as a Drupal username after making it lowercase. I just think it will be more acceptable to the user. I use this function:

// $string_length = strlen($customer->customers_email_address); Uncomment to count the number of characters
$username = strtok($customer->customers_email_address,"@"); // This will isolate the string before @
$username_lowercase = strtolower($username);

One client asked that we create a nickname from the first and last name. If you come across this, here is a handy function:

$customers_firstname = ucfirst(strtolower($customer->customers_firstname));
$customers_lastname = ucfirst(strtolower($customer->customers_lastname));
$customers_fullname = $customers_firstname . ' ' .$customers_lastname;
$customers_username = $customers_firstname . $customers_lastname;

If you have a better alternative for coming up with a better username please share it in the comments section.

2) Convert the Date to Drupal Unix Timestamp

Drupal stores the date and time of when the account was created and when it was updated in Unix timestamp format. To my dismay I could not find any mention of account created date in the OsCommerce database. Please correct me if I'm wrong. Instead, I used the date of the first order made by the customer which is stored in a readable format. You can use this simple code to put the date in timestamp during the migration:

$date_created = $customer->date_ordered;
$date_created = str_replace('/', '-', $date_created);
$timestamp_created = strtotime($date_created);
3) Import the Addresses

If you use the Ubercart Addresses module you will find that moving the users addresses is not easy, especially because OsCommerce stores the countries in a two letter ISO code while Ubercart uses both a two digit and a three digit ISO code. For this, I ended up with a long script for the customer country names that were available in OsCommerce. Depending on your need you will require a function similar to this:

if (($customer->countries_name) == 'United Kingdom') {
$country = '826';
}
else if (($customer->countries_name) == 'Ireland') {
$country = '372';
}
else if (($customer->countries_name) == 'United States') {
$country = '840';
}
else if (($customer->countries_name) == 'Canada') {
$country = '124';
}
else if (($customer->countries_name) == 'Australia') {
$country = '36';
}
else if (($customer->countries_name) == 'Brazil') {
$country = '76';
}
else if (($customer->countries_name) == 'Netherlands') {
$country = '528';
}
else if (($customer->countries_name) == 'Greece') {
$country = '300';
}
else if (($customer->countries_name) == 'France') {
$country = '250';
}
else if (($customer->countries_name) == 'Portugal') {
$country = '620';
}
else if (($customer->countries_name) == 'South Africa') {
$country = '710';
}
else if (($customer->countries_name) == 'New Zealand') {
$country = '554';
}
else if (($customer->countries_name) == 'Indonesia') {
$country = '360';
}
else if (($customer->countries_name) == 'Spain') {
$country = '724';
}
else if (($customer->countries_name) == 'Switzerland') {
$country = '756';
}
else if (($customer->countries_name) == 'Sweden') {
$country = '752';
}
else if (($customer->countries_name) == 'Austria') {
$country = '40';
}
else {
'';

You can create more IFs to suit your needs by referring to the complete list of country ISO codes. I suggest you also visit the database and look at the tables uc_countries and uc_zones. Obviously this code is suitable if you have a few countries. If you have a larger number of countries I suggest you just import the country name as they are and later map the fields to uc_addresses with the Fields module. Probably there is a better solution, please share it the comments section.

4) Import the Zones

I don't usually care much about the zones and will leave the column empty, unless my client insists, then I would use a function similar to this:

if (($customer->countries_name) == 'United States' && (($customer->entry_city) == 'New York')) {
$zone = 'America/New York';

You can create more IFs, of course this is reasonable only if you have a few number of zones.

5) Name First Letter Capitalisation and Lowercase Email Address

We want the first letter of the user name to be capital and the rest of the letters to be lowercase. You also want all email addresses to be lowercase. Below are two functions for achieving this.

(a) Name capitalisation:

$customers_firstname = ucfirst(strtolower($customer->customers_firstname));
$customers_lastname = ucfirst(strtolower($customer->customers_lastname));

(b) Email address in lowercase:

$email_lowercase = (strtolower($customer->customers_email_address));

Now you should be able to migrate users and at the same time capitalize the first letter of the users' names, make the email addresses lowercase and do several other things during migration. I hope you find these scripts helpful in your next project, and don't forget to leave a comment.

Related post: Convert Website to Drupal, The Process

Blog Tags: 
Hobsite's Blog and News
Modern Web Design

Responding to the Current Trends in User Experience Web design is an art in itself, but among all the professional...

Skills of Writing Product Descriptions

For a powerful website that is fully optimized for sales and conversions, the skillful writing of product descriptions must...

Develop Writing Skills

How I Developed My Writing Skills - True Story Most people probably assume that writing is a natural gift that you either...

Write to Us

Hobsite, Warren Road, London E10 5QA
Telephone: +44 7931732696
Email: moc.etisboh@troppus