Mass update stock levels in Magento – FAST

This isn’t ground-breaking code, but rather just using some core code in a rather stripped out manner. As we’re not big fans of Magento data flow here, we perform most of our stock and catalogue updates using external scripts to Mage::app. We have been trickling elements of these out to the wider world so you can also benefit from them.

Managing large catalogues and updating stock levels regularly can be mission impossible in Magento, but not any more. With this script – we processed 120,000 stock updates in the blink of an eye. Next time we’ll teach you how to import products at 0.7 seconds each …

1

Create a CSV with a minimum of 2 columns, the SKU and any of the following,

qty
min_qty
use_config_min_qty
is_qty_decimal
backorders
use_config_backorders
min_sale_qty
use_config_min_sale_qty
max_sale_qty
use_config_max_sale_qty
is_in_stock
use_config_notify_stock_qty
manage_stock
use_config_manage_stock
stock_status_changed_automatically
type_id

Then save it to ./app/var/import/updateStockLevels.csv. For examples sake, we will use,

"sku","qty"
"prod1","11"

2

Copy the code below into a new file, ./quick_updateStock.php

<?
 
 define('MAGENTO', realpath(dirname(__FILE__)));
 require_once MAGENTO . '/app/Mage.php';
 
 umask(0);
 Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
 $count = 0;
 
 $file = fopen(MAGENTO . '/var/import/updateStockLevels.csv', 'r');
 while (($line = fgetcsv($file)) !== FALSE) { 
 
 if ($count == 0) {
 foreach ($line as $key=>$value) {
 $cols[$value] = $key;
 } 
 } 
 
 $count++;
 
 if ($count == 1) continue;
 
 #Convert the lines to cols 
 if ($count > 0) { 
 foreach($cols as $col=>$value) {
 unset(${$col});
 ${$col} = $line[$value];
 } 
 }
 
 // Check if SKU exists
 $product = Mage::getModel('catalog/product')->loadByAttribute('sku',$sku); 
 
 if ( $product ) {
 
 $productId = $product->getIdBySku($sku);
 $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
 $stockItemId = $stockItem->getId();
 $stock = array();
 
 if (!$stockItemId) {
 $stockItem->setData('product_id', $product->getId());
 $stockItem->setData('stock_id', 1); 
 } else {
 $stock = $stockItem->getData();
 }
 
 foreach($cols as $col=>$value) {
 $stock[$col] = $line[$value];
 } 
 
 foreach($stock as $field => $value) {
 $stockItem->setData($field, $value?$value:0);
 }
 
 
 
 $stockItem->save();
 
 unset($stockItem);
 unset($product);
 }
 
 echo "<br />Stock updated $sku";
 
 }
 fclose($file);
 
?>

3

Visit the file above in your browser, or via command line. It runs very quickly (approx 0.03 seconds per product), so don’t be too surprised if its over quickly!

Last 5 posts in Magento

Share and Enjoy:

This entry was posted on Sunday, January 31st, 2010 at 6:54 pm and is filed under Magento. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

30 Responses to “Mass update stock levels in Magento – FAST”

Tj
8 Feb. 2010

This is great! I have a multiple website install with different B2B setups as well as retail site, all of which have different pricing structures.

Perhaps you guys could do a post for fast updating of prices by store?

Reply
- ben@sonassi.com
  9 Feb. 2010
  
  We certainly could do (in fact, have done), although for pricing – it does take a little longer for product saves, around 0.8s per product.
  
  Reply
sean
12 Feb. 2010

Great information, thanks. Looking forward to your post on importing products quickly. I actually need to figure out that aspect of my site right away, but hopefully it will pop up while I work on some other things!

Reply
Ted
23 Feb. 2010

Great Post guys. Thank You.
Any idea when we can expect the mass products import script?

Reply
- ben@sonassi.com
  23 Feb. 2010
  
  At some point in the near future
  
  Reply
Anh Nguyen
25 Feb. 2010

OMG, this is such a beautiful work. Magento’s importing sucks. I have to spend hours for importing 1000 product stocks. Thank you so much!

Reply
N
31 Mar. 2010

“Next time we’ll teach you how to import products at 0.7 seconds each …”

Really looking forward to this…

Great work on the above script!

Reply
Paul Hachmang
14 Apr. 2010

Hi,

I’ve been using your module with great succes. I only have a few problems. The stock check seems to slow down significantly when i have it running a longer period. I have a shop with about 18.000 products.

When i start the import it does about 20 products per second, but by the time I reach the 8000th product it does only 1.5 products per second.

I tried calling the Mage::reset() function and then Mage::app()->etc. but that doesn’t matter.

Do you have any solution for

Reply
Siri Khalsa
21 Apr. 2010

Great script! Any chance you’ve done something similar to change the ‘visibility’ attribute to a group of products?

Any help or guidance is greatly appreciated!

Thanks – Siri

Reply
James Macintyre
21 Apr. 2010

Great Script Thanks, updating the stock levels is not a nightmare now.

@Paul – Have noticed in the past that emptying the cache can speed a script up again once running. maybe try this.

Reply
Jon
26 Apr. 2010

Hi Guys,

Thanks for the script. I am having some problems with it thought and I’m not sure if this has to do with CSV encoding on Mac. My CSV is formatted as above comma seperated enclosed in quotes.

After: $cols[$value] = $key;
I added: echo “Key: $key; Value: $value\n”;
and this is what I’m getting…

Key: 0; Value: sku
Key: 1; Value: qty “1″
Key: 2; Value: 2 “2″
Key: 3; Value: 3 “3″
Key: 4; Value: 1 “4″
Key: 5; Value: 1 “5″
etc.

TIA for any help.

Reply
Jon
26 Apr. 2010

Just an update I believe I have resolved the problem I just need to change the line endings from Mac CR to Unix LF and everything seems to be working!

Thanks again for this incredibly useful script! Cheers, Jon

Reply
Earl Cristopher
26 Apr. 2010

This is really fast, thanks for the code. When do you guys think finish the script for fast importing of products. I currently need to import 100K products, and it is really really slow.

Thanks!

Reply
Brian Mark
30 Apr. 2010

Your “Blink of an eye” is a little slower than I blink. 120,00 products at .03s/product = 1 hour. That’s more like a nap, not a blink.

Anyway, this script is awesome but I have one question. Would it be faster if we were using ID’s directly instead of sku’s? I can get the id easier than the sku (takes an extra db lookup) from our exports out of our inventory management system.

Reply
- ben@sonassi.com
  30 Apr. 2010
  
  Blink/nap/hibernate … what’s the difference
  
  Regarding loading by ID, perhaps. It wasn’t suitable for us to load by ID as that is stored and generated server side rather than being also stored locally.
  
  Load by attribute does require an extra couple of joins – so you’d save some SQL running if you have the entity_id.
  
  You’ll only really get the answer if you benchmark it I guess…
  
  Reply
Mike
9 May. 2010

Thanks for the script. Works for us but times out with the qty. to import being high. With such a large qty. it would seem that php wouldn’t be the fastest way to update the stock level.

I’ve tried to figure out the mysql JOIN and UPDATE to update the stock directly in the db but haven’t had any luck. Any idea of how an sql query could do this when you have 50k+ skus?

Thanks,

Mick

Reply
Pete
11 May. 2010

HI Guys,

Amazing script thanks alot, do you need to refresh any caches after running this??

Pete

Reply
- ben@sonassi.com
  11 May. 2010
  
  In 1.4 the stock level cache might need a prod.
  
  Reply
Pete
19 May. 2010

Cheers ben,

Mike i think thats a great idea for a large number of products, you would have to upload to a temp table then run the update sql. problem being the eav database architecture makes writing a query to run the update tricky.

Pete

Reply
Alex
25 May. 2010

Thanks man! This worked great for me.

Reply
Mara
26 May. 2010

In 1.4.0.1, I can’t get it to work. I used 2 columns, sku and short_description, ran from the browser, and the script output raw code.

I’m bummed, since it sounds like it works so well for everyone else.

Reply
marky
25 Jun. 2010

Thanks,

I needed way to manage of store.

Reply
James
30 Jun. 2010

This is a great piece of code.

I am considering the need to flag stock status ( out of stock, in stock ) based on the stock value being greater than zero. Has anyone else done this already?

James

Reply
- ben@sonassi.com
  26 Jul. 2010
  
  Hi James,
  
  It wouldn’t be difficult to apply a little logic to it.
  
  Reply
Tim
15 Jul. 2010

Completely amazing!!!! Wow the speed is so much better, thanks so much for this little script, has saved leaving the computer to run all night!

thanks

Reply
Sean
24 Aug. 2010

Magento Datefloe, it’s Glacial!

Reply
Daniel Knudsen
31 Aug. 2010

Hej iv found that importing the csv file to an array and then looping the array speeds up the process i go from 1300 rows handled to 7000 in 20 seconds.

might be worth a look

regards
Daniel Knudsen

Reply
Daniel Knudsen
31 Aug. 2010

strike that, i forgot to save data

its 1300 vs 1600 products, but i do get better performance on the loop if the csv is saved to an array, so it might remove som part of the bottleneck

Reply