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

prestashop-export-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;

prestashop-export-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;

prestashop-export-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;

prestashop-export-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;

prestashop-export-products.sql

SELECT 
  p.id_product AS `Product ID`,
  p.active AS `Active (0/1)`,
  pl.name AS `Name`,
  (
    SELECT 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 #`,
  p.supplier_reference AS `Supplier reference #`,
  su.name AS `Supplier`,
  m.name AS `Manufacturer`,
  p.ean13 AS `EAN13`,
  p.upc AS `UPC`,
  p.ecotax AS `Ecotax`,
  p.width AS `Width`,
  p.height AS `Height`,
  p.depth AS `Depth`,
  p.weight AS `Weight`,
  pl.delivery_in_stock AS `Delivery time of in-stock products`,
  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`,
  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`,
  p.unity AS `Unity`,
  p.unit_price_ratio AS `Unit price`,
  pl.description_short AS `Summary`,
  pl.description AS `Description`,

  '' AS `Tags`,
  
  pl.meta_title AS `Meta title`,
  pl.meta_keywords AS `Meta keywords`,
  pl.meta_description AS `Meta description`,
  pl.link_rewrite AS `URL rewritten`,
  pl.available_now AS `Text when in stock`,
  pl.available_later AS `Text when backorder allowed`,
  p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
  p.available_date AS `Product available date`,
  p.date_add AS `Product creation date`,
  p.show_price AS `Show price (0 = No, 1 = Yes)`,

  CONCAT('https://www.my-prestashop.com/img/p/', i.id_image, '-', p.id_product, '.jpg') AS `Image URLs (x,y,z...)`,
  CONCAT('https://www.my-prestashop.com/img/p/', i.id_image, '-', p.id_product, '.jpg') AS `Image alt texts (x,y,z...)`,

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

  '' 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`,
  pd.display_filename AS `File URL`,
  pd.nb_downloadable AS `Number of allowed downloads`,
  pd.date_expiration AS `Expiration date`,
  pd.nb_days_accessible AS `Number of days`,
  0 AS `ID / Name of shop`,
  p.advanced_stock_management AS `Advanced stock management`,
  0 AS `Depends On Stock`,
  0 AS `Warehouse`,
  '' AS `Acessories  (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