How to import csv or excel format products data in magento

Data importation is very common in e-commerce or commercial website. Magento provides inbuilt facility to export and import different types of data - csv or excel - like products, customers , custom type data etc. Importing of data is quiet easy in magento due to its inbuilt import and export functionality.


Following steps involves for products data importation in Magento:

1. Getting Valid csv file
2. Checking exported csv file validity at magento interface
3. Placing real data in valid csv file
4. Importing data into magento
5. Indexing imported data into magento catalog

1. Getting Valid csv file:
   Valid csv file can be obtained by exporting products data at -
   System > Import/Export / Export
   
   

   Select entity type - Products in Entity Type select box option in "Export Settings" box at this Export page. Export page image visual has been presented below:

   
   View Full Image in New Window or New Tab
   
   

   Tick check boxes under SKIP heading which you don't want to appear at csv file. Do not skip required 'attribute code'. Following are required product 'attribute code' as per magento official site ('required' word has been mentioned in red color in Full view of the above image) -
   description, name, price, short_description, sku, status, tax_class_id, visibility, weight.
   
   It means you cannot place blank data in the above 'attribute code' otherwise data importation will give error for 'required value'.
   
   You can check and place different column head default and required value at Magento official site also.
   
   If you skip required 'attribute code' while exporting, Magento IMPORT(when you will import that csv file) VALIDATION ENGINE will produce an error for REQUIRED ATTRIBUTE as image below -
   
   Now, press "Continue" button to export product data as csv file.

2. Checking exported csv file validity at magento interface
   Open exported csv file in MS Office Excel(Observation and testing has been done in Office 2007). Save As this csv File with UTF-8 encoding. For UTF-8 encoding formatted file saving visual has beeen given below-
   
   

   Select 'Save as Type' - CSV(Comma delimited) option.
   Click at 'Tools' button to select 'web option' link.
   Select "Unicode(UTF-8)" in "Save this document as" in encoding dialog box. Finally click at OK and save file.
   

   PRE-CAUTION:
   Some pre-caution is necessary if you are placing HTML content in product description. Open saved file content in Notepad or Wordpad or EditPlus to examine csv format output. If you are placing comma, slash, double quote, single quote or other html elements for product description, check QUOTE("") at start and end of the value. Magento will not import csv data without quote at this type of description value or entry.

   
   

   Use OpenOffice org Calc application for start and end double QUOTE generation in description value or entry in csv file, in case of MS Office is not outputing or avoiding double quote in csv file after edit.
   
   Simply, open csv file and save as again in Calc OpenOffice application. Do check "Quote" in check box while opening the csv file in OpenOffice Calc application as below image visual -
   
   View Full Image in New Window or New Tab

   OpneOffice Calc will place double QUOTE automatically at start and end of each field as like below image sample visual(in small red circle). You can examin start and end QUOTE in WIN NOTEPAD view of this saved file also.
   
   NOTE:
   Magento imports only UTF-8 formatted csv file.

   Now go at System >Import/Export/Import in Magento admin. Browse your UTF-8 formatted file to import and check valid file as per Magento import pattern.

   After successful importation, there will be success message for Validation result as image below:

   
   
   One can get valid csv files(one with category and other without categories - valid-file-tested-successfully-with-category.csv, valid-file-tested-successfully-without-category.csv) at here also, just go bottom of this article. Do check validation of file first before real data placement at dolownloaded valid csv file.
   
   
3. Placing real data in valid csv file:
   Open exported and checked csv file, place your products data in respective column like sku, category, description, name, price , short_description, weight and qty.
   
   Category and Subcategory placement -
   Use forward slash as separator for subcategory path in category column. Don't place forward slash at end of the subcategory path. Category and subcategory name must exists at Magento admin to avoid error as "Category does not exists in rows: 1, 2, 3, 4.....". Image visual has been presented below for writing category and subcategory path in category column -
   
   
   Product image file placement -
   Place product image file name starting with forward slash at image, small_image and thumbnail columns as image below:
   
   Place all product image physically at "media/import" folder path at your Magento installation. Magento will accommodate it automatically at proper place.
   
   Actually Magento makes two folder as per starting first and second character of image file name respectively to accommodate a product image in "media/catalog/product" folder. One can examin this path at "value" field in "catalog_product_entity_media_gallery" table in Magento database as below image visual:
   
   
   Just repeat cell value for other column head like - _attribute_set, _type, _product_websites, status, tax_class_id, visibility, min_qty etc.
   
4. Importing data into magento:
   Go at System > Import/Export / Import in Magento Admin.

   Browse your csv file and click at "Check data" button for verification of your csv data as per magento standard.

   Successful message can be viewed at "Validation Results" box. Successful validation image visual has been presented below:

   
   

   Now press "Import" button for importing product data into magento. Successful message image visual has been presented below:

   
   
   
5. Indexing imported data into magento catalog
   Data indexing is necessary to show products at shop frontend. It is done at :
   System > Index Management at Magento admin.
   
   Index Management page image visual has been presented below -
   
   Indexing can be done individually(examine 'action' column respective of 'red color tab' status) or collectively by selecting all option('select all' link). Click at 'Submit' button after selecting your appropriate indexing options and go at shop frontend, refresh the page to check or view your newly imported products.
   

Product data import has been tested successfully(1200 products - "with pre avialable excel data and image paths" - in one go only in half an hour) in magento-1.5.1.0. It saved my team's more than hundreds of hours.

Download Source Here

Tags : CSV data to Magento, Data importing in Magento, E-Commerce, Excel to Magento, Importing CSV product data in Magento, Magento, Magento products excel import

By Aashutosh Kumar Yadav

He is a PHP-based UI/Web designer and developer by profession and very interested in technical writing and blogging. He has been writing technical content for about 10 years and has proficient in practical knowledge and technical writing.
@www.infotokri.in