1

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];
}
3
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;
|improve this answer|||||
0

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(); 
}
?>
|improve this answer|||||
0

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

  1. Sku
  2. Rating
  3. Review Title
  4. Customer Email (null if guest)
  5. Review Detail

    sku1,5,My Favorite Product!,customer@email.com,"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.

Database friendly rating values

My custom rating used in this import

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());
    }
}
}
|improve this answer|||||
0

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/

|improve this answer|||||

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.