Export products from Prestashop 1.6 to CSV file

export.sql

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
LIMIT 100000;