Export X-Cart Products for Magento

xcart to mag Export X Cart Products for Magento

Something I have been wanting to do for awhile is attempt at writing a Magento X-Cart import module. Since I’m no where near getting such completed, and was needing to get a clients old X-Cart version 3.5.X product data imported into Magento 1.2.x, I decided to do it the old fashioned way. Export the X-Cart data into an Excel document and map the exported data. Since I had no idea what fields Magento would require for the import. I simply exported my existing Magento products I had added manually. If you don’t have any simply create a single test product and then proceed to export from Magento.

Please keep in mind this is a very basic import, as Product Options and Categories from X-Cart are ignored. I’m open to any ideas and/or thoughts to achieve this, so please feel free to add any comments.

Here is the SQL Query that I have used to grab the basic essential data out of X-Cart:

SELECT
xcart_products.productid,
xcart_products.productcode,
xcart_products.product,
xcart_products.brand,
xcart_products.model,
xcart_products.weight,
xcart_products.categoryid,
xcart_products.list_price,
xcart_products.descr,
xcart_products.fulldescr,
xcart_products.param00,
xcart_products.param01,
xcart_products.param02,
xcart_products.param03,
xcart_products.`type`,
xcart_pricing.productid,
xcart_pricing.quantity,
xcart_pricing.price,
xcart_thumbnails.image_path,
xcart_images.image_path,
xcart_images.image_x,
xcart_images.image_y,
xcart_images.image_size,
xcart_images.alt,
xcart_pricing.membership,
xcart_categories.category
FROM
xcart_products
Left Join xcart_pricing ON xcart_products.productid = xcart_pricing.productid
Left Join xcart_thumbnails ON xcart_products.productid = xcart_thumbnails.productid
Left Join xcart_images ON xcart_products.productid = xcart_images.productid
Left Join xcart_categories ON xcart_categories.categoryid = xcart_products.categoryid
GROUP BY xcart_products.productid

xcart import1 Export X Cart Products for Magento   

One Remote MySQL Manager I found very useful for alot of this was “NaviCat“, the free version doesn’t support export/importing however they do offer a 30 day trial with all the bells and whistles.

As you can tell I am only exporting xcart_products, xcart_pricing, xcart_thumbnails, xcart_images and xcart_categories. However I did not use xcart_categories when mapping my data in Excel, I merely wanted to export it so it was easier to determine products while in Excel.

When exporting with NaviCat I noticed two Excel options, both seem to yield the same results, however I noticed with one of the options long descriptions over 255 characters were being truncated, here is the option to use for best results I’ve found:
export Export X Cart Products for Magento

A few things to take note while mapping and/or copying your X-Cart exported data to your Magento exported data is:

  • You will need to remove the full path that X-Cart uses in its image tables, i.e. /home/mysite/public_html/
  • Keep a front slash ‘/’ in front of your image filename’s.
  • Copy all of your X-Cart product’s Images via FTP or however you choose. And upload them to /media/import/
  • Keep existing data in the fields of the Magento products export the same. The most note-able are: store, attribute_set and type.
  • You may have some custom data from X-Cart that can be utilized, per say if you were using param00 for a cost field, you can easily map this field into Magento’s custom attributes you can create to be able to port this information over. You can find custom attributes under “Catalog”->”Attributes”->”Manage Attributes” within the Admin.
  • Export as a “Comma Separated Values” (CSV) file. I opened this file up in notepad after exporting and re-saved just to ensure line-breaks and odd characters aren’t miss-interrupted.

All of Magento’s export and import functionality can be found under “System”->”Import/Export”->”Profiles”.

I created a new profile entitled “X-Cart Import” and used the following settings:
xcart import Export X Cart Products for Magento

Here is the XML profile for my X-Cart import profile as well:

    
    
    true
    
    1
    
    catalog/convert_adapter_product
    parse

One Idea I didn’t realize during my first initial import was the import process at least on a low-end server can take hours to complete, so you may want to take a small portion of your spreadsheet, export to CSV and do a small amount of data to ensure you have all of your data properly laid out. I made this mistake and am now in the process of re-importing all 2000+ products after doing a small amount to ensure things are in order.

If at anytime you want to wipe all of your products and start from no products, run this query in NaviCat or PHPMyAdmin:

TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
insert  into `catalog_product_link_type`(`link_type_id`,`code`) values (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
insert  into `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) values (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
insert  into `cataloginventory_stock`(`stock_id`,`stock_name`) values (1,'Default');

(thanks to Mike Smullin on the Magento Forums for this query!)

Whats planned? I am also going to be attempting to apply the same method for importing X-Cart customer data into Magento. As well as making product options and categories importable as well.

There is a good thread on importing product’s in Magento’s community discussing this topic as well.

Possibly Related Posts:


  • http://www.customerservicehelper.com Dan Waldron

    I found your blog on google and read a few of your other posts. I just added you to my Google News Reader. Keep up the good work. Look forward to reading more from you in the future.

  • admin

    Thanks Dan! Was hoping someone was getting some use out of the time spent making posts. Thanks again.

  • Pingback: Product Thumbnail sizes in Magento | molotov.bliss

  • http://kushinara.com himanshu

    hi there,

    i was thinking of moving from xcart too. but i am still confused … :(

    thanks for your cool post. its really very helpfull.

  • admin

    Himanshu, No problem. What are you confused about? :?:

  • http://www.cgiscriptshop.com Alex

    Check this website for great Mysql tool for export of tables and SELECT results:
    http://www.cgiscriptshop.com -> Mysql Data Manager. They must have free download

  • http://www.gamcc.com wow gold

    Good post,This was exactly what I needed to read today! I am sure this has relevance to many of us out there.

  • Lesya

    There’s also other way to import data to magento fm X-cart – you can simply use web service cart2cart. For few bucks they will move allyour data automatically. (http://www.shopping-cart-migration.com)

  • http://www.glokalit.com Ivan

    Thanks for the great post, it helped me program a script that will do a lot of the hard work for you. I release it as donationware and hopefully it will help a lot of people out there. Download here: http://www.glokalit.com/scripts/x-to-m.rar

  • Pingback: Export Xcart Products To Magento « Redbeard Technologies Software and Systems Development Blog

  • http://thinkwebmedia.com Jeff T

    Ivan script has some good stuff in it, but won’t work for my x-cart version which is 4.0.xx

    I think it was made for an earlier version?