Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
Fastest way to bulk update prices? 
 
vaalaskala
Jr. Member
 
Avatar
Total Posts:  8
Joined:  2009-06-19
 

Hy , I manage multiple magento stores and each have 30k plus SKU -s and it’s not a rare occasion that the whole seller changes all of it’s prices in one day and next day and so on.

However if i’m running a store with two dedicated servers (web and database separated) and I need to update 30k price for products it’ will take me (without re indexing or anything) about 1,5 days to complete .o0 with servers howling @ max speed.

Needless to say that the whole seller has changed already all the prices in next day and it’s quite impossible to keep up with batch processing.

So my questions are:

* if there is a special mass price update model/method like we have for stock items inside magento core?
* if there is any faster way to perform a mass update without harming the data ingression in magentos schema?

cause when the prices change the update must take place within seconds not 4sec per product

 
Magento Community Magento Community
Magento Community
Magento Community
 
Open Mage
Member
 
Avatar
Total Posts:  35
Joined:  2009-09-21
Czech Republic
 

Actually looking for the same feature/module/scripts ... I know how to change it absolutely/percents via SQL query, but not in common way which will be suitable for customers.

 
Magento Community Magento Community
Magento Community
Magento Community
 
stengah
Member
 
Avatar
Total Posts:  42
Joined:  2008-07-17
Denmark
 

Count me in on this one - we probably have to face building an extension for this.

 
Magento Community Magento Community
Magento Community
Magento Community
 
modernm
Member
 
Total Posts:  38
Joined:  2010-01-11
 

Take a look at Store Manager. I have not used it for Magento but it does let us do it in the Zen Cart version.

http://www.magentocommerce.com/extension/1822/store-manager-for-magento

Cal

 
Magento Community Magento Community
Magento Community
Magento Community
 
tnitschinger
Jr. Member
 
Total Posts:  12
Joined:  2007-09-12
Vienna, Austria
 

i would need this also.
we currently need 11h + for about 11.000 articles with a simple price update.

tom

 
Magento Community Magento Community
Magento Community
Magento Community
 
EmJaInteractive
Sr. Member
 
Avatar
Total Posts:  121
Joined:  2009-04-29
Charlotte, NC USA
 

Tom,

Take a look at the extension “Bulk Price Changer” as shown below in my signature. We allow store owner to adjust price in bulk by fixed, percentage, or find in replace. Hopefully this will work for your needs!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ratwiz
Jr. Member
 
Total Posts:  7
Joined:  2008-07-22
South Africa
 

My recommendation would also be to look at Magnetic One’s Magento Store Manager. We also were getting frustrated with Magento’s Dataflow for updating pricing, and our custom stock information attributes. Store Manager seems to be the best option for the price and they have a free trial to boot, so that you can make sure that the product works correctly for you before buying.

Our old imports used to take about 2 hours for 5500 products, now we can update in just around 10 minutes smile .

 
Magento Community Magento Community
Magento Community
Magento Community
 
valestudios
Jr. Member
 
Total Posts:  6
Joined:  2010-09-29
 

Hi everyone - an SQL query is really the cleanest and fastest way to go about this, we just performed price updates on a store with 90,000 SKUs and it was done in about 20 minutes.  We\’ve posted a tutorial of our methodology at ValeStudios.com.

 
Magento Community Magento Community
Magento Community
Magento Community
 
vdeluca
Jr. Member
 
Avatar
Total Posts:  5
Joined:  2010-05-27
Buenos Aires
 

Hi, I used this script http://www.vdeluca.com.ar/?p=19 to update prices, with a cronjob that execute this script one time every day and setting the update_prices table every time the prices has changed. I think is the easier way.

Saludos,

Vicente.

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1848
Joined:  2009-06-04
 

A group of Magento forum users headed by dweeves have been working hard on MagMI (Magento Mass Importer) to use SQL to overcome the DataSlow issues. It does the initial product import and also can be used to update existing product.

Documentation to be found here...

Sourceforge page here...

 
Magento Community Magento Community
Magento Community
Magento Community
 
MagePsycho
Mentor
 
Avatar
Total Posts:  1702
Joined:  2009-06-23
 

Q: Fastest way to bulk update prices?
A: Refer the following blog article:
http://www.blog.magepsycho.com/updating-product-prices-in-magento-in-easier-faster-way/

Thanks
Regards

 
Magento Community Magento Community
Magento Community
Magento Community
 
Boatmagic
Sr. Member
 
Avatar
Total Posts:  91
Joined:  2010-08-14
 

I have been updating stock and prices using the following files. I found them on the forum somewhere? Each of these files updates my 13k sku’s in a matter of seconds.

For updating stock, I use a simple .csv file containing the following:
store sku
admin ##### (whatever your sku is)

I have a file (updatestock.php) code below: This of course is ran from my store address:

<?php

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

///// Path to Your CSV file
$path '/chroot/home/...../....../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`)"
);

}
?>

For updating price, I use the following: 
The only drawback to updating prices is, I cannot get it to update list_price, MAP or MRP.
This part I cannot figure out why it won’t work even when using the correct

function updateList_Price($db_magento$entity_id$list_price// ##:list_price

<?php

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

///// Path to Your CSV file
$path '/chroot/home/............./............./html/var/import/WebPrice.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'];
$cost $line['cost'];  
$price $line['price'];    
       
$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);   
        
updateCost($db_magento$entity_id$cost);
    
updatePrice($db_magento$entity_id$price);
    
echo     
"</tr><td>$sku</td>--<td>$cost</td>--<td>$price</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 updateCost($db_magento$entity_id$cost// 68:cost
     
$db_magento->query("UPDATE catalog_product_entity_decimal p_d
                   SET   p_d.value = '
$cost'
                   WHERE p_d.entity_id = '
$entity_id' AND p_d.attribute_id = '68' ");

}

function updatePrice($db_magento$entity_id$price// 64:price
     
$db_magento->query("UPDATE catalog_product_entity_decimal p_d
                   SET   p_d.value = '
$price'
                  WHERE p_d.entity_id = '
$entity_id' AND p_d.attribute_id = '64' ");




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`,`cost`,`price`)"
);
}
?>
 
Magento Community Magento Community
Magento Community
Magento Community
 
WebShopApps
Guru
 
Avatar
Total Posts:  460
Joined:  2008-05-30
London, UK
 

For those struggling with similar issues we’ve got a great extension to bulk update Prices via templates. - Price Updater

http://bit.ly/FQNQ4f

We produced it for a company with a catalog in the hundred’s of thousands of products. It will save you loads of time.

Email us if you have any questions.

Genevieve

 
Magento Community Magento Community
Magento Community
Magento Community
 
eracings
Jr. Member
 
Total Posts:  15
Joined:  2009-02-09
 

I was wondering if you can expand it to be able to change msrp and qty. Thanks so much.

MagePsycho - 17 November 2011 10:13 AM

Q: Fastest way to bulk update prices?
A: Refer the following blog article:
http://www.blog.magepsycho.com/updating-product-prices-in-magento-in-easier-faster-way/

Thanks
Regards

 
Magento Community Magento Community
Magento Community
Magento Community
 
david_bergston
Jr. Member
 
Total Posts:  20
Joined:  2011-12-25
 

Hi)))
I’ve had the same problem until i bought store manager for magento. This software is really nice. I am not so strong in eCommerce, but with this application I am able to manage the store myself. try! hope it will help http://www.magentocommerce.com/magento-connect/store-manager-for-magento-standard-edition-8094.html

 
Magento Community Magento Community
Magento Community
Magento Community
 
MagePsycho
Mentor
 
Avatar
Total Posts:  1702
Joined:  2009-06-23
 

In case if you want to bulk update any prices like regular, special, tier and group price with cool interface then you can use the following extension:
Mass Importer Pro: Price Importer (Regular, Sepcial, Tier & Group Prices)
Documentation

Thanks
Regards

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2