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?
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It only takes a minute to sign up.
Sign up to join this communityMy 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?
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
.
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;