Required Magento 2 import values

Default Magento 2 import tool allows you to add or update products, replace existing data and delete entities in bulk via CSV file upload.

Comparing to Magento 1.x, the database structure of Magento 2 based stores has been changed. Respectively, the list of required import fields used for Magento 2 import has also been altered.

This article allows to get a deeper understanding of the specific data structure necessary for successful CSV upload into Magento 2.

CSV File Format

The basic file format used for data import is CSV (comma separated value). It is supported by basically all spreadsheet programs and database applications. CSV file is similar to Excel, and it is even simpler and easier to use.

CSV file needs to have a specific structure that matches the database structure when importing.

Here are the main recommendations to spreadsheet formatting:

The first row of the file table should contain the names of the attribute columns. They need to correspond to the attribute codes of the database fields and should be specified in separate column. In order to ensure that the headlines of your CSV file are identical to database fields and will be recognized and accepted by Magento, you can export a few products to see format of column names.

When a CSV file exported from Magento is opened in a text editor (like Notepad), you can see that data fields are separated in most cases by comma (by default), text and other multi-part values are enclosed in double quotes.

If you edit CSV for later import into a Magento 2, it is strongly recommended to re-save the file with UTF-8 encoding.

This is necessary because some of spreadsheet programs add additional characters to the headers or cells that might result in data not being imported to Magento.

To save the file with correct encoding, you can use text editor like Notepad ++ or a spreadsheet program like OpenOffice Calc.

The Sample File

Magento 2 allows you to export the pre-formatted CSV data file, containing the relevant attributes and sample data. To get the file log in to your Magento 2 admin, go to System -> Import -> Entity type (products) -> Download sample file.

This sample file will provide you with a list of all available product attributes and the relations among goods. By default, there are 84 attributes in Magento 2 and the list can be enhanced with attributes that you can create by yourself. However, it does not mean that you need to fill in the info and use all of these attributes.

To keep things simple you can include in the file mandatory fields for Magento 2 import plus additional information fields you want to include for your products.

Correctly Structured CSV File with Fields Explained

Let’s have a closer look at CSV file contents for Magento 2 import. Below, you can find a detailed description of columns necessary for upload of simple products without relations.

Required Fields for Product To Be Created and Displayed

• sku - the alphanumeric identifier that is used to track product for inventory purposes. It should contain no more than 64 symbols and shouldn’t include special characters. Use 0-9 and a-z for SKU. It should be unique and it is not possible to create items with the same SKU if the one already exists.
• name - full product name that will be visible for your clients and can be used to find product in search. No specific requirements for this field except that it shouldn’t be empty.
• price - the value for which a product can be obtained by a shopper. Numeric value (without currency code) is required in format, like 99.
• product_type - this field should contain the information that specifies to which type the product belongs. In Magento there are 6 main product types: simple, configurable, virtual, downloadable, grouped or bundle. Note that it is case-sensitive and values should not be capitalized.
• attribute_set_code - here should be specified the name of attribute set that is available or was created in your Magento 2 store before import. The value in the file should look exactly the same as an attribute set name (including capital letters). For example, ‘Default’, ‘Clothes’
• product_websites - include the column with website code of the product, e.g. ‘base’. Without this info product will be created in admin panel, however will not be displayed at the front-end.
• qty - quantity field with numeric value more than 0. It is also not mandatory, however, if you do not include it, the created products will be displayed out of stock at your store.
• additional_attributes that you have created via Store -> Attributes (Product) and set settings ‘Values required’ to ‘Yes’.

Magento 2 has changed the logics on how you format your custom product attributes in the import CSV file. Magento 1 used to allow a columnar approach, where you could do 1 column for each piece of information. In Magento 2 all the custom attributes you created are placed into one column - additional_attributes.

Format of additional attributes will be the following: attribute=value (for single-value attributes) and attribute=value1|value2|value3 (for multi-value attributes).

Numerous attributes belonging to one product are placed to one cell and separated by comma symbol.

For example: color=Red,gender=Men|Women|Unisex,size=6

Values Set by Default

Here you can find the list of fields set by default during Magento 2 import, in case you haven’t indicated values in file yourself. If necessary, you can include columns with values in your file to change particular details for imported products.

• product_online - this field is responsible for product status. 1 value means Enabled and 2 - Disabled.
• tax_class_name - here should be indicated tax name. By default it is set to ‘Taxable Goods’. If you do not charge taxes, you can set 0. For specific tax rules you have created, you need to use exactly the same tax name, like in Magento 2 backend.
• visibility - determines whether the products is visible in the catalog and available for search. There are 4 visibility variants available: ‘Not Visible Individually’, ‘Catalog’, ‘Search’ and ‘Catalog, Search’.
• is_in_stock - this field represents stock status and it is set depending on quantity of the item. 1 value set in this field means that the product is in stock (if qty is > 0) and can by purchased and 0 stands for ‘out of stock’ (if qty is less than 1).

Recommended Columns

Enumerated columns are not obligatory for Magento 2 import, however, including them might be a good idea in case you would like to provide the buyers the necessary info on your goods.

• categories - to make product easier to find, it is recommended to assign it to category(ies). Magento 2 requires full category path to be indicated (with categories and subcategories separated by slash), when assigning products to categories during import. In case of multiple categories, category paths should be separated by comma or other delimiter used.

For example, Default Category/Gear/Watches,Default Category/Women

This way, products will be assigned to ‘Watches’ and ‘Women’ categories after import.

• description - contains the detailed information on the product, that advertises it and helps buyer learn more about the item sold.
• short_description - more concise description of the product that customers will immediately see viewing product page. Sometimes it is also used in RSS feed listings for shopping sites.

Both, description and short description may include valid HTML tags.

• weight - if items is physically delivered, it also a good idea to include its weight that is often used for shipping cost calculations.
• Meta details, like meta_title, meta_keywords, meta_description help search engines identify and index products in your Magento 2 store.

Image-related fields, like:

• base_image - the main product’s image. For local image to be uploaded: before import it should be placed to /pub/media/import directory at your server. In this case in the file should be placed image name with extension without leading slash (e.g. image-name.jpg). To import remote image, you can use in the file full image URL, like https://site.com/image.jpg (no need to upload images before import)
• small_image - is used on category listings and search results pages. For local image to be uploaded: before import it should be placed to /pub/media/import directory at your server. In this case in the file should be placed image name with extension without leading slash (e.g. image-name.jpg). To import remote image, you can use in the file full image URL, like https://site.com/image.jpg (no need to upload images before import)
• thumbnail_image - appear in the thumbnail gallery, shopping cart, and in some blocks such as related items. For local image to be uploaded: before import it should be placed to /pub/media/import directory at your server. In this case in the file should be placed image name with extension without leading slash (e.g. image-name.jpg). To import remote image, you can use in the file full image URL, like https://site.com/image.jpg (no need to upload images before import)
• additional_images - additional product images (product media gallery) separated with commas. For local images to be uploaded: before import they should be placed to /pub/media/import directory at your server. In this case in the file should be placed image name with extension without leading slash (e.g. image-name.jpg). To import remote images, you can use in the file full image URLs, like https://site.com/image.jpg (no need to upload images before import)

Full list of Magento 2 import fields you can check reading official user guide -

Magento 2 Import Behavior Configuration

After import file has been prepared and all the columns with information are checked, you can proceed with upload itself. To do so, navigate to System-> Import section of your Magento admin.

There select entity type ‘Products’ and configure the following settings:

- Import Behavior - you can select one of the offered actions: add / update, replace existing complex data or delete entities.

- You can specify whether the import process should be stopped if errors occur, and in the Allowed Errors Count field, you can enter how many errors to display.

- Separators: the standard values, ie the comma (","), should normally not be changed. Only in case import file has different separators, these settings may be altered.

- File to import - select the prepared CSV spreadsheet saved at your local PC.

- Images file directory - should be indicated in case pictures were uploaded to other location, than it is recommended by default (/pub/media/import)

Validation of Magento 2 Import Data

Before products are imported into Magento, they must go through the validation process to ensure that the data structure matches the database fields. Validation starts when the ‘Check Data’ button is clicked.

What is checked:

• Column identifiers
• Required values
• Availability of attributes

If the data are valid, ‘File is valid!’ notification appears.

If the validation fails, the error message notifies you on the field(s), where problems were detected, so you can check what should be corrected in your CSV file.

Most Common Import Errors and Solutions to Fix Them

A few most common errors that might occur during import validation are described here along with possible solutions.

We can't find required columns: ....

Check if your file includes mentioned in the error column that is mandatory for Magento 2 import. Re-save the file with UTF-8 encoding.

Please make sure attribute "..." is not empty. in row(s): 1

Column value specified in file should not be empty and has to correspond to Magento requirements. Aso check file encoding.

Column names: "..." are invalid

Check column headers. They have to be exactly the same as Magento 2 database field names. Moreover, incorrect delimiters indicated during import may result in this problem.

Magento 2 Import Without Strict Requirements

As you can see from the bunch of text and explanation above, built-in Magento 2 importer has strict requirements to import file format, columns and fields included. So every time you get the file from your supplier and it does not fit Magento requirements, you have to adjust it manually.

If you are looking for easier and more enhanced Magento 2 import tool, check Store Manager for Magento. This application allows to import products from locally saved or remote (using direct URL or FTP link) files of different formats: CSV, TXT, XML, Excel, ODS. No strict requirements to import file format, due to fields mapping functionality included.

This multi-purpose application also offers the following features to improve Magento 2 import experience:

• Import of all products types with relations
• Add missing values on the fly
• Make various data adjustments right during import (like merging columns, setting price markup etc)
• Update products info with just 2 columns: identifier (SKU, ID etc) and field to be modified
• Setup automated uploads each predefined period of time
• Get free phone, chat, e-mail guidance from our support team

Thanks for reading! If you have questions or need more details on the topic, drop a comment below and we would try to find a solution for your particular case.