PrestaShop 1.7 : query to export all data > create a CSV file > import to an other PrestaShop instance

1-prestashop-brands.sql

SELECT 
    m.id_manufacturer AS `ID`,
    m.active AS `Active (0/1)`,
    m.name AS `Name`,
    IFNULL(ml.description, '') AS `Description`,
    IFNULL(ml.short_description, '') AS `Short description`,
    IFNULL(ml.meta_title, '') AS `Meta title`,
    IFNULL(ml.meta_keywords, '') AS `Meta keywords`,
    IFNULL(ml.meta_description, '') AS `Meta description`,
    CONCAT('https://www.my-prestashop.com/img/m/', m.id_manufacturer, '.jpg') AS `Image URL`
FROM ps_manufacturer m
JOIN ps_manufacturer_lang ml 
    ON m.id_manufacturer = ml.id_manufacturer 
    AND ml.id_lang = 1;

1-prestashop-suppliers.sql

SELECT
    su.id_supplier AS `Supplier ID`,
    su.active AS `Active (0/1)`,
    su.name AS `Name *`,
    sul.description AS `Description`,
    sul.meta_title AS `Meta title`,
    sul.meta_keywords AS `Meta keywords`,
    sul.meta_description AS `Meta description`,
    CONCAT('https://www.my-prestashop.com/img/su/', su.id_supplier, '.jpg') AS `Image URL`
FROM ps_supplier su
INNER JOIN ps_supplier_shop sus ON sus.id_supplier = su.id_supplier AND sus.id_shop = 1
LEFT JOIN ps_supplier_lang sul ON sul.id_supplier = su.id_supplier AND sul.id_lang = 1

2-prestashop-categories.sql

SELECT
  ca.`id_category` as 'Category ID',
  ca.`active` as 'Active (0/1)',
  cl.`name` as 'Name',
  clparent.`name` as 'Parent category',
  ca.`is_root_category` as 'Root category (0/1)',
  cl.`description` as 'Description',
  cl.`meta_title` as 'Meta title',
  cl.`meta_keywords` as 'Meta keywords',
  cl.`meta_description` as 'Meta description',
  cl.`link_rewrite` as 'URL rewritten',
  CONCAT('https://www.my-prestashop.com/img/c/', ca.id_category, '.jpg') AS 'Image URL'
FROM `ps_category` ca
JOIN `ps_category_lang` cl ON (cl.`id_category` = ca.`id_category` AND cl.`id_lang` = 1 AND cl.`id_shop` = 1)
JOIN `ps_category_shop` cs ON (ca.`id_category` = cs.`id_category` AND cs.`id_shop` = 1)
JOIN `ps_category_lang` clparent ON (ca.`id_parent` = clparent.`id_category` AND clparent.`id_lang` = 1)
WHERE ca.id_parent <> 1;

3-prestashop-products.sql

SELECT 
  p.id_product AS `Product ID`,
  p.active AS `Active (0/1)`,
  pl.name AS `Name`,
  (
    SELECT IFNULL(GROUP_CONCAT(cl2.name SEPARATOR ','), '')
    FROM ps_category_lang cl2
    JOIN ps_category_product cp2 ON cp2.id_category = cl2.id_category
    WHERE cp2.id_product = p.id_product
      AND cl2.id_lang = 1
    ORDER BY cp2.position DESC
  ) 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)`,
  '' AS `Discount amount`,
  '' AS `Discount percent`,
  '' AS `Discount from (yyyy-mm-dd)`,
  '' AS `Discount to (yyyy-mm-dd)`,
  p.reference AS `Reference #`,
  IFNULL(p.supplier_reference, '') AS `Supplier reference #`,
  IFNULL(su.name, '') AS `Supplier`,
  IFNULL(m.name, '') AS `Manufacturer`,
  IFNULL(p.ean13, '') AS `EAN13`,
  IFNULL(p.upc, '') AS `UPC`,
  '' AS `MPN`,
  p.ecotax AS `Ecotax`,
  p.width AS `Width`,
  p.height AS `Height`,
  p.depth AS `Depth`,
  p.weight AS `Weight`,
  IFNULL(pl.delivery_in_stock, '') AS `Delivery time of in-stock products`,
  IFNULL(pl.delivery_out_stock, '') AS `Delivery time of out-of-stock products with allowed orders`,
  sa.quantity AS `Quantity`,
  p.minimal_quantity AS `Minimal quantity`,
  IFNULL(p.low_stock_threshold, '') AS `Low stock level`,
  p.low_stock_alert AS `Send me an email when the quantity is under this level`,
  p.visibility AS `Visibility`,
  p.additional_shipping_cost AS `Additional shipping cost`,
  IFNULL(p.unity, '') AS `Unity`,
  p.unit_price_ratio AS `Unit price`,
  LEFT(pl.description_short, 900) AS `Summary`,
  pl.description AS `Description`,
  (
    SELECT IFNULL(GROUP_CONCAT(ta2.name SEPARATOR ','), '')
    FROM ps_tag ta2
    JOIN ps_product_tag pt2 ON pt2.id_tag = ta2.id_tag
    WHERE pt2.id_product = p.id_product
    AND ta2.id_lang = 1
  ) AS `Tags`,
  IFNULL(pl.meta_title, '') AS `Meta title`,
  IFNULL(pl.meta_keywords, '') AS `Meta keywords`,
  IFNULL(pl.meta_description, '') AS `Meta description`,
  pl.link_rewrite AS `URL rewritten`,
  IFNULL(pl.available_now, '') AS `Text when in stock`,
  IFNULL(pl.available_later, '') AS `Text when backorder allowed`,
  p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
  IFNULL(IF(p.available_date = '0000-00-00', '', p.available_date), '') AS `Product available date`,
  p.date_add AS `Product creation date`,
  p.show_price AS `Show price (0 = No, 1 = Yes)`,
  (
    SELECT IFNULL(
      GROUP_CONCAT(
        CONCAT(
          'https://my-prestashop.com/img/p/',
          CASE
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 1 THEN SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1)
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 2 THEN CONCAT(SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 2, 1))
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 3 THEN CONCAT(SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 2, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 3, 1))
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 4 THEN CONCAT(SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 2, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 3, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 4, 1))
              -- Add others WHEN to handke more lengths
          END,
          '/',
          im1.id_image,
          '.jpg'
        ) SEPARATOR ','
       ), ''
    )
    FROM ps_image im1
    WHERE im1.id_product = p.id_product
    ORDER BY im1.cover DESC, im1.position ASC
  ) AS `Image URLs (x,y,z...)`,
  (
    SELECT IFNULL(
      GROUP_CONCAT(il2.legend SEPARATOR ',')
    , '')
    FROM ps_image_lang il2
    JOIN ps_image im2 ON im2.id_image = il2.id_image
    WHERE im2.id_product = p.id_product
    AND il2.id_lang = 1
  ) AS `Image alt texts (x,y,z...)`,

  0 AS `Delete existing images (0 = No, 1 = Yes)`,

  (
    SELECT IFNULL(
      GROUP_CONCAT(
        CONCAT_WS(':', fl1.name, fvl1.value, f1.position)
        SEPARATOR ','
      ), ''
    )
    FROM ps_feature_product fp1
    JOIN ps_feature f1 ON fp1.id_feature = f1.id_feature
    JOIN ps_feature_lang fl1 ON fp1.id_feature = fl1.id_feature AND fl1.id_lang = 1
    JOIN ps_feature_value_lang fvl1 ON fp1.id_feature_value = fvl1.id_feature_value AND fvl1.id_lang = 1
    WHERE fp1.id_product = p.id_product
  ) AS `Feature(Name:Value:Position)`,
  p.online_only AS `Available online only (0 = No, 1 = Yes)`,
  p.condition AS `Condition`,
  p.customizable AS `Customizable (0 = No, 1 = Yes)`,
  p.uploadable_files AS `Uploadable files (0 = No, 1 = Yes)`,
  p.text_fields AS `Text fields (0 = No, 1 = Yes)`,
  p.out_of_stock AS `Out of stock action`,
  p.is_virtual AS `Virtual product`,
  IFNULL(pd.display_filename, '') AS `File URL`,
  IFNULL(pd.nb_downloadable, 0) AS `Number of allowed downloads`,
  IFNULL(pd.date_expiration, '') AS `Expiration date`,
  IFNULL(pd.nb_days_accessible, 0) AS `Number of days`,
  1 AS `ID / Name of shop`,
  p.advanced_stock_management AS `Advanced stock management`,
  sa.depends_on_stock AS `Depends On Stock`,
  IFNULL(st.id_warehouse, '') AS `Warehouse`,
  (
      SELECT IFNULL(
        GROUP_CONCAT(ac1.id_product_2 SEPARATOR ',')
        , ''
      )
      FROM ps_accessory ac1
      WHERE ac1.id_product_1 = p.id_product
  ) AS `Accessories (x,y,z...)`
FROM ps_product p
INNER JOIN ps_product_lang pl 
  ON p.id_product = pl.id_product 
  AND pl.id_lang = 1
LEFT JOIN ps_product_download pd
  ON pd.id_product = p.id_product
LEFT JOIN ps_stock_available sa 
  ON p.id_product = sa.id_product 
  AND sa.id_product_attribute = 0 
  AND sa.id_shop = 1
LEFT JOIN ps_manufacturer m 
  ON p.id_manufacturer = m.id_manufacturer
LEFT JOIN ps_image i 
  ON p.id_product = i.id_product 
  AND i.cover = 1
LEFT JOIN ps_category_lang cl 
  ON p.id_category_default = cl.id_category 
  AND cl.id_lang = 1
LEFT JOIN ps_supplier su 
  ON su.id_supplier = p.id_supplier
LEFT JOIN ps_stock st 
  ON st.id_product = p.id_product
-- WHERE p.price > 0

4-prestashop-combinations.sql

SELECT
  pas.id_product AS `Product ID*`,
  (
    SELECT GROUP_CONCAT(CONCAT_WS(':', agl1.name, atg1.group_type, atg1.position) SEPARATOR ', ')
    FROM ps_product_attribute_combination pac1
    INNER JOIN ps_attribute att1 ON att1.id_attribute = pac1.id_attribute
    INNER JOIN ps_attribute_group atg1 ON atg1.id_attribute_group = att1.id_attribute_group
    INNER JOIN ps_attribute_group_lang agl1 ON agl1.id_attribute_group = att1.id_attribute_group AND agl1.id_lang = 1
    WHERE pac1.id_product_attribute = pas.id_product_attribute
  ) AS `Attribute (Name:Type:Position)*`,
  (
    SELECT GROUP_CONCAT(CONCAT_WS(':', atl2.name, att2.position) SEPARATOR ', ')
    FROM ps_product_attribute_combination pac2
    INNER JOIN ps_attribute att2 ON att2.id_attribute = pac2.id_attribute
    INNER JOIN ps_attribute_lang atl2 ON atl2.id_attribute = pac2.id_attribute AND atl2.id_lang = 1
    WHERE pac2.id_product_attribute = pas.id_product_attribute
  ) AS `Value (Value:Position)*`,
  pa.supplier_reference AS `Supplier reference`,
  pa.reference AS `Reference`,
  pa.ean13 AS `EAN13`,
  pa.upc AS `UPC`,
  pas.wholesale_price AS `Wholesale price`,
  pas.price AS `Impact on price`,
  pas.ecotax AS `Ecotax`,
  pa.quantity AS `Quantity`,
  pas.minimal_quantity AS `Minimal quantity`,
  IFNULL(pas.low_stock_threshold, '') AS `Low stock level`,
  pas.weight AS `Impact on weight`,
  IFNULL(pas.default_on, 0) AS `Default (0 = No, 1 = Yes)`,
  IFNULL(IF(pas.available_date = '0000-00-00', '', pas.available_date), '') AS `Combination available date`,
  (
    SELECT IFNULL(GROUP_CONCAT(im3.position SEPARATOR ','), '')
    FROM ps_image im3
    INNER JOIN ps_product_attribute_image pai3 ON pai3.id_image = im3.id_image
    WHERE pai3.id_product_attribute = pas.id_product_attribute
    ORDER BY im3.position ASC
  ) AS `Image position`,
  (
    SELECT IFNULL(
      GROUP_CONCAT(
        CONCAT(
          'https://my-prestashop.com/img/p/',
          CASE
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 1 THEN SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1)
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 2 THEN CONCAT(SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 2, 1))
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 3 THEN CONCAT(SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 2, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 3, 1))
              WHEN LENGTH(CAST(im1.id_image AS CHAR)) = 4 THEN CONCAT(SUBSTRING(CAST(im1.id_image AS CHAR), 1, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 2, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 3, 1), '/', SUBSTRING(CAST(im1.id_image AS CHAR), 4, 1))
              -- Add others WHEN to handle more length
          END,
          '/',
          im1.id_image,
          '.jpg'
        ) SEPARATOR ','
       ), ''
    )
    FROM ps_image im1
    INNER JOIN ps_product_attribute_image pai1 ON pai1.id_image = im1.id_image
    WHERE pai1.id_product_attribute = pas.id_product_attribute
    ORDER BY im1.position ASC
  ) AS `Image URLs (x,y,z...)`,
  (
    SELECT IFNULL(GROUP_CONCAT(il2.legend SEPARATOR ','), '')
    FROM ps_image_lang il2
      INNER JOIN ps_image im2 ON im2.id_image = il2.id_image
      INNER JOIN ps_product_attribute_image pai1 ON pai1.id_image = il2.id_image
    WHERE pai1.id_product_attribute = pas.id_product_attribute
      AND il2.id_lang = 1
    ORDER BY im2.position ASC
  ) AS `Image alt texts (x,y,z...)`,
  1 AS `ID / Name of shop`,
  pr.advanced_stock_management AS `Advanced Stock Management`,
  sa.depends_on_stock AS `Depends on stock`,
  IFNULL(st.id_warehouse, '') AS `Warehouse`
FROM ps_product_attribute_shop pas
  INNER JOIN ps_product_attribute pa ON pas.id_product_attribute = pa.id_product_attribute
  INNER JOIN ps_product pr ON pr.id_product = pas.id_product
  LEFT JOIN ps_product_attribute_image pai ON pai.id_product_attribute = pas.id_product_attribute
  LEFT JOIN ps_image i ON pai.id_image = i.id_image
  LEFT JOIN ps_stock_available sa ON sa.id_product_attribute = pas.id_product_attribute AND sa.id_shop = 1
  LEFT JOIN ps_stock st ON st.id_product_attribute = pas.id_product_attribute
WHERE pas.id_shop = 1
GROUP BY pas.id_product_attribute;

4-prestashop-customer.sql

SELECT
   c.id_customer AS `Customer ID`,
   c.active AS `Active (0/1)`,
   IFNULL(c.id_gender, 0) AS `Gender ID`,
   c.email AS `Email`,
   c.passwd AS `Password`,
   c.birthday AS `Birthday (YYYY-MM-DD)`,
   c.lastname AS `Last name`,
   c.firstname AS `First name`,
   c.newsletter AS `Newsletter (0/1)`,
   c.optin AS `Optin (0/1)`,
   c.date_add AS `Registration date (YYYY-MM-DD)`,
   (
      SELECT GROUP_CONCAT(gl2.name SEPARATOR ',')
      FROM ps_customer_group cg2
      JOIN ps_group_lang gl2 
          ON cg2.id_group = gl2.id_group 
         AND gl2.id_lang = 1
      WHERE cg2.id_customer = c.id_customer
    ) AS `Group(s)`,
   c.id_default_group AS `Default group ID`
FROM ps_customer c;

5-prestashop-address.sql

SELECT
  a.id_address AS `Address ID`,
  a.alias AS `Alias`,
  a.active AS `Active (0/1)`,
  cu.email AS `Customer e-mail`,
  IFNULL(a.id_customer, '') AS `Customer ID`,
  IFNULL(ma.name, '')  AS `Manufacturer`,
  IFNULL(su.name, '') AS `Supplier`,
  a.company AS `Company`,
  a.lastname AS `Last name`,
  a.firstname AS `First name`,
  a.address1 AS `Address 1`,
  a.address2 AS `Address 2`,
  a.postcode AS `Zipcode`,
  a.city AS `City`,
  cl.name AS `Country`,
  IFNULL(s.name, '') AS `State`,
  a.other AS `Other`,
  a.phone AS `Phone`,
  a.phone_mobile AS `Mobile Phone`,
  a.vat_number AS `VAT number`,
  a.dni AS `DNI`
FROM ps_address a
  LEFT JOIN ps_customer cu ON cu.id_customer = a.id_customer
  LEFT JOIN ps_manufacturer ma ON ma.id_manufacturer = a.id_manufacturer
  LEFT JOIN ps_supplier su ON su.id_supplier = a.id_supplier
  LEFT JOIN ps_country_lang cl ON a.id_country = cl.id_country AND cl.id_lang = 1
  LEFT JOIN ps_state s ON a.id_state = s.id_state
WHERE cu.email IS NOT NULL;