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 5
Import Bulk Attributes
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 

Hello,

I had a question I could not find an answer to through search.  It deals with multiple attributes and how to import them without having to do them by hand.

I make all natural soaps.  As some people are allergic to certain ingredients, I want an “ingredients” attribute that is searchable and is displayed on the product page.  Obviously with different bases, moisturizers, exfoliants, fragrances etc.. there are thousands of possible ingredients.

I went ahead and created one product manually. I also created the “ingredients” attribute as a “Multiple Select” and I made it visible on product pages and searchable etc etc..

I then exported this one product and used it as a template, and added a few more products and imported it.

Sku, description, price etc all are fine EXCEPT the “ingredients” attribute.  I was hoping since I created the attribute it would automatically add the new ingredients from the new products to that attribute, but it does not.

I must say that adding labels/options to the multiple select attribute through admin menu is exceedingly slow.  And with thousands of ingredients to do it manually is just ludicrous. 

Is there a way the import/export feature can be used for more than just products or customers?  I also have lots of compliments for some of my products that I would like to add as well in the review and ratings section, but again going into each product and manually adding things is slow.

Am I doing something wrong or is there an easier way of doing it and I’m not seeing it?  Any info would be appreciated.

Thank you for your time,

Scott

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

I have a crude way of doing it and is working well for me ...have imported around 22K multi-select attribute values with this.

Two files required for this

File 1
: placed on home folder (maybe secure this to be run as a cron only) - This requires the attribute_id of the multi-select attribute being imported
ImpAttributes.php

<?php
define
('MAGENTO'realpath(dirname(__FILE__)));
ini_set('memory_limit''32M');
set_time_limit (0);
require_once 
MAGENTO '/app/Mage.php';
Mage::app();
echo 
1;
try 
{
  $obj 
= new Mage_Eav_Model_Import(MAGENTO);
  echo 
2;
  
$obj->saveOptionValues(579); //579 for â€œingredients” // 55 for manufacturer
echo 3;  
  
catch (Exception $e{
    
echo $e->getMessage();
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 


File 2
: Place this in app\code\core\Mage\Eav\Model and name it Import.php

<?php
class Mage_Eav_Model_Import extends Mage_Eav_Model_Mysql4_Entity_Attribute
{
  
private $fileName ;
  private 
$delimiter '|';
  private 
$enclosure '"';
    public function 
__construct($baseDirectory)
    
{
      parent
::__construct();
      
$this->fileName $baseDirectory '/var/import/importAttrib.csv';
      echo 
$this->fileName;
    
}

  
private function &getCsv;() {
    $file 
fopen($this->fileName,"r");
    while(! 
feof($file))
    {
      $csvArr[] 
fgetcsv($file0$this->delimiter$this->enclosure);
    
}

    fclose
($file);
    return 
$csvArr;
  
}

  
protected function populateOptionTable($attribId{
echo "Upload Begin";  

    
$fields = array();
    
$values = array (); // store id => values
    
$optionValues = array (); // option id => $values
    
$option = array ('value' => $optionValues);    
    
$updateOptionValId;
    
$values null;
    
$row null;
    
$disCounter 0;
    
    
$optionTable        $this->getTable('attribute_option');
    
$optionValueTable   $this->getTable('attribute_option_value');
    
$write $this->_getWriteAdapter();
    
$csvStoreArr = array();

    
// Get CSV into Array
    
$csv = & $this->getCsv();

    
$read $this->_getReadAdapter();

    
// exit if the csv file is empty or if it contains only the headers
    
if (count($csv) < or count($csv) == 1) return;

    
$fields $csv[0]// get the field headers from first row of CSV

    // get the store Ids
    
$stores Mage::getModel('core/store')
                ->
getResourceCollection()
                ->
setLoadDefault(true)
                ->
load();

    
// determine the stores for which option values are being uploaded for
    
foreach ($fields as $hdr{
      
if($hdr === 'position' || $hdr === 'isDefault' || $hdr === 'ERROR'{   
        
continue;
      
}
      
foreach ($stores as $store{
        
if ($store->getCode() === $hdr$csvStoreArr[$hdr] $store->getId();
      
}
    }  
    
// start reading the option values - from row 1 (note that 0 represents headers)
    
for ($indx=1;$indx<count($csv);$indx++) {
      $values 
null// initialize to null
      
$row $csv[$indx]// get row

      
if(isset($row) && count($row) > 0{
       
        
//escape the single quote
        //$whereParam = $read->quote($row);
        
        
        
if (is_array($row)) $whereParam '(\''.implode($row,'\',\'').'\')';
        else if  (
strlen($row)) $whereParam '(\''.$row.'\')';
        
        
$select $read->select()->from(array('vals' => $optionValueTable))
                ->
join(array('opt' => $optionTable),'opt.option_id=vals.option_id')
                ->
where('opt.attribute_id=?'$attribId);
        
$select $select
                
->where('vals.value in ' $whereParam);

        
$optionValData $read->fetchAll($select);

       unset(
$select);

       
// get the option Id for this option
       
if (count($optionValData) > 0{
         $optionValDataRow 
$optionValData[0];
         
$optionId $optionValDataRow['option_id'];
       
else $optionId null;

        
$intOptionId = (int) $optionId;

        if (!
$intOptionId{
          $data 
= array(
                         
'attribute_id'  => $attribId,
                         
'sort_order'    => isset($option['order'][$optionId]) ? $option['order'][$optionId] 0,
                        );
          
try{
            $write
->insert($optionTable$data);
            
$intOptionId $write->lastInsertId();
          
catch (Exception $e{ Mage::log($e->getMessage()); }
        } 
else {
            $data 
= array(
                           
'sort_order'    => isset($option['order'][$optionId]) ? $option['order'][$optionId] 0,
                        );
            
$write->update($optionTable$data$write->quoteInto('option_id=?'$intOptionId));
        
}

          $colIndx 
0//initialize row's column index
        
if (isset($row) && is_array($row) && count($row) > 0{
          
foreach($row as $optVal{
            
if ($fields[$colIndx] !== 'position' || $fields[$colIndx] !== 'isDefault' || $fields[$colIndx] !== 'ERROR'{
                $values[$csvStoreArr[$fields[$colIndx]]] 
$optVal// store id => option value
            
}
            $colIndx
++;
          
}
        }
      }

      
      
if (isset($values) && is_array($values) && count($values) > 0{
        
foreach ($values as $storeId => $value{
          
if (!empty($value) || strlen($value) > 0{
            $value 
trim($value);
            
$data = array(
                      
'option_id' => $intOptionId,
                      
'store_id'  => $storeId,
                      
'value'     => $value,
                  );
            
$optionValInsert true;
            
$optionValUpdate false;

            foreach (
$optionValData as $valData{
              
if ((int)$valData['option_id'=== $intOptionId &&
                    (int)
$valData['store_id'=== $storeId{
                $optionValInsert 
false;
                if (
strcasecmp(trim($valData['value']), $value) !== 0{
                  $optionValUpdate 
true;
                    
$updateOptionValId $valData['value_id'];
                
}
                
break;
              
}
            }

          
if ($optionValInsert{
             $write
->insert($optionValueTable$data);
             
Mage::log('Inserted Value -'.$value);
          
else if ($optionValUpdate{
             $write
->update($optionValueTable$data$write->quoteInto('option_id=?'$updateOptionValId));
             
Mage::log('Updated Value -'.$value)
 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

Now place the values that you need to import in a CSV file like below and place this CSV file var/import directory and name this file importAttrib.csv

admin
AttrVal1
AttrVal2
AttrVal3
...
.
...

Now import these attribute values by accessing the URL
http://<your wesite>/ImpAttributes.php

Happy Importing smile. Please let me know how this can be improved. Haven’t had the time to change it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 

Hello Srinigenie,

Thanks for the post.  Also thank you for your participation in these forums.  You have helped me indirectly in a handful of other posts as well lol

Im actually just going to bed so I can’t try this till the morning.  But I do have a few questions. 

These are idiotic questions lol but keep in mind I’m a soap maker not a php pro like yourself.  lol

1.  For file #1 I just copy it word for word into dreamweaver etc.. save it as the php.  But how do I find out what the “attribute_id” is?  Do I use PHPmyadmin or something similiar?

2.  For file #2 do I have to change anything or is that word for word copy and paste.

If you can answer the 2 questions above for me I can handle step 3 without any help woohoo lol

Thanks again for your help.

Scott

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

hi Scott ... unsure on the dreamweaver ...but to determine the attribute_id, I think it is displayed on the Admin login when you edit the attribute (check the URL when you edit the attribute).

I guess, it should work without making any change..let me know if you are stuck somewhere.

 
Magento Community Magento Community
Magento Community
Magento Community
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 

I got those 3 files set up and put in the folders mentioned.  I obviously screwed the pooch on something lol because when I go to the page I get this error:

Parse error: syntax error, unexpected $end in \www\magento1019870\ImpAttributes.php on line 15

I look on line 15 and it looks fine.  I ran a validator that comes with the software and it says everything is fine. 

Could it be some setting on the server that is causing the issue? 

Thanks again,

Scott

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

my bad!!! I hadn’t closed the catch block in the exception handler!!! ...I have corrected the ImpAttributes.php file in my first post .. please copy this again and retry.

 
Magento Community Magento Community
Magento Community
Magento Community
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 

Hi Srinigenie,

Thanks for the update.  Whatever you added got rid of that particular error.  Thank you very much.  However now it says:

1
Parse error: syntax error, unexpected ‘;’, expecting ‘(’ in \www\magento1019870\app\code\core\Mage\Eav\Model\Import.php on line 13

Look forward to your reply, although you guys are 13 or 14 hours ahead of us here in the states.  I think you’re probably just sitting down for dinner.

Chew faster!! lol j/k

I’ll go ahead and go to bed and have nightmares of Satan punishing me in hell by making me enter bulk attributes manually in Magento lol

Scott

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

in Import.php, remove the semicolon in line 13
private function &getCsv;()

I wonder why the forum is adding this character despite me removing it :(!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
sdb
Sr. Member
 
Avatar
Total Posts:  223
Joined:  2007-11-13
coastal California, USA
 
srinigenie - 03 June 2008 11:52 PM

in Import.php, remove the semicolon in line 13
private function &getCsv;()

I wonder why the forum is adding this character despite me removing it :(!!

These forums tend to break code quite a bit if you don’t get it right the first time. I have some posts I can’t edit anymore because of that…

 
Magento Community Magento Community
Magento Community
Magento Community
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 
 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

I should have done this before wink ..I am attaching the Import.php file

File Attachments
Import.php  (File Size: 7KB - Downloads: 1869)
 
Magento Community Magento Community
Magento Community
Magento Community
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 

Awesome,

It seems without the forum butchering your script/code Im not recieving errors anymore.  Here is what is happening now.

1. I run the script

2. It creates a blank value entry in my ingredients attribute.  with Admin and default store view blank and position has 0.

3.  On the actual ImpAttributes.php webpage it returns this message:

111\www\magento1019870/var/import/importAttrib.csv2Reading file contents - \www\magento1019870/var/import/importAttrib.csvUpload BeginNotice: Undefined index: Collagen in \www\magento1019870\app\code\core\Mage\Eav\Model\Import.php on line 121

So its trying to work its magic.  I have put single and double qoutes around the word thinking maybe it had to be seperated or something.  But no go.  I use MS Excel to make my spreadsheets, I have seen some posts saying excel isnt good to use.  Should I maybe download open office or something?

Again thanks for your help Srinigenie!!

Scott

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

Scott,

PM me your CSV file. Hope you are have fed the right attribute Id.

 
Magento Community Magento Community
Magento Community
Magento Community
 
scottrill2
Jr. Member
 
Total Posts:  7
Joined:  2008-05-27
 

Woohoo!!

It must have been something with MS Excel I downloaded CSVed and remade the attribute file and it worked like a charm except for one thing.

Ingredients that have an apostrophe like “Ewe’s Milk” dont import correctly. It mentions a violation of sql syntax.  Is there a way I can get around this?

And also I was wondering, for the position, if I did want them in a particular order, could I just add a second column in the csv and label it position or something?

Again I can’t thank you enough!!!

Scott

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