Exporting users/customers from X-Cart to Magento

title images import2 Exporting users/customers from X Cart to Magento

Following my previous posting of importing basic product data into Magento from X-Cart I stumbled across this code on the Magento Community forums with a nifty PHP script to prepare a CSV file of users to be imported into Magento.

:!: UPDATE: Finally got around to trying to utilize this script, and realized there are in fact some issues with using it. Some of the main items I noticed is:

  • It is not 3.5.x X-Cart compatible, and possibly lower versions.
  • As well as it was not very optimized for a very large number of customers, causing memory limit errors while attempting to export.

I have updated the code to handle the two items mentioned above and have successfully imported almost 6k customers from a 3.5.x version of X-Cart. Here is the updated script, the same instructions apply, copy the file into your admin directory of X-Cart and then proceed to login to the admin of X-Cart, after successfully logging in, simply change index.php in your url to migrate.php and wait for the script to prompt for you to download a .csv file of the users. Be patient especially if you have a large number of customers as the script can take awhile to generate this data without any prompts that it is working.

bulb Exporting users/customers from X Cart to Magento When using this code be sure and note the following comments as, depending on your X-Cart version. Anonymous customers are ignored.

Create the file $xcart_dir/admin/migrate.php and copy the following code into it:

<?php
/*
Migrate X-Cart customers to Magento
Original by Spydor at: http://www.magentocommerce.com/boards/viewthread/30894/
Modified by B00MER at: http://www.molotovbliss.com 3/15/2009 3:49:23 AM
Modifications include:
X-Cart 3.5 compatibility, possibly lower versions as well.
Optimization for large number of records to avoid memory limit errors
*/
@set_time_limit(2700);
# Include core functions
if (!require("../admin/auth.php"))
	require("./auth.php");
function resolveState($b_state,$b_country,$s_state,$s_country){
	global $states;
	$result = array();
	foreach($states as $key=>$value){
		// Billing
		if(($value['state_code']==$b_state) && ($value['country_code']==$b_country))
			 $result['billing'] = $value['state'];
		// Shipping
		if(($value['state_code']==$s_state) && ($value['country_code']==$s_country))
			 $result['shipping'] = $value['state'];
	}
	if(empty($result['billing']))
		$result['billing'] = $b_state;
	if(empty($result['shipping']))
		$result['shipping'] = $s_state;
	return $result;
}
function func_export_csv($data,$title) {
	$output = $data;
	$size_in_bytes = strlen($output);
	   header("Content-type: application/vnd.ms-excel");
	   header("Content-disposition: csv; filename=".$title . '_' . date("Y-m-d") . ".csv; size=$size_in_bytes");
	   return $output;
}
# Define new line
$newline = "\n";
# Define CSV fields
$output =  '"website","email","group_id","prefix","firstname","middlename","lastname","suffix","password_hash","taxvat","billing_prefix","billing_firstname","billing_middlename","billing_lastname","billing_suffix","billing_street_full","billing_city","billing_region","billing_country","billing_postcode","billing_telephone","billing_company","billing_fax","shipping_prefix","shipping_firstname","shipping_middlename","shipping_lastname","shipping_suffix","shipping_street_full","shipping_city","shipping_region","shipping_country","shipping_postcode","shipping_telephone","shipping_company","shipping_fax","created_in","is_subscribed"';
$output .= "$newline";
$states = func_query("SELECT $sql_tbl[states] .state, $sql_tbl[states] .code AS state_code, $sql_tbl[states] .country_code FROM $sql_tbl[states], $sql_tbl[countries] WHERE $sql_tbl[states] .country_code=$sql_tbl[countries] .code AND $sql_tbl[countries] .active='Y'");
# total customers
$sql = "SELECT COUNT(*) AS total_results FROM $sql_tbl[customers] where login NOT LIKE 'anonymous%'";
$results = func_query($sql);
$total_results=$results[0]["total_results"];
# number of records to return each loop
$num_of_records=150;
# start for loop to feed out data
for($i=0; $i<=$total_results; $i+=$num_of_records) {
  #$q = "SELECT * FROM xcart_customers where login NOT LIKE 'anonymous%' "; // Memory Hog with SELECT *
  $q = "SELECT email,title,firstname,lastname,password,title,b_address,b_city,b_country,b_zipcode,phone,company,fax,s_address,s_city,s_country,s_zipcode FROM xcart_customers where login NOT LIKE 'anonymous%' LIMIT $i,$num_of_records ";
  $result = func_query($q);
  foreach($result as $key=>$value){
       # Uncomment for version 4.0+ of X-Cart
  	   $result[$key]['password_hash'] = md5(text_decrypt($value['password']));
       # for version 3.5 and below of X-Cart
  	   #$result[$key]['password_hash'] = text_decrypt($value['password']);
  	   $realstates = resolveState($value['b_state'], $value['b_country'], $value['s_state'], $value['s_country']);
  	   $result[$key]['b_real_state'] = $realstates['billing'];
  	   $result[$key]['s_real_state'] = $realstates['shipping'];
  }
  foreach($result as $key => $value) {
    #func_print_r($result);exit;
  	$output .= '"base",';
  	$output .= '"' . $value['email'] . '",';
  	$output .= '"General",';
  	$output .= '"' . $value['title'] . '",';
  	$output .= '"' . $value['firstname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['lastname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['password_hash'] . '",';
  	$output .= '"",';
  	# Billing info
  	$output .= '"' . $value['title'] . '",';
  	$output .= '"' . $value['firstname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['lastname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['b_address'] . '",';
  	$output .= '"' . $value['b_city'] . '",';
  	$output .= '"' . $value['b_real_state'] . '",';
  	$output .= '"' . $value['b_country'] . '",';
  	$output .= '"' . $value['b_zipcode'] . '",';
  	$output .= '"' . $value['phone'] . '",';
  	$output .= '"' . $value['company'] . '",';
  	$output .= '"' . $value['fax'] . '",';
  	# Shipping Info
  	$output .= '"' . $value['title'] . '",';
  	$output .= '"' . $value['firstname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['lastname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['s_address'] . '",';
  	$output .= '"' . $value['s_city'] . '",';
  	$output .= '"' . $value['s_real_state'] . '",';
  	$output .= '"' . $value['s_country'] . '",';
  	$output .= '"' . $value['s_zipcode'] . '",';
  	$output .= '"' . $value['phone'] . '",';
  	$output .= '"' . $value['company'] . '",';
  	$output .= '"' . $value['fax'] . '",';
  	$output .= '"default",';
  	$output .= '"0"';
  	$output .= "$newline";
  }
}
#func_print_r($output); // uncomment to see output
print func_export_csv($output,"xcart_customers");
?>

Here is the original code by Spydor:

<?php
require "../auth.php";
$states = func_query("SELECT $sql_tbl[states] .state, $sql_tbl[states] .code AS state_code, $sql_tbl[states] .country_code FROM $sql_tbl[states], $sql_tbl[countries] WHERE $sql_tbl[states] .country_code=$sql_tbl[countries] .code AND $sql_tbl[countries] .active='Y'");
#print_r($states);
$q = "SELECT * FROM xcart_customers where login NOT LIKE 'anonymous%' ";
$result = func_query($q);
foreach($result as $key=>$value){
       $result[$key]['password_hash'] = md5(text_decrypt($value['password']));
       $realstates = resolveState($value['b_state'], $value['b_country'], $value['s_state'], $value['s_country']);
       $result[$key]['b_real_state'] = $realstates['billing'];
       $result[$key]['s_real_state'] = $realstates['shipping'];
}
#print_r($result);
$output =  '"website","email","group_id","prefix","firstname","middlename","lastname","suffix","password_hash","taxvat","billing_prefix","billing_firstname","billing_middlename","billing_lastname","billing_suffix","billing_street_full","billing_city","billing_region","billing_country","billing_postcode","billing_telephone","billing_company","billing_fax","shipping_prefix","shipping_firstname","shipping_middlename","shipping_lastname","shipping_suffix","shipping_street_full","shipping_city","shipping_region","shipping_country","shipping_postcode","shipping_telephone","shipping_company","shipping_fax","created_in","is_subscribed"';
$output .= "1512";
foreach($result as $key => $value){
    $output .= '"base",';
    $output .= '"' . $value['email'] . '",';
    $output .= '"General",';
    $output .= '"' . $value['title'] . '",';
    $output .= '"' . $value['firstname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['lastname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['password_hash'] . '",';
    $output .= '"",';
    # Billing info
    $output .= '"' . $value['title'] . '",';
    $output .= '"' . $value['firstname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['lastname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['b_address'] . '",';
    $output .= '"' . $value['b_city'] . '",';
    $output .= '"' . $value['b_real_state'] . '",';
    $output .= '"' . $value['b_country'] . '",';
    $output .= '"' . $value['b_zipcode'] . '",';
    $output .= '"' . $value['phone'] . '",';
    $output .= '"' . $value['company'] . '",';
    $output .= '"' . $value['fax'] . '",';
    # Shipping Info
    $output .= '"' . $value['title'] . '",';
    $output .= '"' . $value['firstname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['lastname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['s_address'] . '",';
    $output .= '"' . $value['s_city'] . '",';
    $output .= '"' . $value['s_real_state'] . '",';
    $output .= '"' . $value['s_country'] . '",';
    $output .= '"' . $value['s_zipcode'] . '",';
    $output .= '"' . $value['phone'] . '",';
    $output .= '"' . $value['company'] . '",';
    $output .= '"' . $value['fax'] . '",';
    $output .= '"default",';
    $output .= '"0"';
    $output .= "1512";
}
print func_export_csv($output,"xcart_customers") ;
function resolveState($b_state,$b_country,$s_state,$s_country){
    global $states;
    $result = array();
    foreach($states as $key=>$value){
        // Billing
        if(($value['state_code']==$b_state) && ($value['country_code']==$b_country))
             $result['billing'] = $value['state'];
        // Shipping
        if(($value['state_code']==$s_state) && ($value['country_code']==$s_country))
             $result['shipping'] = $value['state'];
    }
    if(empty($result['billing']))
        $result['billing'] = $b_state;
    if(empty($result['shipping']))
        $result['shipping'] = $s_state;
    return $result;
}
function func_export_csv($data,$title) {
    $output = $data;
    $size_in_bytes = strlen($output);
       header("Content-type: application/vnd.ms-excel");
       header("Content-disposition: csv; filename=".$title . '_' . date("Y-m-d") . ".csv; size=$size_in_bytes");
       return $output;
}

Here is the original posting by Spydor:
Magento – Migrating users from Xcart to Magento – General Forum – eCommerce Software for Growth.

Possibly Related Posts:


  • http://redbeardtechnologies.wordpress.com Red Beard

    Awesome post! I have to port a Xcart 3.5 customer database to Magento. I thought it might take me a couple days to complete the task. Now I can go home early this week :)

    If I hit any snags I will let you know.
    Thanks!

  • Pingback: Converting Your Xcart Shopping Cart To Magento « Redbeard Technologies Software and Systems Development Blog

  • Dave

    Hello, Awesome.rnThis helped me so much, i could not save the CSV,rnBut i could open it and then save it..rnAwesome, big thank you !! Keep up the good works.. !!!

  • Joe

    I tried this today and everything seemed to work fine.. until I tried to import the file into magento 1.6nnI got this error: “Can not find required columns: _website”nnAny idea on why I am getting this error?

  • Joe

    It then tells me to “Please fix errors and re-upload file”

  • Anonymous

    More than likely you need to add a website ID, and Store ID’s into the import file in later versions.

  • Vijay

    i got Fatal error: Call to undefined function text_decrypt() in /usr/home/web/users/a0015079/html/admin/migrate.php on line 57

  • Ajay

    my xcart version is 4.5 and i got Fatal error: Call to undefined function text_decrypt() in /usr/home/web/users/a0015079/html/admin/migrate.php on line 57  

  • molotovbliss

    I’m not familiar with X-Cart version 4.5’s password encryption methods, text_decrypt() was used previously to encode passwords, I would dig around the source code or their forums to find out what the proper means of encoding the passwords for 4.5 is.

  • http://magento.ocodewire.com/ shanaya sharma

    Nice tips!!