Updating product qty in Magento in an easier & faster way

December 2, 2011  |  41 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

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

Steps:

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

2> Create a file: update_stocks.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 _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 _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));
}

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

	$sql			= "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
					   " . _getTableName('cataloginventory_stock_status') . " css
	                   SET
					   csi.qty = ?,
					   csi.is_in_stock = ?,
	                   css.qty = ?,
					   css.stock_status = ?
					   WHERE
					   csi.product_id = ?
			           AND csi.product_id = css.product_id";
	$isInStock		= $newQty > 0 ? 1 : 0;
	$stockStatus	= $newQty > 0 ? 1 : 0;
	$connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $productId));
}
/***************** UTILITY FUNCTIONS ********************/

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

$message = '';
$count   = 1;
foreach($data as $_data){
	if(_checkIfSkuExists($_data[0])){
		try{
			_updateStocks($_data);
			$message .= $count . '> Success:: Qty (' . $_data[1] . ') of Sku (' . $_data[0] . ') has been updated. <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: while Upating  Qty (' . $_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_stocks.php
You will see how fast the qty of corresponding skus are updated.

Thanks for reading guys!!

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
  • Andrew

    Can you use this to update just a few sku’s? or do you have to have all the sku’s in the import file?

    • Chris

      Yes you can use this code to update few skus just have the skus on the csv file

  • Dragos

    Doesnt work in magento 1.4.1.1 🙁

  • Chris

    Great work on this one. updates very fast

  • Mich

    Excellent with Magento 1.6.2.0
    Possible to change this script to mass update price ?

  • Howard

    Thank you for this. I need to update attribut field “cost”.
    I have 20,000 products that I exported from magento. and I want to copy price to cost. So that I can mark-up the cost later. It looks like this script will help me but not sure where “cost” field is and how to use it with this script.Any help is appreciated.
    Thanks

  • Im using 1.6.1 and your script seems to run perfectly and reports no errors but when i check the qty’s in magento nothing has changed. Am i missing something?

    Any help is greatly appreciated.

    Cheers,
    Matt

    • You need to rebuild Stock Status index from backend or programmatically:
      $stockIndex = Mage::getSingleton(‘index/indexer’)->getProcessByCode(‘cataloginventory_stock’);
      $stockIndex->reindexAll();

  • Andrew

    Does this work in 1.7.0 ? Getting ready to launch website and need to update qty levels. Thanks.

    • Andrew

      I tested and it works. It even lets you know if you have an item in your update file that is not on the website.

  • Hi there, the script is working like charm, only for the sku and qty, can you please help me to replace the qty by is_in_stock with the values 0 and 1 ?

    And please tell me if can i add the stock rebuild on the bottom of this script.

    Thank you very much…

    neocastelli

  • neocastelli

    Hi, any update? i want to edit this section to put the data in to the cataloginventory_stock_item

    function _getAttributeId($attribute_code = ‘qty’){
    $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));
    }

  • Natalie

    Hello,
    My magento – 1.5.1.0. My error – Parse error: syntax error, unexpected T_STRING in /home/kteluedu/domains/cosmeticall.com.ua/public_html/1510/update_prices.php on line 16
    Could you tell me the solution to this problem? Thank you in advance, Natalie

  • Yossi

    Doesnt work for me. It seems like the csv file is not read.

    I did a print_r($data) and i’m getting the following : Array()

    Is my CSV badly formatted ?

  • Tikkie

    Works awesome on magento 1.7! Thanks.
    Is it possible to load the csv from an external url? (I now need to download it manually from my supplier), just replace stocks.scv with the direct url doesn’t work.
    It would be great if somebody can give me some help with this.

  • Moose

    I simply cannot believe how much time this has saved. This little snipped is…. awesome! Thank you so much for posting. As per a previous comment, to be able to reference a web address and also to be able to change the out of stock in the inventory… would be the ultimate.

    Worked in 1.7.

    Moose.

  • Jim

    Worked beautifully in 1.7.0.2 just now. Thank you so very much!

  • Nice script – surely this wouldn’t take you long to make into a little module?

  • jazzmanq

    The script only worked for me the first time, now it says that the SKU doesn’t exist, please help! 🙁

  • Hi, this is the best way to update the price list by cron job, I need a small help, i need to update the price only for products that have Stock Management to 1 or Stock Status 1, how can i add this condition?

    Thanks

  • Fabrice Lacombe

    Good script!
    I have tested in my shop and it works well. But I have a question: what about reindexing? After running this script you need to rebuild the index of data and many people are saying that reindexing after an update will take substantially longer than the update itself. But I have been experimenting with using Store Manager for qunatity updates on my large catalog (up to 600K items). It is possible to update quantity, any other product info, add new products, increase product price on some percent on a fly. The minimum efforts are required from you since there is an option to rebuild catalog index automatically after import, so you won’t have to do re-index manually. And here is a link to download: http://www.magentocommerce.com/magento-connect/store-manager-for-magento.html

  • Thanks a lot, it works greate. Before it takes 40 minutes to updates throught admin – import, now its 20 seconds.
    I need to solve one more think.
    To generate stock quantity, I use my Accounting software. Each product have its own ID and SKU. ID is depending from source, for the same product = SKU i have more sources. ID is unique.
    If I use your script now, it makes errors, because my csv file contain same sku with diferent quantities, and therefore it rewrite products quantities and use only last quantity of the file.
    Is it possible to regenerate file first, and sumarize identical SKU???
    Thanks a lot.

  • Kenneth

    Hi, amazing scripts. But I have the following scenario:
    My stock are both selling in my real store and web store, and the qty is always “1”.
    When customer place an order from web store, the qty will update from “1” to “0” by magento for sure.
    Then I run the script, since my shop hasn’t create any invoice for this item, the item qty change from “0” to “1” by the script.
    Do you have any idea to modify the script to check if the item is sold from the web store?

    Thanks,

    Kenneth

  • Gordon

    used this script last year, it’s good.
    Now found this extension http://www.mageplace.com/administration/smart-stock-update.html
    thought it might be better for lazybones like I am

  • Anthon

    This seems to work but in reality nothing updates at all in the backend when run, even though its says Success?

    This is after reindexing etc etc.

    Any ideas where issues may lie?

    Seems a fantastic little script if t works right!

    Cheers.

  • hi,
    my csv file resides on different server.
    can you help me on this.
    i am getting this error when i set absolute path
    $data = $csv->getData(‘www.dev.raddyx.in/csv/Availability70D.csv’); //path to csv

    Uncaught exception ‘Exception’ with message ‘File “http://dev.raddyx.in/csv/Availability70D.csv” do not exists’ in /home/tooltrad/public_html/lib/Varien/File/Csv.php:8

    Thank you

  • Stefan

    Hello,
    this is a real great solution. It works fine with me but only with products that are enables. Products that are disabled their stock won’t get updated.
    Is this wanted? Would be great if these could get updated too.

  • Pingback: need to update an attribute via csv in magento | Technology & Programming()

  • 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 my suppliers “sku” with my “codeprod” attribute ? THANKSSS (zanikos11 on SKYPE)

  • Praveen Kumar Navik

    i got Fatal error: Call to a member function getData() on a non-object in /home/chezsoid/public_html/mahoganyusa.com/magento/update_stocks.php on line 77

  • Worked perfectly in 1.7.0.2 Thanks 🙂

  • Pharmokan

    RAJ IS THE BEST! LOVE YOU RAJ!

  • hagen.sal

    Thanks so much! It worked perfectly 🙂

  • you guys could follow this instruction magento tutorial for beginners . So now we have our profile created all we need to do is create our CSV. The CSV is pretty standard format. First of we have our SKU, and then followed by our image attributes.

  • jafar

    how to update the qty of simple products only?. the above scripts helped me to update all of the inventory

  • rana ahamed

    Fatal error: Uncaught exception ‘Exception’ with message ‘Warning: simplexml_load_string(): Entity: line 39: parser error : Opening and ending tag mismatch: entities line 35 and search_Resource in C:xampphtdocsmagentolibVarienSimplexmlConfig.php on line 510’ in C:xampphtdocsmagentoappcodecoreMageCorefunctions.php:245 Stack trace: #0 [internal function]: mageCoreErrorHandler(2, ‘simplexml_load_…’, ‘C:\xampp\htdocs…’, 510, Array) #1 C:xampphtdocsmagentolibVarienSimplexmlConfig.php(510): simplexml_load_string(‘loadString(‘loadFile(‘C:\xampp\htdocs…’) #4 C:xampphtdocsmagentoappcodecoreMageCoreModelConfig.php(318): Mage_Core_Model_Config->loadModulesConfiguration(Array, Object(Mage_Core_Model_Config)) #5 C:xampp in C:xampphtdocsmagentoappcodecoreMageCorefunctions.php on line 245
    ——————————————————————————————————-
    This lines of error is showing

  • REMU Oy

    Thanks for this script. Its working very well with our store. I have a question though. When stock qty is updated, some of our products have a bellow 0 qty those are to be preodred. However, when we update QTY using your script the Stock Availability is set to “out of stock” and the “add to car” button disappear. Is there something to add in your script to make “In stock” to all products? so we dont have to change status manually.

  • Om Dev

    Hello,
    is this possible in Magento2.we require this functionality in magento 2.1.0
    Thanks.