Updating product qty in Magento in an easier & faster way

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 of 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		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 _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!!

42 thoughts on “Updating product qty in Magento in an easier & faster way”

  1. 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

  2. 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();

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

  3. 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

  4. 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));
    }

  5. 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

  6. 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 ?

  7. 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.

  8. 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.

  9. 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

  10. 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

  11. 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.

  12. 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

  13. 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.

  14. 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

  15. 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.

  16. 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)

  17. 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

  18. 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

  19. 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.

  20. Worked great but when we updated to 1.9.3 it stopped changing the qty on the backend. It says no errors when loading the php but doesn’t change the stock levels now. Any help would be great. Thanks

Leave a Comment

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