Magento Forum

   
Page 17 of 17
Poll
Do you think the product import in Magento is too slow?
Yes, it should be improved! 613
No, it’s fine. There are more important things to work on. 19
What are you talking about? 1
Total Votes: 633
You must be a logged-in member to vote
Import Speed / Performance optimization
 
afmaury
Sr. Member
 
Total Posts:  100
Joined:  2008-01-05
 

You might want to post some of your sample files so people can see exactly what you are referring to regarding your CSV and script to convert your CSV file.

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

That’s probably a good idea...TY

This importproducts.php works like a champ. I’m getting the .csv from my distributor and it contains sku and qty, without the column headings.

<?php
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.

// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.

// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.

// Author: Brian Wilhelm
// Website: http://www.bwilhelm.com
//
//my dropshipper's feed URL - replace with your dropshipper's feed
$myFeed 'http://myfeed';
 
// specify the name of the file you want to save
$myFilename 'path/WebStockImport.csv';
     
    if (
$myFeed != ''{
    
//  Initialize the cURL session
    
$ch curl_init();
    
curl_setopt($chCURLOPT_URL$myFeed);
    
//Create a new file
    
$fp fopen($myFilename'w');
    
// Save to file
    
curl_setopt($chCURLOPT_FILE$fp);
    
// Execute the cURL session
    
curl_exec ($ch);
    
//Close cURL session and file
    
curl_close ($ch);
    
fclose($fp);
    
    echo 
"Feed Imported Successfully!";
  
        
//check the file size - if its 0 then my dropshipper didn't provide any updates, so don't let me know
        
if (filesize($myFilename) > 0{
 
            
// send email to me when executed
            
$to "email@email.com";
            
$subject "Feed Imported";
            
$body "Hi,\n\nThe latest product data for YOURSITE has been downloaded on ";
            
$body .= date('l jS \of F Y h:i:s A')."\n\n File Size: ".filesize($myFilename) . " bytes";
 
                if (
mail($to$subject$body)) {
                  
echo("<p>Message successfully sent!</p>");
                     
else {
                 
echo("<p>Message delivery failed...</p>");
                
}
         }
}

Ok, I’m using this script.php to perform the update..Works like a champ!
What I am curious to know is if this could be modified without using the “store” and “is_in_stock” code?

When I remove them the script doesn’t perform anything. My temp_table has store,sku,qty,is_in_stock.
If I remove the store and is_in_stock from the table and the script will it work?

<?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`)"
);

}
?>

I have setup a cron job to get the .csv from my distributor. But I would like to see if it would work as described above.

This part of the code seems to be reading from the magento dbase, and not the temp_table correct?
Or does this need to find the store and is_in_stock fields in the .csv to function? This is what it appears to need?

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 ");

 
Magento Community Magento Community
Magento Community
Magento Community
 
Boatmagic
Sr. Member
 
Avatar
Total Posts:  91
Joined:  2010-08-14
 
saho - 06 September 2009 11:57 AM

I just wanted to take a minute and explain what I did to be able to download and upload inventory and prices for approx 20k products in a matter of a few minutes. You know give my hard work back.

Using the wiki example script I added in two fields that I felt needed to be updated as well. Price and Special Price.
I also use the great extension single feed to be able to download all my products then use the excel vlookup function to be able to update price/special_price/qty with the current data from my POS.

I also built in a script timer and the ability to print it out on screen. If it can be improved, feel free.

In between the sku and qty in the foreach statment added.

$price $line['price'];
          
$special_price $line['special_price'];

after the updateQTY($db_magento, $entity_id, $qty); line i added
updatePrice($db_magento$entity_id$price);
              
updateSpecialPrice($db_magento$entity_id$special_price);

then added two new functions

function updatePrice($db_magento$entity_id$price// 60: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 = '60' ");
      
}
      
            
function updateSpecialPrice($db_magento$entity_id$special_price// 61:special_price
           
$db_magento->query("UPDATE catalog_product_entity_decimal p_d
               SET   p_d.value = '
$special_price'
               WHERE p_d.entity_id = '
$entity_id' AND p_d.attribute_id = '61' ");
      
}

Also make sure in the function updateTempFromFile section you add the two price fields.
function updateTempTableFromFile($db_magento$path){
          $db_magento
->query("TRUNCATE TABLE temp_inv");
 
          
$db_magento->query("LOAD DATA LOCAL INFILE '$path'
                            INTO TABLE temp_inv
                            CHARACTER SET cp1251
                            FIELDS TERMINATED BY ','
                            LINES TERMINATED BY 'n'
                            (`store`,`sku`,`price`,`special_price`,`qty`)"
);
      
}

I have included the actual script in a .zip file that includes the script itself, a screen shot of the settings for the singlefeed configuration, the .csv file and the sql file you need to import into your db.

Have fun.

Oh also the link to the singlefeed extension is: http://www.magentocommerce.com/extension/712/singlefeed-export-module/
One noteworthy mention is after you run the singlefeed file it can be found in media/export

I finally got your changes to work for me..This is a nice improvement.

There is one snag however. When I try to use any attribute that has multiple words, like (list_price) or trying to update map or mrp prices, the code will not function. It works great with sku, qty, is_in_stock, cost and price, but when using the other price options it fails?

I get this error;

PHP Fatal error:  Uncaught exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’list_price’)’ at line 6’

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

Can someone please explain why this code will not work when I add the code for (map,mrp and list_price) and also add them to the temp_table. They all seem to be identical as far as sql goes, but I get these errors; A

PHP Fatal error:  Uncaught exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’map’,’mrp’,`list_price`)’ at line 6’ in /...../html/lib/Zend/Db/Statement/Pdo.php:234
[Thu Jan 06 17:22:37 2011] [error] [client 67.60.150.162] Stack trace:
[Thu Jan 06 17:22:37 2011] [error] [client 67.60.150.162] #0 /......./html/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
[Thu Jan 06 17:22:37 2011] [error] [client 67.60.150.162] #1 /......../html/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
[Thu Jan 06 17:22:37 2011] [error] [client 67.60.150.162] #2 /......./html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’LOAD DATA LOCAL...’, Array)
[Thu Jan 06 17:22:37 2011] [error] [client 67.60.150.162] #3 /......./html/updatestockprice.php(113): Zend_Db_Adapter_Pdo_Abstract->query(’LOAD DATA LOCAL...’)
[Thu Jan 06 17:22:37 2011] [error] [client 67.60.150.162] #4 /......./html/lib/Zend/Db/Statement/Pdo.php on line 234

<?php

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

///// Path to Your CSV file
$path '/chroot/home/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'];
    
$price $line['price'];
    
$cost $line['cost']
    
$map $line['map'];
    
$mrp $line['mrp'];    
    
$list_price $line['list_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);   
        
updateQTY ($db_magento$entity_id$qty);
    
updatePrice($db_magento$entity_id$price);
        
updateCost($db_magento$entity_id$cost);
        
updateMap($db_magento$entity_id$map);
        
updateMrp($db_magento$entity_id$mrp);
        
updateListPrice($db_magento$entity_id$list_price);

    echo 
    
"</tr><td>$sku</td>--<td>$qty</td>--<td>$price</td>--<td>$cost</td>--<td>$map</td>--<td>$mrp</td>--<td>$list_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 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 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 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 updateMap($db_magento$entity_id$map// 123:map
           
$db_magento->query("UPDATE catalog_product_entity_decimal p_d
               SET   p_d.value = '
$map'
               WHERE p_d.entity_id = '
$entity_id' AND p_d.attribute_id = '123' ");
      


    
function updateMrp($db_magento$entity_id$mrp// 124:mrp
           
$db_magento->query("UPDATE catalog_product_entity_decimal p_d
               SET   p_d.value = '
$mrp'
               WHERE p_d.entity_id = '
$entity_id' AND p_d.attribute_id = '124' ");
      
}         

    
function updateListPrice($db_magento$entity_id$list_price// 125:list_price
           
$db_magento->query("UPDATE catalog_product_entity_decimal p_d
               SET   p_d.value = '
$list_price'
               WHERE p_d.entity_id = '
$entity_id' AND p_d.attribute_id = '125' ");
      
}

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`,`price','cost','map','mrp','list_price')
}
?>

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

No one can figure this out?

 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

I would recommend you to take a look at magmi because:
- magmi does not use hardcoded attribute ids
- magmi is really fast (as fast as this script at least, surely even faster)
- magmi as a broader range of import possibilities & can also handle configurables
- even for doing very custom things on your data, magmi has an open plugin architecture

 
Magento Community Magento Community
Magento Community
Magento Community
 
jorgesixto
Jr. Member
 
Total Posts:  3
Joined:  2011-04-16
 

Where I can view this script or documentation of this, because in this forum is a little crazy search for stable version of this script or for little explanation or tutorial.

and a little more question, this script can import the category of the product too?

thanks a lot.

 
Magento Community Magento Community
Magento Community
Magento Community
 
htorres
Jr. Member
 
Total Posts:  1
Joined:  2012-01-14
 

Just wanted to share that after waiting 8 hrs to load 26k customer records, I decided to try increasing the parameter for number of records to 250 and now is processing 250 records every 4 seconds, which is a great improvement considering I have more than 300k customer accounts to import.
Thanks.

Magento Ent v.1.11
redhat 2.6.32
dual core

 
Magento Community Magento Community
Magento Community
Magento Community
 
castfly
Jr. Member
 
Total Posts:  1
Joined:  2012-05-31
 

Thanks for this inventory load script. I am getting an error when I try to run:

“Table ‘magento.temp_table’ doesn’t exist”

I don’t see where the temp table gets created. Do I need to manually create?

Thanks in advance. Full error below.

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento.temp_table' doesn't exist' in /var/www/html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/html/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('
TRUNCATE TABLE ...', Array)
#4 /home/ec2-user/load.php(55): Zend_Db_Adapter_Pdo_Abstract->query('
TRUNCATE TABLE ...')
#5 /home/ec2-user/load.php(20): updateTempTableFromFile(Object(Zend_Db_Adapter_Pdo_Mysql), '
/home/ec2-user/...')
#6 {main}

Next exception '
Zend_Db_Statement_Exception' with message 'SQLSTATE[42S02]Base table or view not found1146 Table 'magento.temp_table' doesn't exist' in /var/www/html/lib/Zend/Db/Statement/Pdo.php:234
Stack trace
:
#0 /va in /var/www/html/lib/Zend/Db/Statement/Pdo.php on line 234

 
Magento Community Magento Community
Magento Community
Magento Community
 
derrickthomas
Jr. Member
 
Total Posts:  1
Joined:  2012-09-11
 

I recently installed Magento my imported items, the regulations Analytics, PayPal, customized layouts, and so on. Every little thing went fairly smooth. Really like Magento! But i would like some 1400 items from the database and I require updating the quantity of data each and every two hours.

 
Magento Community Magento Community
Magento Community
Magento Community
 
grafis
Jr. Member
 
Total Posts:  2
Joined:  2011-09-03
 

Hi2all.

This script is rocking, thank you!!!

One Q:

Prices in XML from my supplier are supply prices and I need to update them (increase them) before all is imported into Magento db.
There is also some kind of condition:

For example: If price in XML feed is equal or greater then 500 usd, than margin is 1,5%, aand if price in XML file of some product is 600 USD then I need to increse them by 1,9% before Magento update.

Some ideas?!

 
Magento Community Magento Community
Magento Community
Magento Community
 
brasseale
Jr. Member
 
Total Posts:  1
Joined:  2013-03-25
 

I haven’t seen an update here in a while and I thought I’d throw in a fantastic project by dweeves. He created the Magento Mass Importer and I have personally used it to update 500+ entries in a little under 4 seconds from a file containing 10k records.

Magmi

This isn’t a plugin for the backend of Magento, but is uploaded in the root folder of your Magento install and accessed by the url http://your.magento.install/magmi/web/magmi.php

**Note: You will definitely want to protect this installation by either setting an htpasswd guarding the folder, or uploading this in a directory above your root and referencing the Magento files properly in the config.

(I’m not affiliated with this developer or the plugin other than having downloaded and used it.)

 
Magento Community Magento Community
Magento Community
Magento Community
 
goivvy
Guru
 
Avatar
Total Posts:  322
Joined:  2010-12-15
Moscow, Russia
 

thumbs up for magmi

it is

a. fast
b. scriptable
c. can be run from command line (cron)

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top
Page 17 of 17