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.
@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 RowNum, eav_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_option, ReOrder SET sort_order= RowNum*10 WHERE option_id= ThisOptionID;
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.
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.
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_id, sort_order) VALUES (888, 0); INSERT INTO eav_attribute_option_value (option_id, store_id, value) VALUES ( 1231, 0, "0-3 Months"); INSERT INTO eav_attribute_option (attribute_id, sort_order) VALUES (888, 0); INSERT INTO eav_attribute_option_value (option_id, store_id, value) VALUES ( 1232, 0, "0-6 Months"); INSERT INTO eav_attribute_option (attribute_id, sort_order) VALUES (888, 0); INSERT INTO eav_attribute_option_value (option_id, store_id, value) VALUES ( 1233, 0, "1-2 Size");
---
INSERT INTO eav_attribute_option (attribute_id, sort_order) VALUES (888, 0); INSERT INTO eav_attribute_option_value (option_id, store_id, value) VALUES ( 1384, 0, "XX-Small"); INSERT INTO eav_attribute_option (attribute_id, sort_order) VALUES (888, 0); INSERT INTO eav_attribute_option_value (option_id, store_id, value) VALUES ( 1385, 0, "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.
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
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.
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.
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.
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.
@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)
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?
