Mar 16 2009

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.

:idea: 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:

?View Code PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

<?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:

?View Code PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

<?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:


2 comments   |  tags: , , , | posted in Magento Commerce, Web Development

Feb 26 2009

WordPress plugin for easy Magento database connection

A Nifty little php script to display products from Magento in your WordPress Blog.

connect2MAGE | WordPress plugin for easy Magento database connection | Inchoo.

Here’s one from LazyMonk via Magento Connect module for Wordpress to Magento.

Possibly Related Posts:


2 comments   |  tags: , , , , , | posted in Magento Commerce