Required CSV File Values for Error-Free Magento Import via Admin
Magento shopping cart is considered to be one of the most feature-rich and at the same time most complex eCommerce platforms. Due to this fact, it is natural that databases are usually massive and contain considerable amount of items. So it is vitally necessary to decrease time required for huge data sets management. For that very reason import has been worked out.
Magento import from .csv is definitely positive option for bulk products upload and mass changes implementation, though lots of users report that they have issues uploading files using it. Imported CSV files must meet a number of specific requirements to be accepted. To be brought up correctly to database, the spreadsheet should include predefined fields with specified values. The names of columns should be exactly the same as database names, otherwise the system might not recognize the row and skip it or return the error. Additionally, remember that shopping cart supports .csv format only and in order for the file to be parsed properly, save it using UTF-8 encoding.
Here is the list of fields with correct names that need to be in your file for Magento product appending to be successful. Remember, that first are specified the exact field names that should be used while import with underbars and no capital letters (used identical ones, do not change them). There is also an explanation for each field and example of possible values to be inputted.
Fields without which the product will not be created:
Without the following fields the product won’t be imported -
- type_id or _type - this field should contain the information that specifies to which type the product belongs, in other words if it is one component or complex. Here should be specified: simple, configurable, virtual, downloadable, grouped or bundle value. Note that it is case-sensitive and values should not be capitalized.
- _attribute_set - here should be the name of attribute set to which products will be assigned. If you did not create any new attribute set, indicate - Default (field is also case sensitive). Otherwise put the name of attribute set exactly the same as you created. You can find it in Admin > Catalog > Attributes > Manage Attribute Set
- tax_class_id - put here tax code if you sell taxable goods (2) or charge for shipping (4). If not, use 0
- weight - it is required to indicate the value for weight as it is used to calculate shipping cost. Use 0 as no value
- sku - the unique alphanumeric identifier that you need to track this product and its quantity in your inventory system. It should contain no more than 64 symbols and shouldn’t include special characters or capital letters. Use 0-9 and a-z for sku.
- name - full product’s name that will be used by your client for product selection. No specific requirements for this field except that it shouldn’t be empty
- price - the value the product is on sale for and for which it can be obtained by the shopper. No specific requirements for this field except that is shouldn’t be empty
- description - should contain the information that advertises item and will help client learn about the product. No specific requirements for this field except that is shouldn’t be empty
- short_description - more concise description of the product that customers will immediately see viewing the product description page. No specific requirements for this field except that is shouldn’t be empty.
The fields responsible for product representation at the front-end
Without the following fields even if the product will be created at database, it won’t be visible from clients’ side:
- visibility - defines whether this product is visible from the catalog, search, both or neither. For clients to be able to find the goods at your store use either value 3 for Search or 4 for Catalog, Search visibility. If needed, you can indicate 1 for Not visible individually - it can be used for configurable, grouped or bundle products that shouldn’t be ordered separately. 2 stands for Catalog view only (in categories products assigned to), so products won’t be shown in search results.
- _root_category - indicate main root category of your shop. If you haven’t made any modifications to your root category, indicate - Default Category
- _category - in this field put the category path in case you have multi-leveled categories and make sure that they are divided by slash separator like Hardware/Memory Cards. Field is case sensitive so in case you have Hardware category in your site and indicate here hardware, you will have duplicated categories. All mentioned is related to Magento versions 1.4+. Older versions use category_ids field.
- _product_websites - indicate to which website the product will be placed. Use base to add default values
- status - you can set it to disabled and depending on this, the product will or will not be available for sale at front-end. If set to disabled, the product will not appear in your catalog. Put 1 for enabled, 2 for disabled.
- qty - required to be more than 0 in order to show your products if you enabled manage stock option in the administration area
- is_in_stock - 1 value means that the product is in stock and 0 stands for ‘out of stock’.
Fields that correspond to attributes you created and made as required
Custom attributes are any attributes that you created yourself and indicated as required (and assigned to particular types of products) become obligatory for your products creation. Check for required attributes in Catalog > Attributes > Manage Attributes and make sure that they are assigned in Catalog > Attributes > Attribute Sets (find the attribute that corresponds to your new products) before you start import
Additional fields (optional)
Other fields are not obligatory and you can add them if you wish. One of such non-obligatory fields are images, though they are needed for more positive general look of your products.
Before the import put image files in media/import and in the .csv file enter the image name prepended with a slash. You can use the same image for base, small, thumbnail images and media gallery. For that fill in the appropriate fields - image, small_image, thumbnail, media_gallery.
How to verify that your file is suitable for import to Magento?
Default Import/Export method has a kind of verification. You have to go to System -> Import/Export -> Import.
Then you will see the button clicking which you will be able to select the file browsing through the folders of your computer. Once selected, click ‘Check data’ button to verify if your CSV and data in it are properly formatted (according to Magento requirements).
After check-up has finished you will verification results: either see errors pointing you what in your file should be adjusted or message notifying you that import file is valid and you can proceed with upload.
Is there sample file suitable for Magento import?We have put together and verified the file, so you can download it and compare with your own spreadsheet -
How to avoid the necessity to include all the mentioned above fields?
If you expect that you can export the data to the file and have an example or can use the same file for import, you are mistaken. Often the file you just exported won’t be suitable for Magento import so you will need to go through all the above requirements to check why your file is not accepted. In case you need working alternative that will deprive you from the necessity to adjust the file according to all requirements, change column names and data placed in them or see the error returned that states that there are some values missing and import failed, try upload from .csv using Store Manager application.
Using it you can:
- import products with only the necessary fields
- do modifications on-the-fly
- even if there are missing fields that are in charge of product reflection at the front-end, default values will be inputted
- if you need to update existing products, you just need the column with identifier (SKU, ID) and other column where you need to implement changes
- free support available that will check your file and will guide you through the process
More on Magento import possibilities read here - www.mag-manager.com/magento-import/
Try error-free Magento Import with FREE Trial of Store Manager software! Import Products
I am uploading a large number products. The upload runs perfectly but
Magento is not adding the file extension to the url_key. So none of the
products uploaded have the “.htm” file extension. Even after indexing.
I’ve tried everything I can think of to resolve this. I’m using version
1.9.4. This is driving me crazy. Do you have any suggestions?
In order to change extension in product URL, you need to provide necessary URL suffix in Magento backend. Navigate to
System->Configuration->Catalog->Search Engine Optimization and specify .htm in 'Category URL Suffix' field, as at the screenshot.
Clear cache and re-index data afterwards.
Great post, how can I overwrite a field with data and replace with a null value.
If the fiedls are empty it won't change them. Is it safe to use null ?
Thank you for your question.
What exactly value you need to set to null?
I tried importing with a column named type_id and it didn't like it. Maybe MAgento 1.9 doesn't like it? 'type' seems to work better.
Hello. Thanks for your comment.
Depending on Magento version, the values for product type column heading vary from 'type_id' to '_type'
Hi, The sample csv is very useful , but I faced problem in case of importing configurable product in Magento 1.9 . Please provide one sample csv for configurable product import .
Thank you for your comment. You can use this step by step guide to import configurable products - https://www.mag-manager.com/useful-articles/how-to-create-different-types-of-products-in-magento/magento-configurable-products/magento-configurable-product-import-step-by-step-tutorial/. There you will also find the sample file.