Bronze Adobe Solution Partner
Login Contact Us
My cart

Import products CSV Explained

Apr 10, 2014
by Mexbs team
Magento Tutorials

Compatibility:The CSV file presented in this article was tested with the following Magento Community Edition versions - 1.6.1.0, 1.6.2.0, 1.7.0.0, 1.7.0.2, 1.8.0.0, 1.8.1.0

In this article we will present a sample CSV import file, the results of the import and an explanation for the key columns and rows of the file.

Here is our sample CSV import file

"sku","name","description","short_description","weight","price","_store","tax_class_id","status","visibility","image","small_image","thumbnail","_media_image","_media_attribute_id","_media_lable","_media_position","_media_is_disabled","qty","is_in_stock","inventory_stock_availability","_category","_type","_attribute_set","_product_websites","shirt_size","color"
"new_product1","New Product1","Description of New Product1","Short Description of New Product1","1.1","101","","0","1","4","new_product1.jpg","new_product1.jpg","new_product1.jpg","new_product1.jpg","703","New Product 1 Label","0","0","11","1","1","Apparel","simple","Default","my_website_code1","Small","Blue"
"","","","","","","","","","","","","","new_product2.jpg","703","New Product 1 Label 2","1","0","","","","","","","","",""
"","","","","","","","","","","","","","","","","","","","","","Apparel/Shirts","","","","",""
"","","","","","","","","","","","","","","","","","","","","","Apparel/Shirts/English","","","","",""
"","","","","","","","","","","","","","","","","","","","","","Apparel/Shirts/German","","","","",""
"","","","","","","","","","","","","","","","","","","","","","Apparel/Shirts/French","","","","",""
"","New Product1 - French","","","1.1","101","french","0","1","4","","","","","","","","","","","","","","","","","Blue"
"new_product2","New Product2","Description of New Product2","Short Description of New Product2","1.2","102","","0","1","4","new_product2.jpg","new_product2.jpg","new_product2.jpg","new_product2.jpg","703","New Product 1 Label","0","0","12","1","1","Apparel","simple","Default","my_website_code1","Medium","Red"
"","","","","","","","","","","","","","","","","","","","","","","","","my_website_code2","",""

Let's import this file by going to the backend, to "System->Import/Export->Import" and make sure that the screen looks like the following:

Choose the CSV and click "Check Data". If no errors are displayed, the "Import" button will show up. Note: If you get the following error "Required attribute 'media_gallery' has an empty value in rows:", you need to do the following: Go to "Catalog->Attributes->Manage Attributes" and search for "media_gallery" in the "Attribute Code" column. Click the attribute row. In "Values Required" field select "No" and save the attribute. Go back to the import and click "Check Data" again. The error should disappear now.Click "Import" and wait until the import is done. After the import, two new products will show up:

  • Product 1:
    • SKU: new_product1
    • Name: New Product1
    • Weight: 1.1
    • Price: 101
    • Color: Blue
    • Quantity: 11
    • Stock Availability: In Stock
    • Images: new_product1.jpg,new_product2.jpg
    • Websites: Main Website
    • Categories: Apparel, Apparel/Shirts, Apparel/Shirts/English, Apparel/Shirts/German, Apparel/Shirts/French
  • Product 2:
    • SKU: new_product2
    • Name: New Product2
    • Weight: 1.2
    • Price: 102
    • Color: Red
    • Quantity: 12
    • Stock Availability: In Stock
    • Images: new_product2.jpg
    • Websites: Main Website, Website2
    • Categories: Apparel

Columns and rows explanation

Here is the header line of the import CSV file:

"sku","name","description","short_description","weight","price","_store","tax_class_id","status","visibility","image","small_image","thumbnail","_media_image","_media_attribute_id","_media_lable","_media_position","_media_is_disabled","qty","is_in_stock","inventory_stock_availability","_category","_type","_attribute_set","_product_websites","shirt_size","color"

It defines the columns of the CSV file. We will explain the essential ones:

  • _store - this value is only specified in the case you want to give product attribute values for store scope. In this case, you should specify the store code. For website/global scopes, this value remains empty. In our example, we wanted to give the product name "New Product1 - French" to the Product 1 only for the french store. You can see this in line 8. Attention!There is a bug in Magento import when assigning product values for store scope. Magento fills the global attributes (e.g: price, weight, color) with the value given in the store scope import. That is, if we would write 
    "","New Product1 - French","","","","","french","","","","","","","","","","","","","","","","","","","",""
    instead of current line 8, Magento will assign empty values to the global attributes of the product. Thus, we must assign values again to all the global attributes each time we use the store scope. (This is what we do in line 8 for "weight","price","tax_class_id","status","visibility" and "color")
  • _media_attribute_id - this is the attribute id of the "media_gallery" attribute. You can find it by going to "Catalog->Attributes->Manage Attributes" and searching by "media_gallery" in the "Attribute Code". Click it and you will find the attribute id in the URL. In our example it is 703.
  • _media_image,_media_lable,_media_position,_media_is_disabled- the file name, label and position of the media image (and if it should be disabled). You should place the image files that you want to upload into the media/import directory. That is, in our example, we placed the files new_product1.jpg and new_product2.jpg to media/import. You can add only one media image per line. If you'd like to add additional image to the product, you should add a line which looks like line 3 in our example csv: 
    "","","","","","","","","","","","","","new_product2.jpg","703","New Product 1 Label 2","1","0","","","","","","","","",""
    (This line must be located under the main line of the product that you want to assign it to)
  • image,small_image,thumbnail - those are the big, small and very small versions of the main product image. Obviously you cannot have more than one of each of those per product.
  • _category - the category that the product is assigned to. It should be the hierarchy of the category starting form the root category. For example: Apparel/ShirtsIf you'd like to assign more than one category to the product, you should add a line which looks like line 4 in our example csv: 
    "","","","","","","","","","","","","","","","","","","","","","Apparel/Shirts","","","","",""
    (This line must be located under the main line of the product that you want to assign it to) In our example, we have 4 such lines for "New Product1", that is - we assigned "New Product1" to 5 categories.
  • _type - Magento product type - can be simple,virtual,downloadable,bundle,configurable,grouped. (Those are the core product types. In the case that you developed additional product types that support import, you can use them as well)
  • _attribute_set - the name of the attribute set of the product. You can find the attribute sets by going to "Catalog->Attributes->Manage Attribute Sets"
  • _product_websites - the website code of the website that the product is assigned to. Similarly to the category and media, you can assign more than one website per product by adding the line similar to line 10 in our example csv.

Note:In versions 1.7.0.0 and 1.7.0.2 there is a bug with import of quantities of products, Magento assigns the last products quantity to all the products which are imported. In order to fix it, do the following: Copy the file app/code/core/Mage/ImportExport/Model/Import/Entity/Product.php to app/code/local/Mage/ImportExport/Model/Import/Entity/Product.php Open the copied file. Go to the line 1608 and add the following line:

$row = array();

So you will get the following result:

...
// only SCOPE_DEFAULT can contain stock data
if (self::SCOPE_DEFAULT != $this->getRowScope($rowData)) {
    continue;
}
$row = array();
$row['product_id'] = $this->_newSku[$rowData[self::COL_SKU]]['entity_id'];
$row['stock_id'] = 1;

...

Sum up - in this article we have covered some of the main columns and rows of the import product CSV file. Currently we covered only the simple products import, and only the "Append Complex Data" import behavior. We will cover the other options in the next articles series.