Updating product prices in Magento in an easier & faster way

November 4, 2011  |  56 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

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 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 <info@magepsycho.com>
 * @website		http://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 proudcts and 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, Sepcial, Tier & Group Prices)

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

Happy Importing!!

Posted in Latest, Magento, Mysql and tagged , , , , , , , , , , , . Bookmark the permalink.

About Raj (MagePsycho)

Raj, the guy behind MagePsycho is a Zend PHP5 Certified Engineer, Magento Certified Developer, Magento Moderator / Freelancer with specialization in web applications (CMS, E-Commerce, ERP etc.). Catch him on: Twitter: @magepsycho Skype: magentopycho
  • http://atwix.com Alex

    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

  • http://www.magepsycho.com MagePsycho

    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

  • Frank

    As usual Great work from the magento expert
    Keep it up

  • http://www.ezshop.ky Finley Josephs

    Hi,

    Very interested in this, did you ever sort out the re-indexing aspect of the program.

    • Magento Developer

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

  • http://www.cosset.ro cosset

    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!

  • http://www.supermarket.no emil

    Hi, I could try your excellent job, but how can I first export my Sku and Price to look like yours? Best regards

  • drooped

    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

  • http://www.royalthaisupermarket.co.uk/ Dan

    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?

  • http://www.royalthaisupermarket.co.uk/ Dan

    Here is a copy of the error:

    1> Error while Upating Price (2.29) of Sku (1)2> Error while Upating Price (1.09) of Sku (2)3>

    • http://www.magepsycho.com MagePsycho

      Hi Dan
      I have updated the code which will give you detailed error message if there is any.
      Also fixed for table with prefixes.
      Try once with the updated code above.

  • http://www.webcraft.be Michiel

    Thank you very much for your solution. It works like a charm!

  • helefa

    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 :)

    • Charl

      Thanks it works great!!

      How can I change the file to update the cost price?

      Thanks again!

  • Alexandru

    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.

  • Francisco

    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.

  • Francisco

    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

  • Ray matos

    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

    • Francisco

      there is a missing { or ; in your code, I also added the full path for the Mage.php file and the ?> at the end.

  • http://www.grupoveta.com Mauro

    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!

    • http://www.magepsycho.com MagePsycho

      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

  • Joe

    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!

  • http://www.sjlwebdesign.co.uk Sam

    You are a hero, this script has saved me so much time, I got it working with special prices too

    • Francisco

      Can you share the special price and dates? also if you have set to different stores can you share that too?

      Thank you

      • http://www.magepsycho.com MagePsycho

        FYI, The script for updating store wise special prices with from and to date using csv is available as paid script. You can contact for the script at:
        http://www.magepsycho.com/contacts

        Thanks

  • johan

    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

  • Allan Hansen

    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

    • Allan Hansen

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

      By commenting that line out, everything works fine.

  • erich

    Does this work for update quantity as well?

    • erich

      nvm. I found your article about it. Thanks!!

    • erich

      Is this possible to collaborate with Google doc?
      Therefore, I can update data in Google doc and set up Cron job that make it automatically.

      Thanks

  • http://www.zarzie.com.au ZarZie

    Opps you’re fantastic man, thanks a lot for this gold mine. it’s saves us heaps of time updating the prices.

    God bless you

  • http://www.magepsycho.com MagePsycho

    Announcement!
    We have developed a Magento Module for importing product prices like regular, special, tier and group price in faster and effective way with cool web interface. Check it out:
    Mass Importer Pro: Price Importer (Regular, Sepcial, Tier & Group Prices)

    Happy Importing!

  • http://www.onlinecabinets.net Billy

    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?

  • Mitchell

    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!

  • raheem

    you save my time.great article,thanks.

  • http://stationeryshop.in Muhammed

    This is really a good help script. I would use this once required.

  • Niklas Bauer

    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.

  • http://www.gudbuy.com Faiq Ali Khan

    I am getting error 500 . what should i do

  • http://www.loadmanager.com scott

    can any give me the update_prices.php? I created it but when I use the URL it just displays the content of the file as plain text.

  • John Brehman

    Hi! If you are a code noob, recommend to use ready-made extensions for the store. As for me i use this to make bulk changes http://amasty.com/mass-product-actions.html

  • Dakshika Jayathilaka

    i need to add cost into this update, is that posible?

  • Creative

    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

  • karthick

    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

  • Fred A. Spies

    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.

  • Mirko

    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.

  • http://www.magentocommerce.com/magento-connect/catalog/product/view/id/17639/s/bulk-update-all-product-prices-4483/ SaveTheMage

    You can use a free extension like http://www.magentocommerce.com/magento-connect/catalog/product/view/id/17639/s/bulk-update-all-product-prices-4483/ which allows you to bulk update all product prices.

  • https://www.houssehouse.com houssehouse

    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 http://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.

  • http://www.magentotaal.nl Luc

    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?

  • PR

    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));
    }
    
  • Pingback: Updating product prices in Magento in an easier & faster way | Slomazon.org – Brand, Design, Wordpress, Magento

  • Luciano Oliveira

    worked perfectly. but the price in the grid and list not updated. how do I update?

  • ZANIKOS

    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)

    • Sherly

      Hi, Zanikos,

      I have the same problem, could you solve?

  • Sarah Robertson

    I also need to change prices for many products from time to time, and I use a special extension which lets me do that – http://amasty.com/mass-product-actions.html. BTW, is has other great features like changing attribute sets.