Hallo,
ich habe kürzlich Produkte, Kategorien, Kunden und Kundendaten von einem Magento-Shop zu Zen Cart 1.5.5 gehoben. Wenn jemand etwas ähnliches vorhaben sollte, könnten die SQL Schnipsel vielleicht hilfreich sein.
... vielleicht hilft's
Gruß Knut
ich habe kürzlich Produkte, Kategorien, Kunden und Kundendaten von einem Magento-Shop zu Zen Cart 1.5.5 gehoben. Wenn jemand etwas ähnliches vorhaben sollte, könnten die SQL Schnipsel vielleicht hilfreich sein.
Code:
# Erstelle in der Magento DB eine Tabelle 'AA_ZC_countries' und importiere die Daten aus der Zen Cart Datenbank Tabelle 'countries' # Create Views in der Magento Datenbank. ACHTUNG!!! Die ids musst Du vorher prüfen, ich kann nicht sagen ob die bei jeder Magento-Installation gleich sind; die meissten findest Du # in der Tabelle 'eav_attribute'. Wenn Du weitere Produkt-Daten wie z.B. Bilder übernehmen willst, musst Du die Abfrage erweitern. # OK? Dann los! # Products CREATE VIEW `products` AS SELECT DISTINCT `cpf1`.`entity_id` AS `products_id`, `cpf1`.`price` AS `products_price`, `cpf1`.`sku` AS `products_model`, `cpf1`.`created_at` AS `products_date_added`, `cpf1`.`updated_at` AS `products_last_modified`, `cpf1`.`weight` AS `products_weight`, `cpei`.`value` AS `manufacturers_id`, `ccp`.`category_id` AS `master_categories_id`, `cpev`.`value` AS `products_ean` FROM `catalog_product_flat_1` `cpf1` LEFT JOIN `catalog_category_product` `ccp` ON `cpf1`.`entity_id`=`ccp`.`product_id` LEFT JOIN `catalog_product_entity_int` `cpei` ON `cpf1`.`entity_id`=`cpei`.`entity_id` AND `cpei`.`attribute_id`='81' LEFT JOIN `catalog_product_entity_varchar` `cpev` ON `cpf1`.`entity_id`=`cpev`.`entity_id` AND `cpev`.`attribute_id`='136'; #Products_description CREATE VIEW `products_description` AS SELECT `cpet`.`entity_id` AS `products_id`,`cpet`.`value` AS `products_description`, `cpev`.`value` AS `products_name` FROM `catalog_product_entity_text` `cpet` LEFT JOIN `catalog_product_entity_varchar` `cpev` ON `cpev`.`entity_id`=`cpet`.`entity_id` AND `cpev`.`store_id`='0' AND `cpev`.`attribute_id`='82' WHERE `cpet`.`attribute_id`='72' AND `cpet`.`store_id`='0'; # products_to_categories CREATE VIEW `products_to_categories` AS SELECT DISTINCT `category_id` AS `categories_id`, `product_id` AS `products_id` FROM `catalog_category_product`; # manufacturers CREATE VIEW `manufacturers` AS SELECT `option_id` AS `manufacturers_id`, `value` AS `manufacturers_name` FROM `eav_attribute_option_value` WHERE `store_id`='0' AND `value_id`>'1000'; # categories CREATE VIEW `categories` AS SELECT `entity_id` AS `categories_id`, `parent_id`, `created_at` AS `date_added`, `updated_at` AS `last_modified`, `position` AS `sort_order` FROM `catalog_category_flat_store_1` WHERE `meta_keywords`!=''; # categories_description CREATE VIEW `categories_description` AS SELECT `ccfs1`.`entity_id` AS `categories_id`,`ccfs1`.`name` AS `categories_name`, `ccet`.`value` AS `categories_description` FROM `catalog_category_flat_store_1` `ccfs1` LEFT JOIN `catalog_category_entity_text` `ccet` ON `ccfs1`.`entity_id`=`ccet`.`entity_id` AND `ccet`.`attribute_id`='44' AND `ccet`.`store_id`='0' WHERE `ccfs1`.`meta_keywords`!=''; # meta_tags_categories_description CREATE VIEW `meta_tags_categories_description` AS SELECT DISTINCT `ccet`.`entity_id` AS `categories_id`, (SELECT `ccet1`.`value`FROM `catalog_category_entity_text` `ccet1` WHERE `ccet1`.`attribute_id`='47' AND `ccet1`.`entity_id`=`ccet`.`entity_id` AND `ccet1`.`value`!='' AND `ccet1`.`store_id`='1') AS `metatags_keywords`, (SELECT `ccet2`.`value`FROM `catalog_category_entity_text` `ccet2` WHERE `ccet2`.`attribute_id`='48' AND `ccet2`.`entity_id`=`ccet`.`entity_id` AND `ccet2`.`value`!='' AND `ccet2`.`store_id`='1') AS `metatags_description`, IF(`ccev`.`attribute_id`='41' , `ccev`.`value`, '') AS `metatags_title` FROM `catalog_category_entity_text` `ccet` LEFT JOIN `catalog_category_entity_varchar` `ccev` ON `ccev`.`entity_id`=`ccet`.`entity_id` AND `ccev`.`store_id`='1' AND `ccev`.`value`!='' AND `ccev`.`attribute_id`='41' WHERE `ccet`.`store_id`='1' AND `ccet`.`value`!='' AND (`ccet`.`attribute_id`='47' OR `ccet`.`attribute_id`='48'); # meta_tags_products_description CREATE VIEW `meta_tags_products_description` AS SELECT DISTINCT `ccet`.`entity_id` AS `products_id`, (SELECT `ccet1`.`value`FROM `catalog_product_entity_text` `ccet1` WHERE `ccet1`.`attribute_id`='83' AND `ccet1`.`entity_id`=`ccet`.`entity_id` AND `ccet1`.`value`!='' AND `ccet1`.`store_id`='0') AS `metatags_keywords`, (SELECT `ccet2`.`value`FROM `catalog_product_entity_text` `ccet2` WHERE `ccet2`.`attribute_id`='73' AND `ccet2`.`entity_id`=`ccet`.`entity_id` AND `ccet2`.`value`!='' AND `ccet2`.`store_id`='0') AS `metatags_description`, IF(`ccev`.`attribute_id`='71' , `ccev`.`value`, '') AS `metatags_title` FROM `catalog_product_entity_text` `ccet` LEFT JOIN `catalog_product_entity_varchar` `ccev` ON `ccev`.`entity_id`=`ccet`.`entity_id` AND `ccev`.`store_id`='0' AND `ccev`.`value`!='' AND `ccev`.`attribute_id`='71' WHERE `ccet`.`store_id`='0' AND `ccet`.`value`!='' AND (`ccet`.`attribute_id`='83' OR `ccet`.`attribute_id`='73'); # customers CREATE VIEW `customers` AS SELECT `c`.`entity_id` AS `customers_id`, `c`.`email` AS `customers_email_address`, (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '5') AS `customers_firstname`, (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '7') AS `customers_lastname`, (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '31') AS `customers_telephone`, (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '32') AS `customers_fax` FROM `customer_entity` AS `c` LEFT JOIN `customer_address_entity` AS `ca` ON `c`.`entity_id` = `ca`.`parent_id` LEFT JOIN `customer_address_entity_text` AS `cat` ON `cat`.`entity_id` = `ca`.`entity_id` GROUP BY `c`.`entity_id`; # address_book CREATE VIEW `address_book` AS SELECT `c`.`entity_id` AS `customers_id`, (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '24') AS `entry_company`, (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '5') AS `entry_firstname`, (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '7') AS `entry_lastname`, (SELECT `fn`.`value` FROM `customer_address_entity_text` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '25') AS `entry_street_address`, (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '30') AS `entry_postcode`, (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '26') AS `entry_city`, (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn.entity_id` AND `fn`.`attribute_id` = '28') AS `entry_state`, (SELECT `countries_id` FROM `AA_ZC_countries` WHERE `countries_iso_code_2`=(SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '27')) AS `entry_country_id` FROM `customer_entity` AS `c` LEFT JOIN `customer_address_entity` AS `ca` ON `c`.`entity_id` = `ca`.`parent_id` LEFT JOIN `customer_address_entity_text` AS `cat` ON `cat`.`entity_id` = `ca`.`entity_id` GROUP BY `c`.`entity_id`; # customers_info CREATE VIEW `customers_info` AS SELECT `entity_id` AS `customers_info_id`, `created_at` AS `customers_info_date_of_last_logon`, '1' AS `customers_info_number_of_logons`, `created_at` AS `customers_info_date_account_created`, `updated_at` AS `customers_info_date_account_last_modified`, '0' AS `global_product_notifications` FROM `customer_entity`; # Jetzt kannst Du die Views als Tabellen im PhpMyAdmin exportieren (einzeln, alle zusammen ging, jedenfalls bei mir, nicht) # und in die Zen Cart Datenbank importieren (auch mit PhpMyAdmin). Wenn die Daten schon einmal importiert wurden und sich geändert haben, benutze 'REPLACE' #Jetzt noch 'customers' mit 'address_book' verknüppern UPDATE `customers` SET `customers_default_address_id`= (SELECT `address_book_id` FROM `address_book` WHERE `customers`.`customers_id`=`address_book`.`customers_id`); # und Einträge ohne Adresse löschen. DELETE FROM `address_book` WHERE `entry_street_address`=''; DELETE FROM `customers_info` WHERE `customers_info_id` NOT IN (SELECT `customers_id` FROM `address_book`); DELETE FROM `customers` WHERE `customers_id` NOT IN (SELECT `customers_id` FROM `address_book`); # Fertig!
Gruß Knut