Magento Import product reviews from a csv

Question

I am trying to import magento product reviews form a csv using the following script but it seem not to be working. When I run the script I get a blank page and nothing is imported into the database although the existing ratings are removed.

please what am I doing wrong ?

// Invoke the Magento environment
require_once( 'app/Mage.php' );
Mage::app();

$_read = Mage::getSingleton('core/resource')->getConnection('core_read');
$_write = Mage::getSingleton('core/resource')->getConnection('core_write');

// Remove existing ratings
$_write->query("SET FOREIGN_KEY_CHECKS = 0;");
$_write->query("TRUNCATE TABLE rating_option_vote;");
$_write->query("TRUNCATE TABLE review_detail;");
$_write->query("TRUNCATE TABLE review_entity_summary;");
$_write->query("TRUNCATE TABLE review_store;");
$_write->query("TRUNCATE TABLE review;");
$_write->query("TRUNCATE TABLE rating_option_vote_aggregated;");
$_write->query("SET FOREIGN_KEY_CHECKS = 1;");

$file_handle = fopen('reviews-data.csv', 'r');
$i = 0;
while( $csv_line = fgetcsv($file_handle) ){
  if( !is_array($csv_line) ) {
    break;
  }
  //if( $i > 50 ) break;
  $i++;
  // Define and clean variables
  $product_id = trim($csv_line[0]);
  $sku = trim($csv_line[1]);
  $product_name = ucwords( strtolower( trim( $csv_line[2] ) ) );
  $rating_value = (int)$csv_line[3];
  if( empty( $rating_value ) ){
    continue;
  }
  $details = trim( $csv_line[4] );
  $first_name = ucwords( strtolower( trim( $csv_line[5] ) ) );
  $last_name = ucwords( strtolower( trim( $csv_line[6] ) ) );
  // Create a single name
  $nickname = '';
  if( !empty($first_name) ){
    $nickname .= $first_name;
  }
  if( !empty($last_name) ){
    if( !empty($nickname) ){
      $nickname .= ' ';
    }
    $nickname .= $last_name;
  }
  if( empty($nickname) ){
    $nickname = 'Guest';
  }

  $email = strtolower( trim( $csv_line[7] ) );
  if( !$created = strtotime( trim($csv_line[8]) ) ){
    $created = time();  
  }

  $status = (int)$csv_line[9];
  if( $status == 1 ){
    $approval_status = 1;
  } else {
    $approval_status = 3;
  }

  // Customer setup;
  $_customer = Mage::helper('custom')->getCustomerByEmail($email); // You'll need to write this logic yourself

  // IMPORTANT: Set up customer session.
  // the rating/option model resource checks the customer session to get the customer ID.
  $_session = Mage::getSingleton('customer/session')->setCustomer($_customer)->setCustomerAsLoggedIn($_customer);

  $_product = _getReviewProduct($sku); // You'll need to write this logic yourself

  if(!$_product) {
    print '<p>Unmatched SKU: ' . $sku . '</p>';
    continue;
  }

  // Add the review
  $_review = Mage::getModel('review/review')
    ->setEntityPkValue($_product->getId())
    ->setStatusId($approval_status)
    // ->setCreatedAt( date('Y-m-d H:m:s', $created) ) // Has no effect
    ->setTitle($product_name)
    ->setDetail($details)
    ->setEntityId(1)
    ->setStoreId(1)
    ->setStores(array(1,0))
    ->setCustomerId($_customer->getId())
    ->setNickname($nickname)
    ->save();
  // Set the created date
  $_write->query("UPDATE review SET created_at = '" . date('Y-m-d H:m:s', $created) .  "' WHERE review_id = " . $_review->getId());
  $_review->aggregate();

  // Map your rating_id to your option_id with an array or something
  $rating_options = array(
      //1 => array(1,2,3,4,5), // <== Look at your database table `rating_option` for these vals
      //2 => array(6,7,8,9,10),
      //3 => array(11,12,13,14,15),
      4 => array(16,17,18,19,20),
  );

  // Now save the ratings
  foreach($rating_options as $rating_id => $option_ids):
    try {
      $_rating = Mage::getModel('rating/rating')
      ->setRatingId($rating_id)
      ->setReviewId($_review->getId())
      ->addOptionVote( $option_ids[$rating_value-1],$_product->getId() );
    } catch (Exception $e) {
      print_r($e);
      die($e->getMessage());
    }
  endforeach;
}

function _getReviewProduct( $sku ){
  static $cache = array();

  if( !array_key_exists($sku, $cache) ){
    $cache[$sku] = NULL;
     // You'll need to write this logic yourself
    }
  }

  return $cache[$sku];
}

TBI Infotech · Accepted Answer · 2014-06-10 13:17:38Z

require_once 'app/Mage.php';
Mage::app();

// IMPORTANT: Set up customer session.

// the rating/option model resource checks the customer session to get the customer ID.

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

// Add Review

$_review = Mage::getModel('review/review');
->setEntityPkValue($_product->getId())
->setStatusId($sc_to_mage_review_status[$row_source_review['Status']])
->setTitle($row_source_review['Title'])
->setDetail($row_source_review['Review'])
->setEntityId(1)
->setStoreId($store)
->setStores(array($store))
->setCustomerId($_customer->getId())
->setNickname($_customer->getFirstname())
->save();

Probable Error within these:

Check last comma of the array

$rating_options = array(
1 => array(1,2,3,4,5), // <== Look at your database table `rating_option` for these vals
2 => array(6,7,8,9,10),
3 => array(11,12,13,14,15)
);

Probable Error within these:

foreach($rating_options as $rating_id => $option_ids):
try {
    $_rating = Mage::getModel('rating/rating')
        ->setRatingId($rating_id)
        ->setReviewId($_review->getId())
        ->addOptionVote($option_ids[$rating_value-1],$_product->getId());
} catch (Exception $e) {
    die($e->getMessage());
}
endforeach;

SoftProdigy · Accepted Answer · 2014-06-10 12:41:14Z

Please use following code to import the product reviews:

<?php 
ini_set('memory_limit', '128M'); 
require_once 'path-to-Mage.php'; 
Mage::app(); 
$fp = fopen($fileLocation, 'r');
Mage::app()->setCurrentStore(4); //desired store id 
while($line = fgetcsv($fp)) {
     $review = Mage::getModel('review/review'); 
  $review->setEntityPkValue($line[0]);//product id 
  $review->setStatusId($line[1]); 
  $review->setTitle($line[2]);  
  $review->setDetail($line[3]); 
  $review->setEntityId($line[4]);                                       
  $review->setStoreId(Mage::app()->getStore()->getId());                      
  $review->setStatusId($line[5]); //approved 
  $review->setCustomerId($line[6]);//null is for administrator 
  $review->setNickname($line[7]); 
  $review->setReviewId($review->getId()); 
  $review->setStores(array(Mage::app()->getStore()->getId()));                     
  $review->save(); 
  $review->aggregate(); 
}
?>

Thomas Jackson · Accepted Answer · 2018-04-29 16:52:13Z

How to Import Magento Product Reviews & Ratings From CSV

I am practically using the same code found in the frontend controller post action.

html/app/code/core/Mage/Review/controllers/ProductController.php (Lines: 174-218)

CSV Format

Sku
Rating
Review Title
Customer Email (null if guest)
Review Detail

sku1,5,My Favorite Product!,[email protected],"This was the best product ever!"

PHP Script

Things to note:

Both the php script and csv file should be in the root directory.
Rating values must be mapped to database values. The rating I use has an ID of 4. Default Magento ratings are 1, 2, 3. If you have a review with multiple ratings, create a mapping for each and loop through each rating after first creating the review.

require_once 'app/Mage.php';  // Load Mage

Mage::app(); // Init Mage

$fp = fopen('reviews.csv', 'r'); // Open CSV

while ($line = fgetcsv($fp)) { // Loop CSV lines

$customer = Mage::getModel('customer/customer')->setWebsiteId(1)->loadByEmail($line[4]); // Load customer by email - Set website
$customerId = $customer->getId(); // Save Customer ID for Later

$product = Mage::getModel('catalog/product')->loadByAttribute('sku', $line[0]); // Load product by SKU

if (!$product->getId()) { // Skip review if roduct is not found. Output line
    var_dump($line);
    continue;
}

$ratingValues = array( // Create ratings map
    1 => 16, // 1 Star
    2 => 17, // 2 Star
    3 => 18, // 3 Star
    4 => 19, // 4 Star
    5 => 20 // 5 Star
);

$review = Mage::getModel('review/review'); // Get review model
$review->setData(array( // Set Review Data
    'nickname' => $line[3],
    'title' => $line[2],
    'detail' => $line[5]
));

try { 
    $review->setEntityId(1) // Review is for a product
        ->setEntityPkValue($product->getId()) // Relate to product
        ->setStatusId(Mage_Review_Model_Review::STATUS_APPROVED) // Status is approved
        ->setCustomerId($customer->getId()) // Set customer id... null is for guest
        ->setStoreId(1) // Set website
        ->setStores(array(1)) // Website again
        ->save(); // Pray and save
} catch (Exception $exception) {
    Mage::log($exception->getMessage());
}

if ($review) { // If review was created
// Loop here if you have multiple rating for review
    try {
        Mage::getModel('rating/rating')
            ->setRatingId(4) // My custom rating ID
            ->setReviewId($review->getId()) // Use id of rating just created
            ->setCustomerId($customer->getId()) // Associate customer
            ->addOptionVote($ratingValues[$line[1]], $product->getId()); // Use rating mapping above to get database friendly value

        $review->aggregate(); // Not sure why... but it's needed
    } catch (Exception $exception) {
        Mage::log($exception->getMessage());
    }
}
}

MagePsycho · Accepted Answer · 2019-08-03 09:08:05Z

If you are familiar with Magento DB structure for reviews, I would recommend using raw SQL.
For example:

-- Edit values
SET @PRODUCT_ID         = 123;
SET @STORE_ID           = 1;
SET @CUSTOMER_ID        = NULL;
SET @REVIEW_TITLE       = 'Lorem Ipsum';
SET @REVIEW_DETAIL      = 'Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit...';
SET @REVIEW_RATING      = 5; -- Between 1 to 5
SET @REVIEW_NICKNAME    = 'John Doe';
SET @REVIEW_CREATED_AT  = '2019-07-15'; -- OR date in YY-mm-dd HH:ii:ss format

-- No need to Edit
SET @REVIEW_ENTITY_ID   = (SELECT entity_id FROM rating_entity WHERE entity_code = 'product'); -- 1: product, ...
SET @REVIEW_STATUS_ID   = (SELECT status_id FROM review_status WHERE status_code = 'Pending'); -- 1: Approved, 2: Pending, 3: Not Approved

INSERT INTO review SET created_at = @REVIEW_CREATED_AT, entity_id = @REVIEW_ENTITY_ID, entity_pk_value = @PRODUCT_ID, status_id = @REVIEW_STATUS_ID;
SET @REVIEW_ID = (SELECT LAST_INSERT_ID());
INSERT INTO review_detail SET review_id = @REVIEW_ID, store_id = @STORE_ID, title = @REVIEW_TITLE, detail = @REVIEW_DETAIL, nickname = @REVIEW_NICKNAME, customer_id = @CUSTOMER_ID;
INSERT INTO review_store SET review_id = @REVIEW_ID, store_id = 0;
INSERT INTO review_store SET review_id = @REVIEW_ID, store_id = @STORE_ID;
INSERT INTO rating_option_vote SET option_id = 5, remote_ip = '', remote_ip_long = 0, customer_id = @CUSTOMER_ID, entity_pk_value = @PRODUCT_ID, rating_id = @REVIEW_ENTITY_ID,
review_id = @REVIEW_ID, percent = 100, value = @REVIEW_RATING;

For details on how to "Import Product Reviews in Magento via SQL", please visit
https://blog.magepsycho.com/import-product-reviews-in-magento-via-sql/

Stack Exchange Network

Magento Import product reviews from a csv

4 Answers 4

How to Import Magento Product Reviews & Ratings From CSV

CSV Format

PHP Script

Your Answer

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

Hot Network Questions

Magento Import product reviews from a csv

4 Answers 4

How to Import Magento Product Reviews & Ratings From CSV

CSV Format

PHP Script

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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