Uploading Products to Magento With A Product CSV Excel File
Uploading products to Magento via the product upload sheet can be a great way to save time rather than adding each product one by one in the admin panel. There are several ways to upload products to Magento. You can do it through the standard import feature, the data profiles feature, or install the Magmi Magento extension that adds more product data import functionality. For the purposes of this blog post I am going to focus on the standard import feature because that alone is a difficult feature to navigate and can be difficult to learn quickly without guidance.
To understand the upload feature, you must first understand how product, category, and attribute data works within Magento. I have written three blog posts covering each of these topics that should help you understand how to manage product data in Magento so that you understand these topics before you upload your products via the product excel import feature in Magento.
We will be coming out with more Magento resources to help eCommerce businesses and Magento developers learn Magento better.
1) Create Your Attributes and Attribute Sets
You have to create your attributes and attribute sets within Magento to upload your products to Magento. For Instance, if you want to upload a bunch of configurable products with Magento, you will need to create the appropriate attributes and attribute sets for that product to be configurable. In other words, if your products have different sizes, size will have to be an attribute and the right attribute values within size will have to be created otherwise you cannot upload that product within excel to Magento. To learn more about attributes and attribute sets read our blog on managing attributes and attribute sets in Magento.
2) Create Your Categories
If you want all of your products to go to the right categories you want on your Magento store, you have to set them up in the admin panel. The standard Magento import product feature will not create categories dynamically, therefore the categories must be setup in the Magento admin panel first. You must assign your products in the excel sheet to go to the categories you want them to go to. For more information on setting up categories read our blog on managing categories in Magento.
2) Create Your Websites, Stores, and Store Views
One of the most powerful features in Magento is the ability to have multiple stores and websites within your Magento configuration. To get there go to:
System => Manage Stores
Here you can add new websites, stores, or store views. Once those are created you can assign your product data to the appropriate website, stores, and store views. To learn more about how managing multiple websites, stores, and store names work, Magento has a lot of information on this topic.
3) Manually Create A Few Sample Products
The best way to get a perfect example of the excel format you need to upload your products to Magento is to manually create a few products. I would create ones with each attribute set you will want, each different type of product you will want (simple, configurable, Grouped, Virtual, bundled, or downloadable) so that you can get a sample excel of all the different variations of product data you will need to import. Keep in mind, bundled, and downloadable product types cannot be uploaded via the import feature. Magmi, the Magento import extension, can handle bundled product imports.
The reason this is such a tricky feature in Magento to easily explain is that every excel format is going to be different depending on the custom attributes, custom attribute sets, custom options in each product, and product types you create for your specific product data set in your Magento eCommerce store. Therefore the easiest way to get a sample excel format for your product data to be uploaded to Magento is to create 10 or 20 products that have all the attribute set and product type variations that you will need and then export that data.
4) Export Those Products
Once you have created enough products to build a solid excel format, you will export those products using the Magento export feature. You can find it in:
System => Import/Export => Export in the admin panel
You will want to hit continue and not skip any attributes unless you are confident you do not want to upload that attribute data for any of your products.
5) Follow The Excel Format Of Those Products
Here comes the hard part. Building the excel sheet for all of your products or new products you want to update. There are six product types as I explained in the managing product in Magento blog. If you export the excel sheet having created no custom attributes or attribute sets, you will still have to fill out the data for the default 42 core Magento attributes such as sku, description, name, short description, image, and more.
THE EXCEL MUST BE WINDOWS COMMA SEPERATED FORMAT!
Some of the attributes will be straight forward, such as the _attribute_set column, which simply allows you to apply whatever attribute set you want to that product. However, others, like custom options, are much more complex. I will not be able to go into each core attribute for this initial post, however I may do that if I have more resources to invest in this tutorial / blog. If you go to the attributes in the Magento admin panel, the left column attribute code is what is used in the excel columns that will be the attribute you want uploaded to Magento.
Simple products are the easiest excel format to follow. If you only have one value per attribute you will only need one row per simple product. However if you want to assign one simple product to multiple categories or multiple values of one attribute than you will be required to add multiple rows for those values. You can see how the multiple category values are used in this screenshot on sku simple2, where multiple categories are applied to that product. You only need to fill out multiple rows of data for the one column that has multiple values. If there is not multiple attribute value data for that attribute or column you can leave that row empty.
You will notice that some columns are related to each other like, custom_option_type, custom_option_title, and custom_option_is_required. This means that this attribute or section of the product data has multiple fields that need data to be entered. For instance you need to select what the custom options types are, whether they are required, what the titles for those options are, and more. There is often a sort order associated with an attribute or piece of data such as related products as well.
6) Import Data / Reindex Data
Once you have finished filling out your excel sheet it is time to Import your product excel sheet in the CSV format to Magento. The import feature can be found in the:
System => Import/Export => Import
If your file has less than 100 errors it can be uploaded to Magento as shown in this screen shot:
1) Choose File and Press Import
2) Click Import
You should see a import successful message if the import worked.
3) Magento Index Management
Once your import is successful you will not immediately be able to see all your new data. Refresh the page and you will get a Index Management warning. Click the index management link to update your data in Magento.
4) Reindex Data
You will have to reindex the Product Prices, Category Products, and Catalog Search Index to see your new product data.
5) Check Your Data
Once your Index is complete go through your products in the admin panel and the front end of your website to see if everything worked properly.
7) Debugging Errors
Debugging Magento product data is one of the most frustrating things you will encounter when dealing with Product data. When you upload your CSV to the import feature you will get a list of up to 100 errors at a time if there are errors. There are many different types of errors such as product type errors, attribute not value errors, duplicate column errors, and much more. A product type error means that Magento cannot accept that type of product data in the excel format.
In this case, if you go to lines 23 and 24 in the CSV file, which refer to rows 22 and 23 because the first row of the excel is skipped due to the column names of the attributes, you will see two products that are bundled and downloadable which are product types that cannot be imported via the Magento import feature. By going to the row that has the error you will be have to identify what columns within that row are causing an error. In this case it is the type column that has two product types the Magento import feature will not accept. Once you have fixed your excel sheet, import the data again and see if you still have the same errors.
8) Appending Data
One of the best things about the import feature is that you can append data that is already in your product database rather than recreating the entire product data set. What this means is that if you want to bulk update the value of certain attributes for certain skus you can do this with the excel without compromising or changing any other data. In this case below you could just update the skus description if you wanted to add new or updated existing descriptions to skus in your Magento eCommerce store.
Make sure you select append data when doing this and do not choose the drop down for delete products or replace existing product set!
I know there is a lot that I did not cover here for the sake of limited time that I have. Please comment if you have questions about the Magento import feature that you do not understand.