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

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
 
Saggy
Sr. Member
 
Total Posts:  133
Joined:  2009-02-03
 

Well done Mike!!! I haven’t used the script as yet but can tell that it looks absolutely wonderful.

Thank you very much for sharing!!!!

Cheers
S

 
Magento Community Magento Community
Magento Community
Magento Community
 
saho
Member
 
Avatar
Total Posts:  52
Joined:  2008-08-06
 

Im getting the same error ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[28000]: that other people have. Does anyone know in particular which permissions you may not have that would not allow you to run LOAD DATA INFILE?

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

@saho

Sorry, can’t say quite. Running as root.

Try LOAD DATA LOCAL INFILE

May be will help.

Regards, Michael.

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

@Saggy,

Nevermid. It;s a community smile

Regards, Michael.

 
Magento Community Magento Community
Magento Community
Magento Community
 
saho
Member
 
Avatar
Total Posts:  52
Joined:  2008-08-06
 

LOAD DATA LOCAL INFILE worked perfectly. Thanks
By the way I am updating 18k items with qty price special_price in 27 seconds.

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

Good speed I think smile

You have good host.

 
Magento Community Magento Community
Magento Community
Magento Community
 
saho
Member
 
Avatar
Total Posts:  52
Joined:  2008-08-06
 

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

File Attachments
inventory.zip  (File Size: 26KB - Downloads: 454)
 
Magento Community Magento Community
Magento Community
Magento Community
 
-- Maggi --
Jr. Member
 
Total Posts:  7
Joined:  2008-12-08
 

Hello. I tried all as mentioned in this post. When I call the script in the browser I only see a blank white page and nothing else happens.

Things, I’ve done:
1. Created temp table
2. Changed DB settings (username, password etc.)
3. Changed all paths
4. Uploaded csv
5. Called php script in browser

Can anyone help me? Thanks a lot!

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

@ --Maggi--

Hi! Go now to admin and check the results.

Regards, Michael

 
Magento Community Magento Community
Magento Community
Magento Community
 
-- Maggi --
Jr. Member
 
Total Posts:  7
Joined:  2008-12-08
 

Hi Michael, thanks for your very quick reply. Unfortunately I don’t see any changes in the admin. I installed a new Magento test-shop with only one product. When I call the php file in the browser, there is only a white page. The QTY in the admin doesn’t change. It seams like nothing happens. Can you give me any more advice? Thank you!

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

Hi Maggi! Can You upload Your csv example?

Regards, Michael.

 
Magento Community Magento Community
Magento Community
Magento Community
 
-- Maggi --
Jr. Member
 
Total Posts:  7
Joined:  2008-12-08
 

Hi Michael,

attatched you find a ZIP including the script.php and the import.csv.
On the FTP Server all files have all permissions (777).
In the MySql database I already created the temp_table.

Thank you very much!!

File Attachments
import.zip  (File Size: 2KB - Downloads: 369)
 
Magento Community Magento Community
Magento Community
Magento Community
 
-- Maggi --
Jr. Member
 
Total Posts:  7
Joined:  2008-12-08
 

Hi again. Perhaps the problem is in the Pdo_Mysql module. The error-log says:

[Tue Sep 08 10:28:36 2009] [error] [client xx.xx.xxx.xx] PHP Fatal error:  Uncaught exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user ‘test-shop’@’%’ (using password: YES)’ in /var/www/vhosts/my_domain/httpdocs/test-shop/lib/Zend/Db/Statement/Pdo.php:238\nStack trace:\n#0 /var/www/vhosts/my_domain/httpdocs/test-shop/lib/Zend/Db/Statement.php(283): Zend_Db_Statement_Pdo->_execute(Array)\n#1 /var/www/vhosts/my_domain/httpdocs/test-shop/lib/Zend/Db/Adapter/Abstract.php(457): Zend_Db_Statement->execute(Array)\n#2 /var/www/vhosts/my_domain/httpdocs/test-shop/lib/Zend/Db/Adapter/Pdo/Abstract.php(230): Zend_Db_Adapter_Abstract->query(’LOAD DATA INFIL...’, Array)\n#3 /var/www/vhosts/my_domain/httpdocs/test-shop/import/script.php(63): Zend_Db_Adapter_Pdo_Abstract->query(’LOAD DATA INFIL...’)\n#4 /var/www/vhosts/my_domain/httpdocs/test-shop/import/script.php(20): updateTempTableFromFile(Object(Zend_Db_Adapter_Pdo_Mysql), ‘/var/www/vhosts...’)\n#5 {main}\n  thrown in /var/www/vhosts/my_domain/httpdocs/test-shop/lib/Zend/Db/Statement/Pdo.php on line 238

Thanks again!

 
Magento Community Magento Community
Magento Community
Magento Community
 
michael_g08
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2009-03-06
Ukraine
 

HI again Maggi!

Here is Your script. I made some changes.

But If You have an error like this, You have to change LOAD DATA INFILE to LOAD DATA LOCAL INFILE....

might help.

Regards, Michael.

File Attachments
import.zip  (File Size: 2KB - Downloads: 410)
 
Magento Community Magento Community
Magento Community
Magento Community
 
-- Maggi --
Jr. Member
 
Total Posts:  7
Joined:  2008-12-08
 

Thank you very much for your time and support! That’s amazing!
Unfortunately it still doesn’t work.
with “LOAD DATA INFILE” and with “LOAD DATA LOCAL INFILE” I see a new error now:

[Tue Sep 08 13:29:41 2009] [error] [client xx.xx.xxx.xx] PHP Parse error:  syntax error, unexpected T_FUNCTION, expecting ‘,’ or ‘;’ in /var/www/vhosts/my_domain/httpdocs/test-shop/import/script.php on line 45

In line 45 you see this code:
function getEntityID_bySKU($db_magento, $sku) {

I already tried to change the syntax in the CSV in various versions like:
sku,qty
“sku”,"qty"
sku;qty

But nothing helps. Always the same error.
Again thank you very much!

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top