Skip to content

Instantly share code, notes, and snippets.

@ajankuv

ajankuv/Prestashop to Magento2 product export

Last active March 7, 2022 11:16
Show Gist options
  • Save ajankuv/33f162fd73e5d6dbccede3c23b030302 to your computer and use it in GitHub Desktop.
Save ajankuv/33f162fd73e5d6dbccede3c23b030302 to your computer and use it in GitHub Desktop.
Download ZIP
Export prestashop 1.6 products for magento 2 import
Raw
Prestashop to Magento2 product export
select ps_product.id_product AS 'sku', ps_product.upc, ps_product.price, ps_product.weight, ps_product.date_add AS 'created_at',
CONCAT('/img/p/',
IF(CHAR_LENGTH(pi.id_image) >= 5,
CONCAT(
SUBSTRING(pi.id_image, -5, 1),
'/'),
''),
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), '/'), ''),
pi.id_image,
'.jpg') as base_image,
IFNULL(GROUP_CONCAT(DISTINCT(CONCAT('/img/p/',
IF(CHAR_LENGTH(pi2.id_image) >= 5,
CONCAT(
-- take the first digit
SUBSTRING(pi2.id_image, -5, 1),
'/'),
''),
IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
pi2.id_image,
'.jpg')) SEPARATOR ', '),'') as 'additional_images',
ps_stock_available.quantity AS 'qty', ps_product_lang.description, ps_product_lang.name,
ps_product_lang.description_short AS 'short_description', ps_product_lang.link_rewrite AS 'url_key',
ps_product_lang.meta_title, ps_product_lang.meta_description,select ps_product.id_product AS 'sku', ps_product.upc, ps_product.price, ps_product.weight, ps_product.date_add AS 'created_at',
CONCAT('/img/p/',
IF(CHAR_LENGTH(pi.id_image) >= 5,
CONCAT(
SUBSTRING(pi.id_image, -5, 1),
'/'),
''),
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), '/'), ''),
pi.id_image,
'.jpg') as base_image,
CONCAT('/img/p/',
IF(CHAR_LENGTH(pi.id_image) >= 5,
CONCAT(
SUBSTRING(pi.id_image, -5, 1),
'/'),
''),
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), '/'), ''),
pi.id_image,
'.jpg') as thumbnail_image,
CONCAT('/img/p/',
IF(CHAR_LENGTH(pi.id_image) >= 5,
CONCAT(
SUBSTRING(pi.id_image, -5, 1),
'/'),
''),
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), '/'), ''),
pi.id_image,
'.jpg') as small_image,
IFNULL(GROUP_CONCAT(DISTINCT(CONCAT('/img/p/',
IF(CHAR_LENGTH(pi2.id_image) >= 5,
CONCAT(
-- take the first digit
SUBSTRING(pi2.id_image, -5, 1),
'/'),
''),
IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
pi2.id_image,
'.jpg')) SEPARATOR ', '),'') as 'additional_images',
ps_stock_available.quantity AS 'qty', ps_product_lang.description, ps_product_lang.name,
ps_product_lang.description_short AS 'short_description', ps_product_lang.link_rewrite AS 'url_key',
ps_product_lang.meta_title, ps_product_lang.meta_description,
'simple' AS product_type, 'Default' AS attribute_set_code, 'All' AS categories
from ps_product
LEFT JOIN ps_product_lang ON ps_product.id_product=ps_product_lang.id_product
LEFT JOIN ps_stock_available ON ps_product.id_product=ps_stock_available.id_product
LEFT JOIN ps_image pi ON ps_product.id_product=pi.id_product and pi.cover = 1
LEFT JOIN ps_image pi2 ON ps_product.id_product=pi2.id_product and pi2.position > 2
GROUP BY ps_product.id_product
ORDER BY ps_product.id_product;
'simple' AS product_type, 'Default' AS attribute_set_code, 'All' AS categories
from ps_product
LEFT JOIN ps_product_lang ON ps_product.id_product=ps_product_lang.id_product
LEFT JOIN ps_stock_available ON ps_product.id_product=ps_stock_available.id_product
LEFT JOIN ps_image pi ON ps_product.id_product=pi.id_product and pi.cover = 1
LEFT JOIN ps_image pi2 ON ps_product.id_product=pi2.id_product and pi2.position > 2
GROUP BY ps_product.id_product
ORDER BY ps_product.id_product;
@sumitnautiyal
Copy link

sumitnautiyal commented Apr 7, 2020 •
edited
Loading

Has anyone found the solution to the query above ? @ajankuv It has errors as pointed out by @ghost.

@cgsoratto
Copy link

Hello people.

Exists some duplicate codes. I did a bit fix in the code:

select ps_product.id_product AS 'sku', ps_product.upc, ps_product.price, ps_product.weight, ps_product.date_add AS 'created_at',
CONCAT('/img/p/',
        IF(CHAR_LENGTH(pi.id_image) >= 5,
            CONCAT(
                SUBSTRING(pi.id_image, -5, 1),
                '/'),
            ''),
        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), '/'), ''),
        pi.id_image,
        '.jpg') as base_image,
IFNULL(GROUP_CONCAT(DISTINCT(CONCAT('/img/p/',
        IF(CHAR_LENGTH(pi2.id_image) >= 5,
            CONCAT(
                -- take the first digit
                SUBSTRING(pi2.id_image, -5, 1),
                '/'),
            ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
        pi2.id_image,
        '.jpg')) SEPARATOR ', '),'') as 'additional_images',
ps_stock_available.quantity AS 'qty', ps_product_lang.description, ps_product_lang.name,
ps_product_lang.description_short AS 'short_description', ps_product_lang.link_rewrite AS 'url_key',
ps_product_lang.meta_title, ps_product_lang.meta_description,
'simple' AS product_type, 'Default' AS attribute_set_code, 'All' AS categories
from ps_product
LEFT JOIN ps_product_lang ON ps_product.id_product=ps_product_lang.id_product
LEFT JOIN ps_stock_available ON ps_product.id_product=ps_stock_available.id_product
LEFT JOIN ps_image pi ON ps_product.id_product=pi.id_product and pi.cover = 1
LEFT JOIN ps_image pi2 ON ps_product.id_product=pi2.id_product and pi2.position > 2
GROUP BY ps_product.id_product
ORDER BY ps_product.id_product;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment