What is the Minimum Set of Required Fields for Importing Products, Customers, and Orders?

what-is-the-minimum-set-of-required-fields-for-importing-products-customers-and-orders
Posted in: Magento 2 Guides
0 comments

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 required fields you'll need to import products, 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 ProfilesAdd New ProfileImport 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. 

    Import Orders →

    Import Customers → 

    Import Products →

    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.

    email

    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.

    email

    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.

    email

    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:

    • simple — tangible items that are generally sold as single units or in fixed quantities;
    • grouped — a group of separate products that are sold as a set;
    • configurable — a product with multiple options that the customer should select before making a purchase. Inventory can be managed for each set of variations because they feature a separate product with a unique SKU. For example, a combination of color and size for a configurable product is associated with a unique SKU in the catalog;
    • virtual — a non-tangible product that does not require shipping and is not kept in inventory. These can be services, memberships, and subscriptions;
    • bundle — a customizable set of simple products sold together.

    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:

    • simple — tangible items that are generally sold as single units or in fixed quantities;
    • grouped — a group of separate products that are sold as a set;
    • configurable — a product with multiple options that the customer should select before making a purchase. Inventory can be managed for each set of variations because they feature a separate product with a unique SKU. For example, a combination of color and size for a configurable product is associated with a unique SKU in the catalog;virtual — a non-tangible product that does not require shipping and is not kept in inventory. These can be services, memberships, and subscriptions;
    • bundle — a customizable set of simple products sold together.

    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.

    July 8, 2022
    July 11, 2022
    July 1, 2022
    Comments
    Leave your comment

    Your email address will not be published

    This blog was created with Amasty Blog Pro

    This blog was created with Amasty Blog Pro

    © 2009-2022 Amasty. All Rights Reserved.