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

  • Select an option

    Learn more about clone URLs
  • Save ajankuv/33f162fd73e5d6dbccede3c23b030302 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
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

ajankuv commented Feb 14, 2018 •
edited
Loading

Copy link
Copy Markdown
Author

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

ajankuv commented Feb 16, 2018

Copy link
Copy Markdown
Author

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

@mirkaone

mirkaone commented Jun 2, 2018

Copy link
Copy Markdown

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.

ghost commented Feb 26, 2019

Copy link
Copy Markdown

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

@sumitnautiyal

sumitnautiyal commented Apr 7, 2020 •
edited
Loading

Copy link
Copy Markdown

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

@cgsoratto

cgsoratto commented Mar 7, 2022

Copy link
Copy Markdown

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