Export Magento products & orders

To export order data from your online store Magento version 2.X.X, ask your IT department or your online store developer to run the following SQL query on the database:

SELECT order_item.created_at AS 'Timestamp',
       order_item.product_id AS 'Item_ID',
       order_item.order_id AS 'Session_ID',
       REPLACE(order_item.price_incl_tax, ',', '.') AS 'Purchase_Price',
       NULL AS 'Parent_Click_ID',
       NULL AS 'Online',
       CASE
           WHEN sales_order.customer_is_guest ='1' THEN 'GUEST_ORDER'
           ELSE sales_order.customer_id
       END AS 'User_ID',
       NULL AS 'Purchase_Group_ID',
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Cust_ID',
       REGEXP_REPLACE (order_item.NAME,
                       '<.+?>',
                       '') AS 'Name'
FROM sales_order_item AS order_item
LEFT JOIN sales_order AS sales_order ON order_item.order_id = sales_order.entity_id

After running the SQL query please save the rows returned from the database in CSV format.

To export product data from your online store Magento version 2.X.X, ask your IT department or your online store developer to run the following SQL query on the database:

SELECT e.entity_id AS Product_ID,
       e.sku AS Code,
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Customer_ID',
       'fdcbb3c07c17ce1e832b852eebdf4fa0' AS 'Category_ID',
       v1.value AS 'Name',
       NULL AS 'Description'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0
AND v1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'name'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0
AND t1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'short_description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0
AND t2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0
AND v2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'image'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0
AND v3.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'thumbnail'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0
AND d1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'price'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
  (SELECT m1.entity_id,
          GROUP_CONCAT(m2.value) AS value
   FROM catalog_product_entity_media_gallery_value_to_entity m1
   INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
   AND m2.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'media_gallery'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_product'))
   GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN
  (SELECT product_id,
          GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
   AND cv.store_id = 0
   AND cv.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'name'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_category'))
   GROUP BY product_id) cids ON e.entity_id = cids.product_id;

After running the SQL query please save the rows returned from the database in CSV format.

Finally, read the following guide for the steps you need to take to upload your product & order data.