1

Can we export product with sku and url into csv in magento2?

Is there any query to get it quickly?

I am looking for query to get all sku and its url key or full product_url.

Please anyone help me. Thanks

3

I got reference for the answer from this answer and I add addition part to show url_key column, please try this sql query :

SELECT e.entity_id AS 'id',
       v1.value AS 'name',
       v0.value AS 'url_key',
       e.sku,
       d1.value AS 'price',
       t1.value AS 'short_description',
       t2.value AS 'description',
       v2.value AS 'image',
       v3.value AS 'thumbnail',
       mg.value AS 'media_gallery',
       cids.category_ids AS 'category_ids',
       cids.category_names AS 'category_names'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v0 ON e.entity_id = v0.entity_id
AND v0.store_id = 0
AND v0.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'url_key'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
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 

if you want get url_path that used on the frontend you can use this query :

SELECT e.entity_id AS 'id',
       v1.value AS 'name',
       v0.value AS 'url_key',
       e.sku,
       cids.category_ids AS 'category_ids',
       cids.category_names AS 'category_names',
       ur.request_path as 'path_name'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v0 ON e.entity_id = v0.entity_id
AND v0.store_id = 0
AND v0.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'url_key'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
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
  (SELECT product_id,
         c.category_id  AS category_ids,
         cv.value 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'))
  ) cids ON e.entity_id = cids.product_id
LEFT JOIN 
catalog_url_rewrite_product_category cur ON cur.product_id = cids.product_id AND cur.category_id = cids.category_ids
LEFT JOIN url_rewrite ur ON cur.url_rewrite_id = ur.url_rewrite_id

Hope this helps.

  • It gave me url_key with most of them as empty values, why it is like that? each and every product should have the url_key is it? but i got result for very less products. Can we get full product urls? – jafar pinjar Oct 11 '18 at 5:47
  • not all product, usually configurable product does not have url_key attribute value. magento do reindex and add rewrite the controller url that call the product and save it in url_rewrite table if it have url_key value then magento use url from that but if it does not have magento will generate based product name, I've update answer to get all frontend url path product. please check again – mrfizh Oct 11 '18 at 6:39
  • yes got it, but i do not have any configurable products, all are simple products only. for them also i am getting url_key as empty values – jafar pinjar Oct 11 '18 at 6:43
  • hmm, it's strange maybe someone forgot to insert url_key attribute value when import them. so magento only generate url for frontend based category and name of products – mrfizh Oct 11 '18 at 6:45
  • so can we update all url_key for the products using sku and url_key from the import? – jafar pinjar Oct 11 '18 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.