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 2 of 5
Import Bulk Attributes
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

yes ...as you noticed, single apostrophes are still a problem and I havent had the time to correct the script for this :( ... for now, you may try substituting, single apostrophe’s with two single apostrophe’s ...think that should work ...

And on the position, I remember taking that into consideration when I started writing this script, but am not confident that it would work as I havent tested that part.

 
Magento Community Magento Community
Magento Community
Magento Community
 
finch8243
Sr. Member
 
Total Posts:  79
Joined:  2007-08-31
 

@srinigenie - thanks for your time and for sharing this query.  It’s saving me a ton of work and would make a nice add-in to an upcoming version of Magento.

@scottrill2 - I wasn’t able to get the ‘position’ column to work, but I came up with a quick SQL query that you can run on any attribute to sort any existing options alphabetically.  I’m sure you could clean it up and run it as a cron to keep all your attribute drop-downs nice and organized:

#Query to alphabetically sort options for an attribute ID
SET @a=-1, @attribute_to_sort=55# '55' is for manufacturer, but change to whatever you want
CREATE TEMPORARY TABLE ReOrder 
SELECT 
(@a:=@a+1) AS RowNumeav_attribute_option.option_id as ThisOptionID FROM eav_attribute_option LEFT JOIN eav_attribute_option_value ON eav_attribute_option.option_id=eav_attribute_option_value.option_id 
WHERE eav_attribute_option
.attribute_id=@attribute_to_sort AND value IS NOT NULL 
ORDER BY value
;
UPDATE eav_attribute_optionReOrder SET sort_orderRowNum*10 WHERE option_idThisOptionID;
 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

good one finch8243 smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
freshwebs
Sr. Member
 
Total Posts:  153
Joined:  2008-05-27
Leicestershire, UK
 

Firstly,
I want to say thanks for your efforts and for putting this in the public domain.

I’ve just managed to upload 1028 values for a drop down attribute - obviously not something I looked forward to doing manually! The alpha sorting SQL was also much appreciated.

My one bitch is that this sort of functionality needs to be added to the core - imagine a smallish online bookstore - the number of potential authors needed to be added to such an attribute is mind boggling, or a car spares shop, etc.

As I’ve got a few such attributes that I need to mass populate, I’ll look at extending the impAttributes code to take in a filename as a parameter - I can’t keep overwriting the same csv file. If I do, I’ll post it up.

Thanks again people,
Eddie

 
Magento Community Magento Community
Magento Community
Magento Community
 
ldat
Jr. Member
 
Total Posts:  1
Joined:  2008-06-20
 
scottrill2 - 04 June 2008 10:37 PM

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

Hey Srinigenie and Scott,

I’m getting the same results as above when I try to run the script.

In my importAttrib.csv file, I have one column containing the admin (Color) and the attribute values which do not contain any apostrophes or quotation marks.

This is what I changed in line 11 of the ImpAttributes.php file:

$obj->saveOptionValues(75//75 for color

I used OpenOffice Calc to create the .csv file. Remaking the file did not help.

Any suggestions?

Thanks to everyone for their contributions. smile

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

must be something with your csv file .... try using CSV edit for saving the csv file

 
Magento Community Magento Community
Magento Community
Magento Community
 
Discovery
Enthusiast
 
Total Posts:  767
Joined:  2007-12-29
 

I did this manual style, i.e. in phpmyadmin and excel. All worked and I escaped out special characters, e.g. double quotes for ‘inches’.

I would recommend trying the new module approach mentioned by Srinigie, however, the phpmyadmin technique may be preferable for someone who not doing too well with the code. Here are my notes:

Bulk importing attribute codes by the hundred.

If importing someone else’s stock file into Magento you may have several hundred size or colour codes to deal with. Obviously you can spend all day typing them in one at a time, however, that can introduce errors, e.g. typos. Another option is to read them all in en-masse using phpmyadmin.

One of the great features of Magento is the filtered navigation, however, a page will look seriously uncool if there are too many options on the panel, e.g. size ‘XXL’ and ‘XX-L’ and ‘2XL’ showing up as well as the customer friendly ‘XX-Large’. The stock file to import can be tidied up so that there are two size entries per product, one for the layered navigation and another for the product page. This requires some work, but the results will pay off. These new size/colour fields can then be setup in Magento so as to not interfere with the rest of your attributes. In this example one code is called ‘size_easy’ and the other is called ‘size_combo’. To find out what they are in the database, open up the Magento database in phpmyadmin and go to the eav_attribute table. Go to the end of the list and note what the attributes are given for their attribute_id values, e.g.:

888 size_easy

889 size_combo

Now, get a list of the attribute values to import. I already have the stock file I need to import in a scratch database table ‘variant’. To pull out the size codes in a sorted order without duplicates I can type:

SELECT DISTINCT size_easy FROM variant ORDER BY size_easy;

In phpmyadmin this result set can be exported as MS Excel CSV and opened up in ... Excel. It seems that I only have 156 options there, these are aross lots of categories so they will be manageable eventually.

SELECT DISTINCT size_combo FROM variant ORDER BY size_combo;

Again I can open this list up in Excel. This reveals that I have 696 ‘full text’ sizes - typical for someone else’s catalogue, as pulled from an ERP system.

Obviously there are posh ways to write these values back in to the database, however, I don’t know them, but I can hack with Excel…

If I now open up eav_attribute_option and see what the last row is, I get 1230 for the autoincremented option_id, The attribute_id is for a colour code (890) and the sort_order is zero in this ‘lil example. The next entry I add in there is going to be 1231, sort order zero, attribute_id 888 for size_easy. I am now able to hack together a SQL task to do that for me, just by adding a few columns in the Excel list of sizes, e.g.:

INSERT INTO eav_attribute_option (attribute_idsort_orderVALUES (8880); INSERT INTO eav_attribute_option_value (option_idstore_idvalueVALUES 12310"0-3 Months");
INSERT INTO eav_attribute_option (attribute_idsort_orderVALUES (8880); INSERT INTO eav_attribute_option_value (option_idstore_idvalueVALUES 12320"0-6 Months");
INSERT INTO eav_attribute_option (attribute_idsort_orderVALUES (8880); INSERT INTO eav_attribute_option_value (option_idstore_idvalueVALUES 12330"1-2 Size");

---

INSERT INTO eav_attribute_option (attribute_idsort_orderVALUES (8880); INSERT INTO eav_attribute_option_value (option_idstore_idvalueVALUES 13840"XX-Small");
INSERT INTO eav_attribute_option (attribute_idsort_orderVALUES (8880); INSERT INTO eav_attribute_option_value (option_idstore_idvalueVALUES 13850"Youth");

To concatenate the columns together use ‘CONCATENATE’ in Excel, making sure there are no spaces introduced. The list of SQL statements can then be read into phpmysqladmin. You may want to backup your database before executing them, however, if all goes to plan there should be a new list of attribute values added to your attribute. Note that the properties for the new attribute will take a while to load up if you have hundreds of options added on there. The products using the attribute should load nice and quickly though.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sparsehian
Jr. Member
 
Total Posts:  2
Joined:  2008-07-02
 

Hello, I’m fairly new to Magento and having trouble importing a customer list.

Magento Version: 1.1.6

I’ve tried uploading a .CSV file test import and it keeps giving me errors. I’ve exported the customer list from to see the template Magento uses and tired re-uploading that same list and its still giving me errors. I’ve done mapping and used the magento format and both does not work. I’ve tried over 20 different ways and still no use!

Ideally, once the import goes through, should i see the customer in the “Manage Customer” section?

The Error: “Skip import row, website “Webiste” field not exists

Please can someone shed some light!

Thanks in Advance

 
Magento Community Magento Community
Magento Community
Magento Community
 
spuky
Jr. Member
 
Total Posts:  4
Joined:  2008-07-18
 
ldat - 28 July 2008 08:54 AM

scottrill2 - 04 June 2008 10:37 PM
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

Hey Srinigenie and Scott,

I’m getting the same results as above when I try to run the script.

In my importAttrib.csv file, I have one column containing the admin (Color) and the attribute values which do not contain any apostrophes or quotation marks.

This is what I changed in line 11 of the ImpAttributes.php file:

$obj->saveOptionValues(75//75 for color

I used OpenOffice Calc to create the .csv file. Remaking the file did not help.

Any suggestions?

Thanks to everyone for their contributions. smile

for everybody that is getting the
Undefined index: Collagen in \www\magento1019870\app\code\core\Mage\Eav\Model\Import.php on line 121

the First line of you csv file must be “admin”

took me a while to figure that out… so to save your time I post this here.

Spuky

 
Magento Community Magento Community
Magento Community
Magento Community
 
Nemphys
Member
 
Total Posts:  54
Joined:  2009-01-28
 

OK, I managed to import about 200 Colors (Attributes) in my tables using the php files provided earlier in this post, and the data inside the tables eav_attribute_option and eav_attribute_option_value look fine (I even got it to work with multiple store views in one single import with a “|” delimited csv).
The problem is that when I try to access attribute “color” or any product in the product list from the admin GUI, I get the following error:

Item (Mage_Eav_Model_Entity_Attribute_Option) with the same id “721” already exist
Trace:
#0 /var/www/magento/lib/Varien/Data/Collection/Db.php(654): Varien_Data_Collection->addItem(Object(Mage_Eav_Model_Entity_Attribute_Option))
#1 /var/www/magento/app/code/core/Mage/Core/Model/Mysql4/Collection/Abstract.php(191): Varien_Data_Collection_Db->load(false, false)
#2 /var/www/magento/app/code/core/Mage/Adminhtml/Block/Catalog/Product/Attribute/Edit/Tab/Options.php(113): Mage_Core_Model_Mysql4_Collection_Abstract->load()
#3 /var/www/magento/app/design/adminhtml/default/default/template/catalog/product/attribute/options.phtml(200): Mage_Adminhtml_Block_Catalog_Product_Attribute_Edit_Tab_Options->getOptionValues()
#4 /var/www/magento/app/code/core/Mage/Core/Block/Template.php(144): include(’/var/www/magent...’)
#5 /var/www/magento/app/code/core/Mage/Core/Block/Template.php(176): Mage_Core_Block_Template->fetchView(’adminhtml/defau...’)

Checking the tables, attribute option with id “721” is on random color entry close to the end of the list, which has nothing suspicious (related to the others).
Any help would be appreciated.

 
Magento Community Magento Community
Magento Community
Magento Community
 
QuadraVis
Sr. Member
 
Avatar
Total Posts:  82
Joined:  2008-05-30
D - Erbach
 

Have a look at this post http://www.magentocommerce.com/boards/viewthread/10634/

Should be an error in Variens lib. I had the same error when i moved a categoriy tree to an other.
Modified the code and then i goes on correctly.

BTW: I imported about 1500 colors without any problems in work. Only the backend won’t open it any more. I got an script error. So if you want to edit it, you have to go into database.

 
Magento Community Magento Community
Magento Community
Magento Community
 
no.mongo
Jr. Member
 
Avatar
Total Posts:  2
Joined:  2008-12-18
 

@srinigenie
My man. You are a legend. A giant, walking amongst mere mortals. Thank you, thank you, thank you for this technique. I was looking at manually inserting 450+ manufacturers and instead it only took a couple of minutes.

Just a note:
On the Line 121 errors people are getting… Leave in the ‘admin’ as the first line of your CSV and don’t use any delimiters (Check it in any plain Text Editor, if your using Excel)

Sweet as a nut mate!

 
Magento Community Magento Community
Magento Community
Magento Community
 
pdxwebhead
Jr. Member
 
Total Posts:  27
Joined:  2008-07-16
Portland Oregon
 

Hi, I am a little perplexed on how to format my the values in my .csv file.  I have a few questions:

1. Must the Option Exist prior to import or can you create an Option by adding it to the .csv file
2. Can you only import values to One Option per import?
3. Can someone post a .csv file sample of a successful import?

TIA

Cheers

 
Magento Community Magento Community
Magento Community
Magento Community
 
jlaha
Jr. Member
 
Total Posts:  12
Joined:  2007-08-31
 

I just tried srinigenie’s set of scripts and instructions and it worked perfectly with Magento 1.2.1.  Great work!

 
Magento Community Magento Community
Magento Community
Magento Community
 
abijah
Sr. Member
 
Total Posts:  130
Joined:  2008-10-01
 

jlaha, can you give us an example of your csv, or answer above questions?

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