Export and Import product reviews in Magento 1

README.md

Export and Import product reviews in Magento 1

Based on script by "Magento Craftsman in Melbourne": https://blog.mdnsolutions.com/migrate-products-reviews-and-ratings-programmatically/

The steps below will export product reviews at "source" server and import the same reviews into magento at "destination" server.

1. Connect to the "source" server. (If your server allows remote mysql connections you can do the steps 2-6 from local environment.)

2. Copy export-reviews.sql to home directory.

3. Export reviews to ~/reviews.csv file:

   mysql db -u root --password=123 < ~/export-reviews.sql | sed "s/\"/\"\"/;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/" > ~/reviews.csv

4. Open ~/reviews.csv and check if everything is fine.

5. Change sku's to match sku's at "destination" server.

6. Take a look at the column titles in ~/reviews.csv. Make sure that __x__ in rating__x__value column names matches destination rating ids.

7. Save changes to ~/reviews.csv

8. Connect to the "destination" server.

9. Copy import-reviews.php to MAGENTO_ROOT/shell.

10. Copy ~/reviews.csv to MAGENTO_ROOT/shell folder and run the following command:

    php -f import-reviews.php -- -file reviews.csv

export-reviews.sql

select
 catalog_product_entity.sku,
 review_detail.store_id,
 review.status_id,
 customer_entity.email,
 review.created_at,
 review_detail.title,
 REPLACE(REPLACE(review_detail.detail, '\r', ''), '\n', '<br>') as detail,
 review_detail.nickname,
 IFNULL(rov1.value, 0) as rating__1__value,
 IFNULL(rov2.value, 0) as rating__2__value,
 IFNULL(rov3.value, 0) as rating__3__value
from review
inner join review_detail on review.review_id=review_detail.review_id
left join customer_entity on review_detail.customer_id=customer_entity.entity_id
inner join catalog_product_entity on review.entity_pk_value=catalog_product_entity.entity_id
left join rating_option_vote rov1 on rov1.review_id=review.review_id and rov1.rating_id=1
left join rating_option_vote rov2 on rov2.review_id=review.review_id and rov2.rating_id=2
left join rating_option_vote rov3 on rov3.review_id=review.review_id and rov3.rating_id=3
order by sku, created_at;

import-reviews.php

<?php

require_once 'abstract.php';

class Mage_Shell_Import_Reviews extends Mage_Shell_Abstract
{
    public function run()
    {
        if (!$this->getArg('file')) {
            die('Usage: php -f review_import.php -- -file /path/to/file.csv');
        }

        $product = false;
        $ratings = $this->getRatings();
        $data = $this->getData();

        foreach ($data as $row) {
            if (!$product || $product->getSku() !== $row['sku']) {
                $product = Mage::getModel('catalog/product')->loadByAttribute('sku', $row['sku']);
            }

            if (!$product) {
                continue;
            }

            $store = Mage::getModel('core/store')->load($row['store_id']);
            $customer = Mage::getModel('customer/customer')
                ->setWebsiteId($store->getWebsiteId())
                ->loadByEmail($row['email']);

            Mage::getSingleton('customer/session')
                ->setCustomer($customer)
                ->setCustomerAsLoggedIn($customer);

            $review = Mage::getModel('review/review')
                ->setEntityPkValue($product->getId())
                ->setStatusId($row['status_id'])
                ->setTitle($row['title'])
                ->setDetail(str_replace('<br>', "\n", $row['detail']))
                ->setEntityId(1)
                ->setStoreId($row['store_id'])
                ->setStores(array($row['store_id']))
                ->setCustomerId($customer->getId())
                ->setNickname($row['nickname'])
                ->save();

            foreach ($ratings as $ratingId => $options) {
                $ratingValue = $row["rating__{$ratingId}__value"];

                if (!isset($options[$ratingValue])) {
                    continue;
                }

                Mage::getModel('rating/rating')
                    ->setRatingId($ratingId)
                    ->setReviewId($review->getId())
                    ->addOptionVote($options[$ratingValue], $product->getId());
            }

            $review->aggregate();
            $review->setCreatedAt($row['created_at']);
            $review->save();
        }
    }

    private function getData()
    {
        $csv = new Varien_File_Csv;
        $data = $csv->getData($this->getArg('file'));
        $headers = $data[0];
        unset($data[0]);
        foreach ($data as $key => $row) {
            $data[$key] = array_combine($headers, $row);
        }
        return array_values($data);
    }

    private function getRatings()
    {
        $ratings = [];
        $options = Mage::getModel('rating/rating_option')->getCollection();
        foreach ($options as $option) {
            $ratings[$option->getRatingId()][$option->getValue()] =
                $option->getOptionId();
        }
        return $ratings;
    }
}

$app = new Mage_Shell_Import_Reviews();
$app->run();

Verify Github on Galaxy. gid:v96FAFMzxhDnRnCamV5epX