Manually creating Magento products one at a time though the admin interface can be a time consuming process. One way to speed this up is to import an Excel spreadsheet containing all your product details. There are a few pitfalls to avoid during the process as Magento is very particular about the format of the spreadsheet you import from. This article tells you how to export a template spreadsheet, modify it and add new product data and then import it into Magento avoiding the all to common “Column Name has Duplicates” issue.
1. Firstly, you need to create a few products manually using the Magento Admin interface. If you are going to have both ‘simple’ and ‘configurable’ products in your store, I would create one of each. Create these products as fully as you can so they reflect exactly what you want to see in the shop. These products will be used to create the template spreadsheet.
2. In the Magento Admin, go to System > Import/Export > Export. Select ‘Products’ and ‘CSV’ from the dropdown menus and then scoll to the bottom of the page and click ‘Continue’. Download your template spreadsheet to your local computer and open it.
3. When you come to import the spreadsheet again later in this article you can run into problems. Magento is extremely picky about the format of the spreadsheet and can return errors such as “Column Name has Duplicates”. For this reason you have to jump through a few hoops to get the spreadsheet into the correct format. Lets go:
4. Firstly open Excel (I’m using 2007) and create a new Workbook.
5. Click on ‘Data’ and then click the ‘From Text’ button in the Get External Data menu. This will bring up the Text Import Wizard.
6. Navigate to your Magento Exported CSV file and click ‘Import’.
7. Make sure you have Delimited set as the first option. You can also select which row you would like to start from. Click ‘Next’.
8. In the second step, check the box next to Comma and uncheck any other checkboxes. Click ‘Next’.
9. In the next step, leave the ‘data column format’ at ‘general’ and then select ‘Finish’.
10. You will usually just want to insert the data at Excel cell A1, so just click ‘OK’ and the data will import.
11. You can add your first couple of products by simply modifying the existing products in the spreadsheet. Ensure you honour all Magento limitations. For example, ensure the SKU is unique, ensure the Short Description is less that 255 characters in length. There are a lot of columns in the spreadsheet and you will not need to modify all of them. When you added your products manually through the admin backend in step 1, you only populated certain fields and these are the columns you will need to modify in the spreadsheet. You can highlight these in Excel with a background colour so you can locate them easily when you come back to add more products.
12. When you are ready to save your spreadsheet, select ‘Save as’.
13. In the format window, choose ‘Windows Comma Seperated (csv)’
14. Rename your file to whatever you choose. For example, “products_20120524″
15. Go to your Magento admin panel. Choose System -> Import and select your file.
Your products should import successfully!