Requête export des produits de Prestashop 1.6 vers 1.7
| Administrator
Nous souhaitons exporter tous les produits d'un site sous Prestashop 1.6 vers la dernière version de Prestashop 1.7.
ATTENTION ! Cette requête est uniquement valable pour une boutique source en Prestashop 1.6 et une boutique cible en Prestashop 1.7.
Dans cette requête il y a une erreur au niveau de la remontée des légendes images.
Voici le détail de la requête a effectuer sur phpMyAdmin ou équivalent en adaptant les variables suivantes :
- "Get the shop domaine" pour mettre votre domaine à vous.
- Mettre '1' AS 'ID Boutique' qui doit correspondre au bon numéro de boutique,
- Vérifier la dernière partie du WHERE afin de valider les bonnes valeurs
- WHERE pl.id_lang = 5
- AND cl.id_lang = 5
- AND pil.id_lang = 5
- AND p.id_shop_default = 1
Il faut également penser à changer les préfixes des tables si différent de ps_
Vous pouvez maintenant exécuter la requête afin d'extraire pour les lignes de votre base de données.
SELECT p.id_product, p.active, pl.name AS 'Name',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Categories (x,y,z...)',
p.price AS 'Price tax excluded',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Discount amount',
IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Discount percent',
pr.from AS 'Discount from (yyyy-mm-dd)',
pr.to AS 'Discount to (yyyy-mm-dd)',
p.reference AS 'Reference #',
p.supplier_reference AS 'Supplier reference #',
ps.name AS 'Supplier',
pm.name AS 'Manufacturer',
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
p.ecotax AS 'Ecotax',
p.width AS 'Width',
p.height AS 'Height',
p.depth AS 'Depth',
p.weight AS 'Weight',
'' AS 'Délai Prod Stock',
'' AS 'Délai Prod Epuisé',
sa.quantity AS 'Quantity',
p.minimal_quantity AS 'Minimal quantity',
3 AS 'Niv Stk bas',
1 AS 'Envoyer Email Stk Bas',
'both' AS 'Visibility',
p.additional_shipping_cost AS 'Additional shipping cost',
p.unity AS 'Unity',
p.unit_price_ratio AS 'Unit price',
pl.description_short AS 'Short description',
pl.description AS 'Description',
IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Tags (x,y,z...)',
pl.meta_title AS 'Meta title',
pl.meta_keywords AS 'Meta keywords',
pl.meta_description AS 'Meta description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order (0 = No, 1 = Yes)',
'' AS 'Product available date',
p.date_add 'Product creation date',
p.show_price AS 'Show price (0 = No, 1 = Yes)',
GROUP_CONCAT(DISTINCT(CONCAT('https://',
-- get the shop domain
'www.mon-domaine.fr',
-- IFNULL(conf.value, 'undefined_domain'),--
-- the path to the pictures folder
'/img/p/',
-- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
-- assuming we have smaller image id than 100'000 ;)
IF(CHAR_LENGTH(pi.id_image) >= 5,
-- if we have 5 digits for the image id
CONCAT(
-- take the first digit
SUBSTRING(pi.id_image, -5, 1),
-- add a slash
'/'),
''),
-- repeat for the next digits
IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
IF(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
-- add the image id
pi.id_image,
-- put the image extension
'.jpg')) SEPARATOR ', ') AS 'Images (x,y,z...)',
GROUP_CONCAT(DISTINCT(CONCAT(pil.legend)) SEPARATOR ',') AS 'Legend Image(x,y,z...)',
0 AS 'Delete existing images (0 = No, 1 = Yes)',
GROUP_CONCAT(DISTINCT(CONCAT((fl.name), ':', (fvl.value), ':0')) SEPARATOR ',') AS 'Feature (Name:Value:Position)',
p.online_only AS 'Available online only (0 = No, 1 = Yes)',
p.condition AS 'Etat',
0 AS 'Personnalisable (0 = No, 1 = Yes)',
0 AS 'Fichier Telechgt (0 = No, 1 = Yes)',
0 AS 'Champ Texte (0 = No, 1 = Yes)',
p.out_of_stock AS 'Out of stock',
p.is_virtual AS 'Prod Démat (0 = No, 1 = Yes)',
'' AS 'Url fichier',
0 AS 'Nbre download',
'' AS 'Date expiration',
0 AS 'Nbre de jour',
'1' AS 'ID Boutique',
0 AS 'Gest Stk Avancé',
NULL AS 'Fonct du stock',
NULL AS 'Entrepôt',
'' AS 'Accessoires (x,y,z...)'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON(cp.id_category = cl.id_category)
LEFT JOIN ps_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product)
LEFT JOIN ps_image_lang pil ON(pi.id_image = pil.id_image)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = ''
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)
WHERE pl.id_lang = 5
AND cl.id_lang = 5
AND pil.id_lang = 5
AND p.id_shop_default = 1
AND p.active =1
GROUP BY p.id_product;
Une fois l'extraction terminée, il faut modifier l’id les codes de taxes de la boutique cible avec Notepad.
Vous êtes prêt à remonter les produits sur votre Prestashop en dernière version.
