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;
@ajankuv
Copy link
Author

ajankuv commented Feb 14, 2018 •
edited
Loading

I didn't want to pay some company 100's of $$$ to migrate my data. This will export it in the magento 2 format with image paths for import.

I recommend using this with phpmyadmin or something similar.

@ajankuv
Copy link
Author

ajankuv commented Feb 16, 2018

Updated Feb, 16th 2018 for thumbnail and small image. This allows thumbnails to generate for the main image you export from prestashop.

@mirkaone
Copy link

mirkaone commented Jun 2, 2018

how exactly, do i use this in my phpMyAdmin?
I'm prety new at this. But it'll not stop me from giveing it a try.

Copy link

ghost commented Feb 26, 2019

There are two errors in the SQL. It is broken on lines 29 and 90.

@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