How to improve product inventory daily updates from CSV file?

Question

I'm working on a 10000 products Magento store. I scheduled a cronjob to run everyday at 7am to read a CSV file and update each product inventory and set it out of stock if new QTY is less than 1.

Currently, the update process takes around an hour (more/less) to update all 10000 products. My code as follow:

 $csv = new Varien_File_Csv();
 $csvData = $csv->getData('products.csv');

 foreach($csvData as $k => $v){
     // code to store product_id and new_qty into a temporary table 
 }

 $stock_item_model = Mage::getModel('cataloginventory/stock_item');
 $stock_item_collection = $stock_item_model->getCollection();

 foreach($stock_item_collection as $stock_item) {

     // load new_qty from temporary table based on product_id
     $new_qty = Mage::getResourceModel('custom/temp')->getQty($stock_item->getProductId())

     // set new QTY
     $stock_item->setQty($new_qty);

     // check if in stock or out of stock
     if($new_qty < 1) {
         $stock_item->setIsInStock(0);
     } else {
         $stock_item->setIsInStock(1);
     }   
 }

 // save all
 $stock_item_collection->walk('save'); // this takes around an hour to finish.

So, is there any other way to decrease the update time? Do I need to change the way I'm saving stock items?

Answer 1

try changing indexes to manual, then reindex everything once the import id done (and put the indexes back to auto). if that doesn't help, try working with the database directly - that might depend on your store setup, if you rely on observers when products go out of stock or back in stock.

Answer 2

So, there's

• magmi
• danslo/ApiImport
• use sql against the DB (although this isn't generally recommended) Assuming by product_id you mean the magento entity_id, create your TEMPORARY TABLE in memory, then join it against cataloginventory_stock_item (is_in_stock,qty), cataloginventory_stock_status (stock_status, qty) and cataloginventory_stock_status_idx (stock_status, qty) to update them

Answer 3

Definitely use Magmi. It will zip through 10,000 records happily in under 5 minutes. You can also call it on the cli so it can be automated to run every hour if you wish.

I work on a site with 60,000 products, 26 store views and languages. Magmi is the only way we can manage such a big site without forever waiting for updates to complete.

Answer 4

* UPDATE *

Here is a another copy-pasta of a more bare bones PHP/SQL approach without Magento's layers of onions getting in the way.

This script will take a CSV use the MySQL's built-in functions to load and parse the CSV into a temporary table, where a single loop is performed to update product EAV tables accordingly.

NOTES:

• Keep in mind other flags/observers won't be triggered without initializing the whole Magento Stack.
• is_in_stock isn't considered.
• Promos around products may need to be applied through indexing.
• Enterprise EE 1.13+ may index properly with the addition of MySQL Triggers without the need to having to reindex or modify indexer modes to manual.

Also read over a previous post about optimizing your stack to avoid having to do such:

• Full Page Cache on CE 1.8 - An FPC Magento Module? Varnish? Both?

.

<?php

////////////////GLOBAL DEFINITIONS////////////////////
//DATABASE SETTINGS 
$dbConfig = array(
     'host'      => 'localhost',
     'username'  => 'username',
     'password'  => 'password',
     'dbname'    => 'dbname',
     'driver_options'=> array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'),
);

///// Path to Your CSV file
$path = '/path/html/var/import/WebStockImport.csv';

// Path to Mage.php 
require_once 'app/Mage.php';

// Connect to the databases
$db_magento = Zend_Db::factory('Pdo_Mysql', $dbConfig);

updateTempTableFromFile($db_magento, $path);   

$query = $db_magento->select()->from('temp_table');

$contents_array = $db_magento->fetchAll($query);

// Run for each line
foreach ($contents_array as $line) {


    $sku = $line['sku'];
    $qty = $line['qty'];

    $exists = $db_magento->query("SELECT COUNT(sku) cnt FROM catalog_product_entity WHERE sku = '$sku' LIMIT 1");

    $find_product = (($exists->fetchObject()->cnt) > 0) ? true : false;

    if ($find_product == true) {
        $entity_id = getEntityID_bySKU($db_magento, $sku);   
        updateQTY ($db_magento, $entity_id, $qty);
    echo "</tr><td>$sku</td>-<td>$qty</td></tr><br>";   
    }

}

function getEntityID_bySKU($db_magento, $sku) {

    $entity_row = $db_magento->query("SELECT entity_id FROM catalog_product_entity p_e WHERE p_e.sku = '$sku'")->fetchObject();
    $entity_id  = $entity_row->entity_id;
    return $entity_id;
}

function updateQTY($db_magento, $entity_id, $qty) {

     $db_magento->query("UPDATE cataloginventory_stock_item s_i, cataloginventory_stock_status s_s
         SET   s_i.qty = '$qty', s_i.is_in_stock = IF('$qty'>0, 1,0),
               s_s.qty = '$qty', s_s.stock_status = IF('$qty'>0, 1,0)

         WHERE s_i.product_id = '$entity_id' AND s_i.product_id = s_s.product_id ");

}

function updateTempTableFromFile($db_magento, $path){
    $db_magento->query("TRUNCATE TABLE temp_table");
    $db_magento->query("LOAD DATA LOCAL INFILE '$path'

                      INTO TABLE temp_table
                      FIELDS TERMINATED BY ','
                      LINES TERMINATED BY 'n'
                      (`store`,`sku`, `qty`,`is_in_stock`)");

}
?>

Sauce:

• http://www.magentocommerce.com/boards/viewthread/32491/P240/

---

* OLD POST *

Give this a try as it will avoid a lot of the overhead of the entire stack, as long as you're just updating stock/inventory this will work much faster.

<?
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->getId();
        $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);
?>

note: $file location path for CSV file, and CSV is simply: sku, qty.

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

Sauce:

• https://www.sonassi.com/knowledge-base/magento-kb/mass-update-stock-levels-in-magento-fast/

Answer 5

I am using Magento 1.9

I have tried with the following code. it's OK But i have another smart code

$stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
$stockItemId = $stockItem->getId();
$stockItem->setData('qty', $stockQty);
$stockItem->setData('manage_stock',1);
$stockItem->save(); 

My code is following:-

Mage::getModel('cataloginventory/stock')->backItemQty($productId,$new_qty);