Shopex迁移数据（商品）到Magento手把手教程

声明：这个教程是以我们公司的实际数据为例——在涉及商品属性的时候，大家要自己按照你们的情况来处理了，有问题可以联系我 lanshunfang###gmail.com

★从Shopex转移数据(商品)到Magento

1. 备份远程服务器中的Magento 数据库到本地
   
2. 
   
   
3. 清理旧数据：在本地数据库删除所有旧图片：
   

   ★UPDATE `catalog_product_entity_media_gallery` SET `value` = ""
   ★删除/media/catalog/product/下面的
   
   

4. 把Shopex的网站上的图片，全部拷贝到var/import下面　——　参考：http://www.magentocommerce.com/boards/viewthread/6220/P75/#t130135
   
   
   
   
5. 打开网站：PHPmyadmin——这个要具体问题具体处理了
   1. 执行语句，在Shopex里面生成相应的表格：
      1. 生成全部信息
         1. 生成图书 （对报佳音价分两次处理）：
            
            DROP TABLE IF exists `ShopexToMagento_Books_is_cs`;
            
            CREATE TABLE `ShopexToMagento_Books_is_cs` (
            
            SELECT LEFT(TRIM(`bn`),4) AS `sku`," gnic,baojiayin" AS `websites`,"Book" AS `attribute_set`,"simple" AS `type`, SUBSTRING(`image_file`,13,48) AS `image`,SUBSTRING(`image_file`,13,48) AS `small_image`,SUBSTRING(`image_file`,13,48) AS `thumbnail`, `p_23` AS `isbn`, "1" AS `manage_stock`, "1" AS `stock_status_changed_automatically`, `unit` AS `notify_stock_qty`, CAST(`weight` AS SIGNED) AS ` safety_stock_level`, "No" AS `enable_googlecheckout`,"150" AS `weight`,`mktprice` AS `listprice`,CASE WHEN `marketable` = "false" THEN "Disabled" ELSE "Enabled" END AS `status`,"Catalog, Search" AS `visibility`, "None" AS `tax_class_id`,`store` AS `qty`,SUBSTRING(`bn`,6) AS `shelf_id`, `p_24` AS `publication_date`, `p_26` AS `supplier`,`cost` AS `cost` ,"1" AS ` use_config_manage_stock`,"1" AS ` use_config_notify_stock_qty`, IF( `store`>0 ,"1","0") AS ` is_in_stock`, "1" AS ` is_qty_decimal`,
            
            
            `mktprice` AS `price_is`,
            `price` AS `price_cs`,
            
            
            `name` AS `name`,
            `p_21` AS `author`,
            
            CASE
            
            WHEN `p_1`= '0' THEN '其他出版社'
            
            WHEN `p_1`= '1' THEN '百花文艺出版社'
            
            WHEN `p_1`= '2' THEN '百家出版社'
            
            WHEN `p_1`= '3' THEN '北京出版社'
            
            WHEN `p_1`= '4' THEN '北京大学出版社'
            
            WHEN `p_1`= '5' THEN '北京图书馆出版社'
            
            WHEN `p_1`= '6' THEN '当代中国出版社'
            
            WHEN `p_1`= '7' THEN '东方出版社'
            
            WHEN `p_1`= '8' THEN '甘肃人民美术出版社'
            
            WHEN `p_1`= '9' THEN '光明日报出版社'
            
            WHEN `p_1`= '10' THEN '广播电视大学出版社'
            
            WHEN `p_1`= '11' THEN '广西师范大学'
            
            WHEN `p_1`= '12' THEN '贵州人民出版社'
            
            WHEN `p_1`= '13' THEN '哈尔滨出版社'
            
            WHEN `p_1`= '14' THEN '海南出版社'
            
            WHEN `p_1`= '15' THEN '海峡文艺出版社'
            
            WHEN `p_1`= '16' THEN '海洋出版社'
            
            WHEN `p_1`= '17' THEN '航空工业社'
            
            WHEN `p_1`= '18' THEN '黑龙江人民出版社'
            
            WHEN `p_1`= '19' THEN '华夏出版社'
            
            WHEN `p_1`= '20' THEN '华艺出版社'
            
            WHEN `p_1`= '21' THEN '机械工艺出版社'
            
            WHEN `p_1`= '22' THEN '吉林摄影出版社'
            
            WHEN `p_1`= '23' THEN '吉林文史出版社'
            
            
            ELSE ""
            
            END
            
            AS `publisher`,
            
            `intro` AS `description`, `brief` AS `short_description`, `name` AS `product_name`, "simple" AS `product_type_id`, `p_25` AS `number_of_words`, `p_22` AS `translator`
            
            FROM `bdb_goods`
            
            WHERE
            
            LEFT(TRIM(`bn`),4) < 3000
            AND LEFT(TRIM(`bn`),4) > 0
            AND `type_id` <> "2"
            AND `type_id` <> "1"
            
            
            );
            
            
            
            
            
            
            
            
            
            
            
            
         2. 生成礼品
            
            
            
            DROP TABLE IF exists `ShopexToMagento_Giftware_cs`;
            
            CREATE TABLE `ShopexToMagento_ Giftware _cs` (
            
            SELECT LEFT(TRIM(`bn`),4) AS `sku`, " baojiayin" AS `websites`," Giftware" AS `attribute_set`,"simple" AS `type`, SUBSTRING(`image_file`,13,48) AS `image`,SUBSTRING(`image_file`,13,48) AS `small_image`,SUBSTRING(`image_file`,13,48) AS `thumbnail`, ` p_21` AS ` size`, "1" AS `manage_stock`, "1" AS `stock_status_changed_automatically`, `unit` AS `notify_stock_qty`, CAST(`weight` AS SIGNED) AS ` safety_stock_level`, "No" AS `enable_googlecheckout`,"300" AS `weight`,`mktprice` AS `listprice`,CASE WHEN `marketable` = "false" THEN "Disabled" ELSE "Enabled" END AS `status`,"Catalog, Search" AS `visibility`, "None" AS `tax_class_id`,`store` AS `qty`,SUBSTRING(`bn`,6,6) AS `shelf_id`, `p_22` AS `supplier`,`cost` AS `cost`,"1" AS ` use_config_manage_stock`,"1" AS ` use_config_notify_stock_qty`, IF( `store`>0 ,"1","0") AS ` is_in_stock`, "1" AS ` is_qty_decimal`,
            
            
            
            `price` AS `price`,
            
            
            `name` AS `name`,
            
            
            `intro` AS `description`, `brief` AS `short_description`, `name` AS `product_name`, "simple" AS `product_type_id`
            
            FROM `bdb_goods`
            
            WHERE
            
            LEFT(TRIM(`bn`),4) > 3000
            AND LEFT(TRIM(`bn`),4) > 0
            AND `type_id` <> "3"
            AND `type_id` <> "1"
            
            
            );
            SELECT *
            FROM `ShopexToMagento_Giftware_cs`
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
      2. 检查OpenOffice程序是否使用Unicode作为默认的文档编码方式
      3. 必须用 OpenOffice打开上面导出的CSV文件，对应“国际中文”、“中文”storeview
         1. 用Open Office Calc，选择所有的字段，特别是注意ISBN，将其类型转化为“文本”，并检查，确保没有科学记数法
         2. 对姓名字段中出现的“中点变成问号”的乱码情况，批量替换成中点。
         3. 另存为shopextomagento_*_cs_default.csv，
            • 删除`price_is`,并修改`price_cs`成`price`，
         4. 另存为shopextomagento_*_is_price.csv，
            • 删除`price_cs`,并修改`price_is`成`price`，只留下/ `sku` /和/`price`/
         5. 利用notepad++，检查上面的两个文件还有ShopexToMagento_Giftware_cs是否已经以UTF-8方式， 进行重编码
         6. 上传到var/import
      4. 后台导入设定：import profile里面，点击“Import All Products”，设计导入的Store为Default (Admin) Value，保存并继续编辑，
      5. 切换admin的界面语言为英文
      6. Run Profile ，分别导入shopextomagento_*_cs _default.csv、ShopexToMagento_Giftware_cs 到Magento
      7. 等待导入完毕
      8. 如果导入的是礼品的话，请导入后，查找所有Giftware属性的商品，然后批量将商品目录设为指定目录，网站设为中国商店
         
      9. 后台导入设定：import profile里面，点击“Import All Products”，设计导入的Store为国际English，保存并继续编辑
      10. 导入shopextomagento_*_is _price.csv到Magento
      11. 导入productFormat_English.csv到Magento
      12. 删除var/import下面的所有图片、CSV文件
      13. 检查产品图像是否存在被Exclude的状态，如果是，请参考这个帖子：
          http://www.magentocommerce.com/boards/viewthread/42150/#t154066
          
          可能需要运行：
          update catalog_product_entity_media_gallery_value set disabled = 0 WHERE position = 2;
          update catalog_product_entity_media_gallery_value set disabled = 3 WHERE position <>2;
          delete from catalog_product_entity_media_gallery_value WHERE disabled =3;
          delete from catalog_product_entity_media_gallery WHERE value = "";