You can import any type of data into Magento, but in order to create a new record in the database, you need to provide certain information. The import file must contain a minimum set of mandatory fields. For example, it’s impossible to import orders without specifying products or import customers without their names.
Today, we'll talk about the minimum set of Magento 2 product import required fields as well as other mandatory fields you'll need to import customers and orders.
To get a better understanding of how it works, here are the sample files you can fill out as you read the guide:
Import Orders →
Import Products →
Import Customers →
What does the required data range depend on?
There are several types of import behavior in Magento — Add/Update, Replace, Delete (using our extension you can also choose Only Add / Only Update). If you want to update data in already existing records (for example, you have a file with new prices, and you want to import this file, because it’s too time-consuming to update each product manually), the number of required fields will be much lower as the record already exists. If you choose the Add behavior (you want to create a new file), then you will have to specify much more information.
It also depends a lot on what exactly you are importing. Importing orders requires its own unique set of fields, different from importing products.
How does it work in the Amasty Import/Export extension?
This extension has a tree structure: you select the main entity and its fields, and then include smaller sub-entities, depending on what data from the file you need to import into Magento.
There is a special setting in our extension that automatically activates the necessary sections and fields for importing so that you can understand which blocks must be filled in.
Let's see how this works using an example of importing products:
1. go to Import Profiles → Add New Profile → Import Settings
2. and select the Import Behavior here:
- Add/Update — the extension will compare product IDs from the import file with the already existing products in your Magento instance. If a product ID is unique, a new product will be created. The extension will change/add the data specified in the import file if there is already a product with this ID.
- Only Add — the extension will import products with new IDs and skip existing ones.
- Only Update — the extension will update the information in the existing products and skip non-existing ones.
- Delete — the extension will delete the products specified in the import file from Magento.
3. Move the toggle to Yes in the Enable Autofill for Typical Use Cases option.
This option adds all required fields to the Fields Configuration tab. Note that this functionality is available only when creating a new profile.
Finally, make sure that the fields that were added are in the file. If everything is okay, set up field mapping and validate.
Set of entities and fields
Importing consists of several steps for each of which you need to specify various data.
Let's review the main entities, what mandatory fields they have, and what data should be stored in each of them.
Order
You will need to specify the following blocks of fields:
- Order (root entity)
- Order Item
- Order Payment
- Order Shipping Address
- Order Billing Address
Each block has its own set of minimum required fields. Here’s what you need to specify in each of them:
Order (root entity)
Magento field to enable | Data to specify |
entity_id | The unique identifier of an order for the table. |
state | Order state predefined by Magento (New, Processing, Complete, Pending Payment, Canceled, Closed, Payment Review, On Hold). |
status | Order status that you use in your personal workflow (may be the same as in Magento or include custom statuses). |
is_virtual | Indicates whether the cart contains only virtual products. It uses a boolean value for Yes/No. "1" means "Yes", and "0" means "No". |
store_id | Foreign key associated with the store table. Determines which Magento store view is associated with the order. |
customer_id | Determines customer attributes associated with the order. If the order was placed through guest checkout, this field will be NULL. |
customer_group_id | Determines the customer group associated with the order. |
customer_firstname | Indicates a customer’s first name. |
customer_lastname | Indicates a customer’s last name. |
base_grand_total | Final price in the base currency paid by the customer for the order, after all taxes, shipping, and discounts are applied. |
base_shipping_amount | Shipping value in the base currency applied to order. |
base_subtotal | Gross merchandise value of all items included in the order in the base currency. Taxes, shipping, discounts, and so on are not included. |
base_tax_amount | Tax value applied to the order in the base currency. |
grand_total | Final price paid by the customer for the order, after all taxes, shipping, and discounts are applied. |
shipping_amount | Shipping value applied to the order. |
subtotal | Gross merchandise value of all items included in the order. Taxes, shipping, discounts, and so on, are not included. |
tax_amount | Tax value applied to the order. |
base_to_global_rate | The coefficient of the base currency to global rate for the stores with multi-currency support (ex: If an item costs 50 USD on the global level and 40 EUR in a particular store view, then the coefficient you need to specify in the file will be 0.8). |
base_to_order_rate | The coefficient of the base currency to global rate for the stores with multi-currency support (ex: If an item costs 50 USD on the global level and 40 EUR in a particular order, then the coefficient you need to specify in the file will be 0.8). |
total_qty_ordered | Total quantity of items included in the order. |
shipping_address_id | Determines the shipping address details associated with the order. |
billing_address_id | Determines the billing address details associated with the order. |
quote_id | Determines cart attributes associated with the cart item. |
increment_id | Unique identifier for the order, and commonly referred to as the order_id within Magento. The increment_id is most often used for connecting with external sources, like Google Ecommerce. |
customer_email | Email address of the customer placing the order. This option will be filled in all situations, including orders placed through the guest checkout. |
created_at | Creation timestamp of the order, usually stored locally in UTC. Depending on your configuration in MBI, this timestamp may be converted to a reporting time zone in MBI that differs from your database time zone. |
updated_at | Update timestamp of the order, usually stored locally in UTC. |
Order Item
Magento field to enable | Data to specify |
item_id | The unique identifier of the product. |
parent_item_id | Foreign key that relates a simple product to its parent bundle or configurable product. Determines parent product attributes associated with a simple product. For parent order items (that is, bundle or configurable product types), the parent_item_id will be NULL. |
order_id | Determines order attributes associated with the order item. |
store_id | Determine which Magento store view is associated with the order item. |
product_id | Determines product attributes associated with the order item. |
product_type | Type of product that was sold. It can be a simple, configurable, grouped, virtual, bundle, or downloadable product. |
weight | The weight of the individual product. The actual product weight is determined by the carrier when shipped. |
is_virtual | Indicates whether the cart contains only virtual products. It uses a boolean value for Yes/No. "1" means "Yes", and "0" means "No". |
sku | The unique identifier for the Order item that was purchased. |
name | Text name of the order item. |
qty_ordered | The number of units of the product that are included in this specific order. |
price | The price of a single unit of the order item. |
base_price | The price of an individual unit of a product at the time of sale after catalog price rules, tiered discounts, and special pricing is applied and before any taxes, shipping, or cart discounts are applied, featured in the base currency of the store. |
row_total | Row total is calculated per order item. The formula is as follows: Row Total = ((ordered item price * ordered item Qty) + Tax) - Discount |
base_row_total | Row total specified in the base currency. |
row_weight | The weight of the simple product that will be displayed in the parent row. |
created_at | Creation timestamp of the order item, usually stored locally in UTC. Depending on your configuration in MBI, this timestamp may be converted to a reporting time zone in MBI that differs from your database time zone. |
updated_at | Update timestamp of the order, usually stored locally in UTC. |
product_options | Contains product options and custom values. Usually, the info_buyRequest function is used. |
Order Payment
Magento field to enable | Data to specify |
entity_id | The unique identifier of an order for the table. |
parent_id | The identifier of the original order in case it has been edited from the backend. |
method | The applied payment method. |
base_amount_ordered | The total amount in the base currency. |
amount_ordered | The total amount in the currency used for the payment. |
Order Shipping Address
Magento field to enable | Data to specify |
entity_id | The unique identifier of an order for the table. |
parent_id | The identifier of the original order in case it has been edited from the backend. |
quote_address_id | The unique identifier that relates to the quote_address table to match the order with the quote. |
region_id | The ID of the region (most often state or province) for the order. |
region | The text value of the region (most often state or province) for the order. |
lastname | Indicates the customer’s last name. |
street | The street specified in the shipping address. |
city | The city specified in the shipping address. |
The email address associated with the account. | |
telephone | Indicates the customer’s telephone number. |
firstname | Indicates the customer’s first name. |
address_type | Indicates the shipping or billing address type. |
country_id | The ID of the shipping country for the order. |
Order Billing Address
Note: same fields as for the Order Shipping Address
Magento field to enable | Data to specify |
entity_id | The unique identifier of an order for the table. |
parent_id | The identifier of the original order in case it has been edited from the backend. |
quote_address_id | The unique identifier that relates to the quote_address table to match an order with the quote. |
region_id | The ID of the region (most often state or province) for the order. |
region | The text value of the region (most often state or province) for the order. |
lastname | Indicates the customer’s last name. |
street | The street specified in the billing address. |
city | The city specified in the billing address. |
The email address associated with the account. | |
telephone | Indicates the customer’s telephone number. |
firstname | Indicates the customer’s first name. |
address_type | Indicates the shipping or billing address type. |
country_id | The ID of the billing country for the order. |
Customer
When importing customers, you only need to specify the root entity for the correct import. The others are optional.
Customer (root entity)
Magento field to enable | Data to specify |
entity_id | The unique identifier of a customer for the table. |
The customer’s email address. | |
website_id | Identifies the specific website for installations with multiple websites. |
group_id | Determines the customer group associated with the registered account. |
store_id | Determines which Magento store view is associated with the account. |
firstname | Indicates the customer’s first name. |
lastname | Indicates the customer’s last name. |
If you want to import customer groups, the file must contain the following fields:
Magento field to enable | Data to specify |
customer_group_id | The unique identifier of a customer group for the table. |
tax_class_id | The identifier of the tax class attribute that will be assigned to the group. |
customer_group_code | The text value for the customer group (e.g. General) |
Product
Product importing is most complicated because there are different types of products – and you have to import them in different ways. To import Configurable Products, you must first migrate Simple ones. Only when all Simple Products are imported, you can start importing the Configurable ones.
Simple Products
The only thing you need to import Simple Products is to specify the root block:
Product (root entity)
Magento field to enable | Data to specify |
entity_id | The unique identifier of a product for the table. |
attribute_set_id | Assigns the product to a specific attribute set or product template, according to the product type. |
type_id | Indicates the product type. Values are:
|
sku | The Stock-Keeping Unit is a unique, alphanumeric identifier that is used to track inventory. An SKU can be up to 64 characters in length. For example: sku123 Note that an SKU longer than 64 characters causes failure of importing. |
has_options | 1 — if the product has the options (for example, specify 1 for configurable products)/ 0 — the product doesn’t have options. |
required_options | 1 — if the product has required options; 0 — the product doesn’t have required options. |
Configurable Products
After the Simple Products have been successfully added, you can import the Configurable Products. To do this, you need to specify the following blocks:
- Product (root entity)
- Configurable Product Super Attribute
- Configurable Product Super Attribute Link
So, at this step, you need to include the following fields and specify the data shown below:
Product (root entity)
Magento field to enable | Data to specify |
attribute_set_id | The unique identifier of an attribute set for the table. |
type_id | Indicates the product type. Values are:
|
sku | The Stock-Keeping Unit is a unique, alphanumeric identifier that is used to track inventory. An SKU can be up to 64 characters in length. For example: sku123 Note that an SKU longer than 64 characters causes failure of importing. |
has_options | 1 — if the product has the options (for example, specify 1 for configurable products)/ 0 — the product doesn’t have options. |
Configurable Product Super Attribute
Magento field to enable | Data to specify |
product_super_ attribute_id | The attribute ID of a configurable product variation. |
product_id | The ID of the configurable product. |
attribute_id | The ID of the linked attribute. |
position | Determines the position of the associated product when listed with other associated products. |
Configurable Product Super Attribute Link
Magento field to enable | Data to specify |
product_id | The ID of the simple product. |
parent_id | The ID of the configurable product. |
sku | The SKU of a single product. |
Pricing
Let’s proceed with the Pricing. You can Import Pricing using the Customer Group Price block.
In case of pricing, you will need to complete the following fields:
Magento field to enable | Data to specify |
value_id | The unique identifier of the price group for the table. |
all_groups | 1 - if the price group applies to all customer groups; 0 - if the price group applies to specific customer groups. |
customer_group_id | Determines the customer group identifier for which the pricing should be applied; 0 - for all groups. If you need it for a specific group, specify the ID of the desired one. |
qty | The quantity of the product that must be ordered to apply the specified price. |
value | The price that should be applied if the required quantity is ordered. |
website_id | Identifies a specific website where the product is available (for installations with multiple websites). If blank, the product is available on all websites. |
Product Category
The next entity you can import is Product Category. To import categories, you need to enable the following entities:
- Product Category
- Product Category Attribute
After that, complete the fields with the data below:
Product Category
Magento field to enable | Data to specify |
entity_id | The unique identifier of the category for the table. |
path | The path according to the category tree. E.g. 1/2/20/22 |
position | Determines the position (sort order) of the category. |
children_count | The number of subcategories included in the parent one. |
Product Category Attribute
Magento field to enable | Data to specify |
name | The title of the category. |
available_sort_by | The parameter to use for the sorting in the category, e.g. Position, Product Name, Price. |
default_sort_by | The default parameter to use for the sorting in the category, e.g. Position, Product Name, Price. |
filter_price_range | The parameter is specified as the price step of the layered navigation. |
Importing is a complicated process, and we hope we've managed to make it a little easier and clearer for you.
If you still have any questions, don't hesitate to ask them in the comments below.
Stay tuned, we are already working on the following useful articles about import/export!
Special thanks to Pavel Gabrusevich for his help in creating the article.
