声明:这个教程是以我们公司的实际数据为例——在涉及商品属性的时候,大家要自己按照你们的情况来处理了,有问题可以联系我 lanshunfang###gmail.com
★从Shopex转移数据(商品)到Magento
- 备份远程服务器中的Magento 数据库到本地
-
- 清理旧数据:在本地数据库删除所有旧图片:
★UPDATE `catalog_product_entity_media_gallery` SET `value` = ""
★删除/media/catalog/product/下面的
- 把Shopex的网站上的图片,全部拷贝到var/import下面 —— 参考:http://www.magentocommerce.com/boards/viewthread/6220/P75/#t130135
- 打开网站:PHPmyadmin——这个要具体问题具体处理了
- 执行语句,在Shopex里面生成相应的表格:
- 生成全部信息
- 生成图书 (对报佳音价分两次处理):
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"
);
- 生成礼品
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`
- 生成图书 (对报佳音价分两次处理):
- 检查OpenOffice程序是否使用Unicode作为默认的文档编码方式
- 必须用 OpenOffice打开上面导出的CSV文件,对应“国际中文”、“中文”storeview
- 用Open Office Calc,选择所有的字段,特别是注意ISBN,将其类型转化为“文本”,并检查,确保没有科学记数法
- 对姓名字段中出现的“中点变成问号”的乱码情况,批量替换成中点。
- 另存为shopextomagento_*_cs_default.csv,
- 删除`price_is`,并修改`price_cs`成`price`,
- 另存为shopextomagento_*_is_price.csv,
- 删除`price_cs`,并修改`price_is`成`price`,只留下/ `sku` /和/`price`/
- 利用notepad++,检查上面的两个文件还有ShopexToMagento_Giftware_cs是否已经以UTF-8方式, 进行重编码
- 上传到var/import
- 后台导入设定:import profile里面,点击“Import All Products”,设计导入的Store为Default (Admin) Value,保存并继续编辑,
- 切换admin的界面语言为英文
- Run Profile ,分别导入shopextomagento_*_cs _default.csv、ShopexToMagento_Giftware_cs 到Magento
- 等待导入完毕
- 如果导入的是礼品的话,请导入后,查找所有Giftware属性的商品,然后批量将商品目录设为指定目录,网站设为中国商店
- 后台导入设定:import profile里面,点击“Import All Products”,设计导入的Store为国际English,保存并继续编辑
- 导入shopextomagento_*_is _price.csv到Magento
- 导入productFormat_English.csv到Magento
- 删除var/import下面的所有图片、CSV文件
- 检查产品图像是否存在被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 = "";
- 生成全部信息
- 执行语句,在Shopex里面生成相应的表格: