How to export multistore product data from DB?

Question

My client wants to have a CSV file containing three columns, the product SKU, website ID (1 = CA, 2 = US) and the final price.

How could I do it?

Answer 1

You just need to run the query below to have the data, it will execute an INNER JOIN in the tables catalog_product_index_price and catalog_product_entity.

Export product data via SQL

SELECT catalog_product_entity.sku, catalog_product_index_price.website_id, catalog_product_index_price.final_price 
FROM catalog_product_index_price 
INNER JOIN catalog_product_entity 
ON catalog_product_index_price.entity_id=catalog_product_entity.entity_id; 

☝️ You can use the same query above to export to a CSV file called products-sku-price.csv running this command below. Don't forget to change the MySQL accesses.

mysql -h'my_sql_host' -u'my_user' --password='my_secure_password' 'my_magento_db' \
--default-character-set=utf8mb4 --batch --raw -e \
"SELECT catalog_product_entity.sku, catalog_product_index_price.website_id, catalog_product_index_price.final_price FROM catalog_product_index_price INNER JOIN catalog_product_entity ON catalog_product_index_price.entity_id=catalog_product_entity.entity_id" | \
tr '\t' ',' > $(pwd)/products-sku-price.csv;