Updating product prices in Magento in an easier & faster way

Introduction

Product prices can be updated via default import profile though but this is very slow and requires lots of CSV fields(besides sku & price) for updating.
Today I am going to talk about updating product prices just by using CSV with two fields: sku & price(new) which is very fast enough even for thousands of products.

Steps:

1> Prepare CSV file(prices.csv) with two fields: sku & price and upload in the root of Magento installation.
You can see the snapshot of how it should look like:

prices.csv
prices.csv

Note: Field/Values should be enclosed within double quotes(“) and separated by a comma(,). I would prefer OpenOffice for CSV formatting.

2> Create a file: update_prices.php in the root of Magento installation and paste the following code:


<?php
/**
 * @author		MagePsycho <[email protected]>
 * @website		https://www.magepsycho.com
 * @category	Export / Import
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

/***************** UTILITY FUNCTIONS ********************/
function _getConnection($type = 'core_read'){
	return Mage::getSingleton('core/resource')->getConnection($type);
}

function _getTableName($tableName){
	return Mage::getSingleton('core/resource')->getTableName($tableName);
}

function _getAttributeId($attribute_code = 'price'){
	$connection = _getConnection('core_read');
	$sql = "SELECT attribute_id
				FROM " . _getTableName('eav_attribute') . "
			WHERE
				entity_type_id = ?
				AND attribute_code = ?";
	$entity_type_id = _getEntityTypeId();
	return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}

function _getEntityTypeId($entity_type_code = 'catalog_product'){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
	return $connection->fetchOne($sql, array($entity_type_code));
}

function _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));

}

function _checkIfSkuExists($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . "	WHERE sku = ?";
	$count		= $connection->fetchOne($sql, array($sku));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _updatePrices($data){
	$connection		= _getConnection('core_write');
	$sku			= $data[0];
	$newPrice		= $data[1];
	$productId		= _getIdFromSku($sku);
	$attributeId	= _getAttributeId();

	$sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
				SET  cped.value = ?
			WHERE  cped.attribute_id = ?
			AND cped.entity_id = ?";
	$connection->query($sql, array($newPrice, $attributeId, $productId));
}
/***************** UTILITY FUNCTIONS ********************/

$csv				= new Varien_File_Csv();
$data				= $csv->getData('prices.csv'); //path to csv
array_shift($data);

$message = '';
$count   = 1;
foreach($data as $_data){
	if(_checkIfSkuExists($_data[0])){
		try{
			_updatePrices($_data);
			$message .= $count . '> Success:: While Updating Price (' . $_data[1] . ') of Sku (' . $_data[0] . '). <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: While Upating  Price (' . $_data[1] . ') of Sku (' . $_data[0] . ') => '.$e->getMessage().'<br />';
		}
	}else{
		$message .=  $count .'> Error:: Product with Sku (' . $_data[0] . ') does\'t exist.<br />';
	}
	$count++;
}
echo $message;

3> Open your browser and run the following URL:
http://your-magento-url/update_prices.php
You will see how fast the prices of corresponding SKUs are updated.
I have tested with 2000 products and the update was accomplished within seconds. Isn’t that fast enough?

EDIT
Recently we have developed an extension for importing all types of prices(regular, special, tier & group prices) via web-interface using the same raw SQL concept. Here goes the link for the extension:
Mass Importer Pro: Price Importer (Regular, Special, Tier & Group Prices)

Thanks for reading. Please share your success story with this code.

Happy Importing!!

ANNOUNCEMENT

We have released the fastest price importer extension for Magento 2 for updating any kinds of prices (regular, cost, MSRP/MAP, special, tier & customer group price) via different methods (web, CLI & cron jobs)

Go to M2 Extension Page

79 thoughts on “Updating product prices in Magento in an easier & faster way”

  1. Great post! I had the task in the past to update over 10,000 SKU records and it indeed taken quite a few hours (about 5 I think). Got a question though, what about reindexing? Wouldn’t be nice to run price reindex after update is complete?

    Also you badly need some sharing features in the blog, like twitter and google +1

    Regards,
    Alex

    Reply
  2. Thanks Alex for your suggestions. I will take a look on re-indexing and will update the blog article accordingly
    And regarding Social Bookmarking, it is in the to-do lists which will be deployed shortly.

    Regrads

    Reply
    • You can simply run re-indexing programatically as:
      Mage::getSingleton(‘index/indexer’)->getProcessByCode(‘catalog_product_price’)->reindexAll();

      Reply
  3. WELL DONE PEOPLE!!!!!!! thank you so so so so much for this! it’s great, i have an website with over 20k products and with the standard magento import tools it took me over 12 hours to update the stock and price, but with this…when the updating day is coming there are no worries 🙂 . THANK YOU!

    Reply
  4. Hi im Getting this error

    HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request.

    can you help me

    Reply
  5. Hi,

    I followed your instructions precisely, but when running the php file, it had all my products and new prices but an error next to each one.

    I’m using Magento 1.6.2 CE, any ideas what I’m doing wrong?

    Reply
  6. THIS IS THE COOLEST!
    You really saved me hours of work. We update prices all the time, and I edited this file to accomodate special_price instead of price and it worked like a charm. Since I was updating 3 different price fields, I had to redo it three times. Do you think it would be complicated to make this work on 3 columns of data?
    I absolutely love the error reporting, much better than doing this via import where you have no idea what items the errors were caused by and why they didn’t work.
    The reason I got to this page was because my magento import starting messing up my numbers when importing and adding on zeros and moving decimal places, no matter what format I tried uploading. I was going to do it manually because I had a deadline to meet and you’ve saved me. thank you thank you thank you 🙂

    Reply
  7. First of all thanks for this great script, it really speeds up price updating. I have a quick question – one of my clients is worried that our suppliers might sometimes send us wrong prices (let’s say that product X costs USD 100 and the next day something goes wrong and the suppliers’ feed sends us a price of USD 40 – there are no promotions or discounts). Is there any way to include some kind of validation in the script, meaning that if the new price for SKU X is lower or higher by let’s say 15% than the old price, the price won’t be updated and I will be given an error?
    Thanks in advance.

    Reply
  8. Is there a way to include promotion or discount price, (special_price, special_price_from and special_price_to) also if I have multiple sites with different prices CAD and USD, how can I change the store to be upload to?

    thank you.

    Reply
  9. By far the fastest code I’ve test, and it is instant!!! what took 6 hours to do with Magento Import/Export this code does it in about 40 seconds!!!

    It’s too good to be true!!! I’m working on adding the Special Price to the code and testing how to add the price to each store… but it is way too fast… GREAT JOB!!

    PD: I have tested so many different codes for updating inventory and price that this is just great… thank you again

    Reply
  10. Hey having an issue, i get

    Parse error: syntax error, unexpected T_STRING in /home/classich/public_html/update_prices.php on line 18

    I think its an issue with app/Mage.php

    I using the latest version of Magento 1.6.2.0.

    Thanks for your help

    Reply
  11. Hello!
    I speak from Argentina!
    Thank you very much for your help.
    I’m trying to update the preicos, but I can.
    See the error:
    http://www.grupoveta.com/update_prices.php

    Here I leave early, but I spend all records the same.

    1> with Sku :: Product Error (14080, 4250) does’t exist.
    2> Error with Sku :: Product (148, 6250) does’t exist.
    3> Error with Sku :: Product (147, 4590) does’t exist.
    4> Error with Sku :: Product (145, 2600) does’t exist.
    5> Error with Sku :: Product (144, 2700) does’t exist.
    6> Error with Sku :: Product (143, 3490) does’t exist.
    7> Error with Sku :: Product (142, 850) does’t exist.
    8> Error with Sku :: Product (141, 660) does’t exist.
    9> Error with Sku :: Product (140, 2450) does’t exist.

    Any idea because it will be?
    THANK YOU SO MUCH!

    Reply
    • This means your csv is not properly formatted.
      Note that Field/Values should be enclosed with double quotes(“) and separated by comma(,). I would prefer OpenOffice for csv formatting.

      Thanks

      Reply
  12. This is really great! Saved me loads of time!

    Can this be customised for any other fields i.e. adding bulk attributes to existing products?

    Could you make a tutoiral on how to customise 🙂

    Thank you again for this brilliant piece of information!

    Reply
  13. Hi,
    How can I update my pricelist when I have 5 stores with 5 different price.
    Can you please show an exampel?
    I have magento 1.62.00

    Take care

    Reply
  14. Hi, I’ve updated the script so it also updates stock qty.

    But when I run the script I get the follwing error:
    “Fatal error: Uncaught exception ‘Exception’ with message ‘Recoverable Error: Argument 1 passed to Mage_Core_Model_Store::setWebsite() must be an instance of Mage_Core_Model_Website, null given…”

    Am I supposed to set the store somewhere?

    Hope you can help.

    // Allan

    Reply
    • It seems it is this line that gives the error:
      Mage::setIsDeveloperMode(true);

      By commenting that line out, everything works fine.

      Reply
  15. I just ran the pricing script and it seems to have messed up my Cat price rule for wholesale pricing. It applies the discount to the old price from before the script. Any ideas?

    Reply
    • There is one another database table to update manually after you modify the `catalog_product_entity_decimal`, it is `catalog_product_index_price`. You will find there the `price`, `final_price`, `min_price`, `max_price` columns which have been updated for every product and each `customer_group_id`.

      Reply
  16. Hi There,

    Looking forward to getting this working but I have run into a speed bump!

    I followed your instructions (as much as I think I have) but when I go to run update_prices.php I am hit with the following error:

    Fatal error: Uncaught exception ‘Exception’ with message ‘Warning: set_time_limit() [function.set-time-limit]: Cannot set time limit in safe mode in /var/www/vhosts/himarkcomputers.com/httpdocs/update_prices.php on line 16′ in /var/www/vhosts/himarkcomputers.com/httpdocs/app/code/core/Mage/Core/functions.php:245 Stack trace: #0 [internal function]: mageCoreErrorHandler(2, ‘set_time_limit(…’, ‘/var/www/vhosts…’, 16, Array) #1 /var/www/vhosts/himarkcomputers.com/httpdocs/update_prices.php(16): set_time_limit(0) #2 {main} thrown in /var/www/vhosts/himarkcomputers.com/httpdocs/app/code/core/Mage/Core/functions.php on line 245

    Any help would be greatly appreciated!

    Reply
  17. This is a great detailed compilation of information, thanks!
    And your example of code worked for me well. The certain truth is that online performance is business success! At the same time the most efficient official backend data importer for your database is Store Manager desktop application that is perceptibly faster than the Dataflow. The time that it saves is exponentially incredible.

    Reply
  18. This is really a great solution.
    I have just one problem, when I execute I got a special price notion on the front-end and I just want to change the baseprice

    Reply
  19. i’m having this error.
    Please help me….

    Fatal error: Uncaught exception ‘Exception’ with message ‘File “Array” do not exists’ in E:\xampp\htdocs\karthick\herbalproducts\lib\Varien\File\Csv.php:89 Stack trace: #0 E:\xampp\htdocs\karthick\herbalproducts\update_price.php(79): Varien_File_Csv->getData(‘prices.csv’) #1 {main} thrown in E:\xampp\htdocs\karthick\herbalproducts\lib\Varien\File\Csv.php on line 89

    Reply
    • I was having the same issue and here’s how I solved it. The .php calls the prices.csv file but it has no clue where to find that file. Just had your absolute path in the .php script:


      /***************** UTILITY FUNCTIONS ********************/

      $csv = new Varien_File_Csv();
      $data = $csv->getData('/your/absolute/path/here/prices.csv'); //path to csv

      Reply
  20. Oh man , you’re the best! Thank you so so much!
    Actually im using your script to update prices using a webservice. Works like a charm.
    You saved me hours and hours of research about magento core.

    Reply
  21. Class Script!
    How about that I can change the price for a website! Have a multi-shop and would like to make only 9 of storeID change the prices.

    Reply
  22. Hi,

    I’ve tried to apply your method on magento product names, as I’ve been unable to change them since I’ve add them for the first time. The only way for me to force names to change is to change SKUs.

    Your php code is really great and fast worked for the prices.
    Maybe you can help me ? Once I run the code it seems to work perfectly but my products names become Blank on homepage and the former name still appear on product page.

    here is The modified code (Note : My csv file name is names.csv (two collumns “sku” and “name”. And the php file is update_prices.php.
    ———————————————-

    <?php
    /**
    * @author MagePsycho
    * @website https://www.magepsycho.com
    * @category Export / Import
    */
    $mageFilename = 'app/Mage.php';
    require_once $mageFilename;
    Mage::setIsDeveloperMode(true);
    ini_set('display_errors', 1);
    umask(0);
    Mage::app('admin');
    Mage::register('isSecureArea', 1);
    Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

    set_time_limit(0);
    ini_set('memory_limit','1024M');

    /***************** UTILITY FUNCTIONS ********************/
    function _getConnection($type = 'core_read'){
    return Mage::getSingleton('core/resource')->getConnection($type);
    }

    function _getTableName($tableName){
    return Mage::getSingleton('core/resource')->getTableName($tableName);
    }

    function _getAttributeId($attribute_code = 'name'){
    $connection = _getConnection('core_read');
    $sql = "SELECT attribute_id
    FROM " . _getTableName('eav_attribute') . "
    WHERE
    entity_type_id = ?
    AND attribute_code = ?";
    $entity_type_id = _getEntityTypeId();
    return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
    }

    function _getEntityTypeId($entity_type_code = 'catalog_product'){
    $connection = _getConnection('core_read');
    $sql = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
    return $connection->fetchOne($sql, array($entity_type_code));
    }

    function _getIdFromSku($sku){
    $connection = _getConnection('core_read');
    $sql = "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
    return $connection->fetchOne($sql, array($sku));

    }

    function _checkIfSkuExists($sku){
    $connection = _getConnection('core_read');
    $sql = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
    $count = $connection->fetchOne($sql, array($sku));
    if($count > 0){
    return true;
    }else{
    return false;
    }
    }

    function _updateNames($data){
    $connection = _getConnection('core_write');
    $sku = $data[0];
    $newPrice = $data[1];
    $productId = _getIdFromSku($sku);
    $attributeId = _getAttributeId();

    $sql = "UPDATE " . _getTableName('catalog_product_entity_varchar') . " cped
    SET cped.value = ?
    WHERE cped.attribute_id = ?
    AND cped.entity_id = ?";
    $connection->query($sql, array($newName, $attributeId, $productId));
    }
    /***************** UTILITY FUNCTIONS ********************/

    $csv = new Varien_File_Csv();
    $data = $csv->getData('names.csv'); //path to csv
    array_shift($data);

    $message = '';
    $count = 1;
    foreach($data as $_data){
    if(_checkIfSkuExists($_data[0])){
    try{
    _updateNames($_data);
    $message .= $count . '> Success:: While Updating Name (' . $_data[1] . ') of Sku (' . $_data[0] . '). ';

    }catch(Exception $e){
    $message .= $count .'> Error:: While Upating Name (' . $_data[1] . ') of Sku (' . $_data[0] . ') => '.$e->getMessage().'';
    }
    }else{
    $message .= $count .'> Error:: Product with Sku (' . $_data[0] . ') does\'t exist.';
    }
    $count++;
    }
    echo $message;

    —————————————-
    Thank you so much.

    Reply
  23. In a configurable product I need to update also the price of associated products. Can you please point into the right direction? In what table are these values stored?

    Reply
  24. Thx buddy this is help for me if you want update prece per website replace this function and ass field store_id in csv

    
    function _updatePrices($data){
        $connection     = _getConnection('core_write');
        $sku            = $data[0];
        $newPrice       = $data[1];
        $storeId        = $data[2];
        $productId      = _getIdFromSku($sku);
        $attributeId    = _getAttributeId();
     
        $sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
                    SET  cped.value = ?
                WHERE  cped.attribute_id = ?
                AND cped.entity_id = ?
                AND store_id = ?";
        $connection-&gt;query($sql, array($newPrice, $attributeId, $productId, $storeId));
    }
    
    Reply
  25. Hi freinds,

    I named my products with internal SKU’s, and created an attribute for
    supplier’s original Sku’s named “codeprod”. I tried to replace all
    “sku” with “codeprod” but the update doesn’t work. What should I do so I
    can update new prices from my suppliers “sku” with my “codeprod” attribute ? THANKSSS
    (zanikos11 on SKYPE)

    Reply
  26. Hi,
    this is my website(http://www.stylehoops.com) while updating special price it’s working absolutely fine without issues. but my query is i want to update the bulk attribute values. “discount” is my attribute code. is there any way to do the bulk discount option in magento.

    any one suggest me how to do this bulk discount values.
    Plz plz…
    Best regards,
    Pradeep. B

    Reply
  27. I’ve been using this script forever. I’ve since switched to Magento 2 but it’s not the same structure. How are you doing it now? Thanks! 🙂

    Reply
  28. Beautiful and useful script!
    Can you use it to edit other fields, such as “Name” and / or “Description”, etc. … of products?
    Thank you

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.