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 179
the quest for an efficient product import script
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

Welcome to the Magmi thread , this is the new headpost.

What is Magmi :
- Magmi is a Magento Mass Importer developed as a magento DATABASE client, (ie not a magento extension) , that operates directly in SQL and is the result of a deep analysis of the Magento Database Model.
The first magmi goal was catalog import & update.
- it can create products or update an existing catalog and deal with HUGE data (Millions of products could be managed by magmi,but even above a few 1000’s , you’ll see the real difference with dataSlow)

Main Features:
- It provides decent speed compared to Dataflow (depending on server config & number of attributes, 70-100 rows/sec is standard magmi speed).
- It supports Dataflow export CSVs file & also some enhanced CSV syntax for dealing with custom options import & media gallery import.
- it works for multistore
- It supports remote image urls for image related attributes (in this case, speed is affected by image download)
- It also provides Generic SQL Datasource Plugin that enable getting data to be imported into magento from another legacy database instead of CSV.
- Can handle configurables (through configurables plugin)
- support for related,cross relate,upsell & cross sell (from 0.7.11)

UI:
- Magmi provides a Web UI for configuration & running, however,it can be called via cli for automation.

Limitations:
- cannot handle downloable nor bundled product types yet

Environment:
- PHP , to be deployed on magento server
- InnoDB tables compatibilty (using Transactions)

Compatiblity:
Magento 1.3.x,1.4.x,1.5.x (unless proven otherwise)

Documentation (Wiki) - to be updated
New wiki - to be updated
Sourceforge project Page (provides a download link for last release)
Magmi downloads (Plugins , Releases)

DISCLAIMER
- This tool has to be tested upon non production databases before being put into production and has to be used with CAUTION.
- Each new release should be tested the same manner.

 
Magento Community Magento Community
Magento Community
Magento Community
 
pastanislas
Member
 
Total Posts:  46
Joined:  2008-07-29
 

Hello again wink
Bug on csv separator :
line 969 :

$this->cols=fgetcsv($f,4096,$csep);
should read
$this->cols=fgetcsv($f,4096,'$csep');
line 985
while($row=fgetcsv($f,4096,$csep,'"'))
should read
while($row=fgetcsv($f,4096,'$csep','"'))

I confirm that the Upsell / Crossell bug has been solved.

I still have a bug using the reset parameter, the products are well cleared but the script crash before import. here is the output.

Clearing product list....OK
PHP Fatal error
:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /var/www/peyrabon/magento/magento_mass_importer.class.php:71
Stack trace
:
#0 /var/www/peyrabon/magento/magento_mass_importer.class.php(71): PDOStatement->execute(Array)
#1 /var/www/peyrabon/magento/magento_mass_importer.class.php(111): DBHelper->exec_stmt('SELECT `eav_att...', Array)
#2 /var/www/peyrabon/magento/magento_mass_importer.class.php(279): DBHelper->select('SELECT `eav_att...', Array)
#3 /var/www/peyrabon/magento/magento_mass_importer.class.php(960): MagentoMassImporter->initAttrInfos(Array)
#4 /var/www/peyrabon/magento/magento_mass_importer.cli.php(52): MagentoMassImporter->import('/var/www/peyrab...', 1)
#5 {main}
  
thrown in /var/www/peyrabon/magento/magento_mass_importer.class.php on line 71

Thank you again for your work and for sharing to the community.

PS: just for those missing that in posts from previous post:

The speed is simply AMAZING I have loaded 1000 product in less than 10 seconds, your script report 10669 rec/mn !

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

the $csep is ok in the code, but try to put your separator between double quotes in the .ini

csv_separator=";" if you are using semicolon since ; is also a comment in a .ini

i’ll investigate the buffering problem.

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

Updated version 0.2.2

some .ini cleaning
using fetchAll for setting up attribute info in order to try to avoid exception

File Attachments
magento_mass_importer.zip  (File Size: 10KB - Downloads: 851)
 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

version 0.2.3

- fix: do not create option value for empty select value

File Attachments
magento_mass_importer.zip  (File Size: 10KB - Downloads: 849)
 
Magento Community Magento Community
Magento Community
Magento Community
 
mennos
Member
 
Total Posts:  47
Joined:  2010-04-11
 

wow.. it’s fast!.. and I mean - FAST!!

awesome work dweeves!

started working with it yesterday, and found the following; I thought I\’d share to avoid others the pain of finding this error:

Category Id: (i.e.: category = 4, subcategory = 10)
We have lots of categories & subcategories, main categories are set as ANCHOR
therefore we only have to enter the subcategory in the category_ids, i.e. 10 - On an export, Magento also exports this as (ie) 10
however, in the script this doesn’t work - we must enter 4,10 to avoid an error (sql looks for path 1/2/10 that doesn\’t exist - only 1/2/4/10 exists)

cheers,
Menno

update: tried some other category settings to check the behaviour.
tried to put an item in multiple categories : main category 3, subcategory 7 AND main category 4, subcategory 10
magento exports this as 3,4,7,10 - script won’t process this as this is entered in sql as 1/2/3/7 and 1/2/4/10, script looks for 1/2/3/4/7/10

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

Thanks for the report, i’ll work on it and will deliver new version fixing anchor & multiple categories assignment

 
Magento Community Magento Community
Magento Community
Magento Community
 
mennos
Member
 
Total Posts:  47
Joined:  2010-04-11
 

I missed support for Multiple Select attributes, so added the code below in the public function handleVarcharAttribute (after line 634)
works like a charm - all multiselect values are being updated with the correct values

can’t say this enough: dweeves - great powerful script - keep up the great work, and a million thanks for sharing!

//Added to support multiple select attributes
        //(as far as i could figure out) always stored as varchars

        //if it's a multiselect value
        if($attrdesc["frontend_input"]=="multiselect")
        
{
        $attid
=$attrdesc["attribute_id"];

            
//do nothing if empty
            
if($ivalue=="")
            
{
                
return false;            
            
}

            
//magento uses "," as separator for different multiselect values

            
unset($temp_values); 
            
$multiselectvalues=explode(",",$ivalue);

            
//for each value retrieve option id

            
foreach($multiselectvalues as $multivalue)
            
{
                
//retrieve the option id 
            
$entryvalue=$this->getOptionIdFromValue($attid,$multivalue);

            
$temp_values .= $entryvalue;
            
$temp_values .=','//value separator

            
}

            
//result_all has a colon to much on the end, strip it:

            
$ovalue substr_replace($temp_values,"",-1);

            
// ovalue is set to the option id's, seperated by a colon and all multiselect values will be inserted 
        
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

Thanks mennos,
i’ll suggest to use getOptionId instead of getOptionIdFromValue

- getOptionIdFromValue works only if option id already exists
- getOptionId will create a new option if value does not exist smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
mennos
Member
 
Total Posts:  47
Joined:  2010-04-11
 

thanks dweeves - works even better now smile

I’ve been trying to get all different sorts of data in, came across an issue:

When inserting/updating dates (like special_to_date), the value inserted when empty is 0000-00-00 00:00:00
in the frontend & backend this results in weird dates like: 30-11--1.
The value that the script inserts should be NULL, then there’s no problem.

same for special_price: now it’s 0, should also be NULL (the item show now as on sale for EUR 0.00)

working on this now, if I’ve got a solution, I’ll post again

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

for mennos:
Can you test those Quick fixes for your issues:
Will be included in next release if works

add these 2 methods:

/**
     * attribute handler for decimal attributes
     * @param int $pid    : product id
     * @param int $ivalue : initial value of attribute
     * @param array $attrdesc : attribute description
     * @return mixed : false if no further processing is needed, 
     *                     string (magento value) for the decimal attribute otherwise  
     */     
    public function handleDecimalAttribute($pid,$ivalue,$attrdesc)
    
{
        $ovalue
=(empty($ivalue)?false:$ivalue);
        return 
$ovalue;
    
}
    
    
/**
     * attribute handler for datetime attributes
    * @param int $pid    : product id
     * @param int $ivalue : initial value of attribute
     * @param array $attrdesc : attribute description
     * @return mixed : false if no further processing is needed, 
     *                     string (magento value) for the datetime attribute otherwise  
     */    
public function handleDatetimeAttribute($pid,$ivalue,$attrdesc)
    
{
        $ovalue
=(empty($ivalue)?false:$ivalue);
        return 
$ovalue;
    
}

And change assignCategories method with this code

public function assignCategories($pid,$item)
    
{
        $ccfst
=$this->tablename("catalog_category_flat_store_".$this->website_ids[$item["websites"]]);
        
$catids=explode(",",$item["category_ids"]);
        
//build possible path list
        
$sql="SELECT entity_id FROM $ccfst
              WHERE entity_id IN ("
.$item['category_ids'].")";
        
$ccpt=$this->tablename("catalog_category_product");
        
#if we did not reset products
        
if(!$this->reset)
        
{
            
#remove category assignment of current product
            
$sql="DELETE FROM $ccpt WHERE product_id=?";
            
$this->delete($sql,$pid);
        
}
        
        $inserts
=array();
        
$data=array();
        foreach(
$catids as $catid)
        
{
            $inserts[]
="(?,?)";
            
$data[]=$catid;
            
$data[]=$pid;
        
}
        
#create new category assignment for products
        
$sql="INSERT INTO $ccpt (`category_id`,`product_id`)
             VALUES "
;
        
$sql.=implode(",",$inserts);
        
$this->insert($sql,$data);
    
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
mennos
Member
 
Total Posts:  47
Joined:  2010-04-11
 

fairly easy to solve the NULL-issue; replaced :

$data[]=$ovalue;

on line 710 with

if(!empty($ovalue)) {
                    $data[]
=$ovalue;
                    
else {
                    $data[]
=NULL;
                    
}

- I;m pretty much a newbie here, so code should probably be reviewed and optimised!

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

my fix is better optimized since it won’t even perform an insert for empty values smile

i PM’d you my instant messenging coords if you want to chat on the current work

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

updated version 0.2.4
- support for multiple categories (thanks mennos for submitting bug)
- support for optimzed multiselect (thanks mennos for submitting patch)
- fix for empty values on decimal & datetime attributes

File Attachments
magento_mass_importer.zip  (File Size: 10KB - Downloads: 388)
 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

updated version 0.2.5
- put version as member , removed from .ini
- fix empty value management (force NULL insert) for datetime & decimal (thanks mennos for quick test)

File Attachments
magento_mass_importer.zip  (File Size: 10KB - Downloads: 381)
 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

updated version 0.2.6

- fix for multiselect options (thanks mennos for testing it live !!!!)

File Attachments
magento_mass_importer.zip  (File Size: 10KB - Downloads: 794)
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 179