Magento’s (now Adobe Commerce) product catalog is extremely robust, but setup is key to any successful eCommerce site. If you find yourself fumbling trying to get data loaded remember sometimes less is more; if you are new to the platform take it slow and in pieces. Below we will break catalog imports into easier to digest pieces.

We won’t tell you this is the end all guide to data in the platform, but this will hopefully save you some time in your journey to becoming a Magento Data Guru. You can get tripped up if you don’t know the proper formats, required fields, and naming scheme. 

If you do get into a pickle, and need some help, we offer a variety of Data Services to help make your data migration easier. Let’s dive in with the the first thing you need to do with your import; building your file.

Getting Started Building a File

There is no better place to start than with the learning where to import/export data. We always recommend setting up a few of your own products manually to see how they look after exported. This will give you a template to work off of for your other products.

If you follow the basic downloaded format, and save your file as .CSV UTF-8, you should be able to import without issues. Most newer editors like Excel, OpenOffice Calc, etc. will allow you to save into .CSV UTF-8.

If you are still working in Magento 1, it may have issues with files coming out of Excel that are UTF-8. I would recommend contacting us for a migration to Magento 2 (LOL – its time to upgrade!) or use OpenOffice Calc.

Quick Tip: If you go to: System > Data Transfer > Import  you will find the following screen. Choosing the Entity Type in the drop down will give you a sample data file of whatever Entity Type you chose. Download Sample File

Fields And Formats Matter

Now that you have the standard file layout, we will focus on the fields that are required, and those that are recommended, to get the most out of your data .

File Headers will always be lowercase, and Magento will use (_) for spaces in header names; not all fields in the catalog file are needed to create products. Updating products only require sku and whatever attributes you are wanting to update.

• sku – It is required for all products and should be unique for each of them. Your sku can include both digits and letters without spaces separated with underscores (_) and dashes (-).Quick Tip: If you are building data for your website, and are struggling with how to manage part numbers that might not be unique, or are creating new data for configurable products, we recommend to use suffixes like: sku-blk, sku-red, sku-config, or sku-brand. This will allow you keep the base sku.
• store_view_code – This is the associated store view code. This column can be empty if an associated store / website have a single sub item.
• attribute_set_code – We recommend to leave this as Default unless you have a large amount of product attributes that are tied to specific product maybe by type/category. Product attributes need to be created and configured before importing products into Magento 2. The value(s) should look exactly the same as an attribute set name (e.g. “Default”, “Shirts”, “Shoes”, etc.) If they do not look the same, this can create more work for you in the long run.
• product_type – Type of imported product. Use only lowercase letters (small, configurable etc.)
• categories – Importing categories will automatically create the Product Taxonomy in Catalog > Inventory > Categories. Full paths for categories, are required. Use a | to separate more than one category: (e.g. “Default Category/Parent|Default Category/Parent/Child”) .Quick Tip: You can import with the lowest level category and setup categories to anchors so the product will show up in all Default Category/Parent/Child categories.
• product_websites – Associated product website code. Use lowercase letters only and default is (e.g. “base”).
• name – The name of the product is one of the more critical pieces of data. It is a required field that will trigger the url_key to change on the product if you are importing a new name without url_key in the file. We always recommend importing them together when making updates to name to ensure you aren’t changing your product page URLs.
• description –Product long description or the marketing copy is found in the details tab. Can use HTML.
• short_description – Product short description normally found under the name on the product page. Can use HTML.
• weight – The weight of a product is very important to calculating accurate shipping rates.
• product_online – Enables or disables products. Use 1 or 0 respectively.
• tax_class_name –Product tax classes are required to trigger taxes at checkout
• visibility – Visibility of a product (e.g. “Catalog, Search”, “Not Visible Individually” etc.).
  • Catalog, Search will make the product visible everywhere and is the most common used.
  • Not Visible Individually is normally used with Configurable Products where you don’t want duplicate pages shown.
  • Search – Search only is recommended to use if you have products you don’t want to clutter up your categories with like discontinued items.
• price –Price is a required attribute (e.g. 99.990).
• url_key – URL key of products must be unique. If the field is empty field, a value is generated automatically based on a product name. If you update an existing product name using imports it will trigger a URL key change if they aren’t imported together. We always recommend changing names and url_keys together!
• base_image –Main image on the product page. We recommend using a direct URL of an image, such as http://site.com/image.jpg. As long as its accessible Magento will consume the image for the product import. Images can be ran locally if uploaded into: /pub/media/import.
• small_image – Small image on the category pages. We recommend using a direct URL of an image, such as http://site.com/image.jpg. As long as its accessible Magento will consume the image for the product import. Images can be ran locally if uploaded into: uploaded to /pub/media/import.
• thumbnail_image – Thumbnail image on the search results & cart/checkout pages. We recommend using a direct URL of an image, such as http://site.com/image.jpg. As long as its accessible Magento will consume the image for the product import. Images can be ran locally if uploaded into: uploaded to /pub/media/import.
• additional_attributes – Custom options related to a simple product. This was one of the worst design flaws when we went from M1 to M2, as they combine all custom attributes into this one field at export. This free extension can help with exports of custom attributes, as it allows you break them out individually. If you are using an editor like Excel, the Data tab has a feature called “Text to Columns” that could help break these out a bit. You can import any custom attribute by making its own column in your file. The header should match attribute name.
• qty – Quantity of a product. This only matters if you are maintaining inventory.
• allow_backorders –set 1 if backorders are enabled.
• use_config_backorders – use a default system value for back-order options (enable / disable).
• is_in_stock – 1 – a product is in stock, 0 – a product is out of stock. This is important to maintain if you are doing inventory imports. If a product is out of stock and you import new stock without updating this flag it will remain out of stock.
• manage_stock –Magento 2 considers that a product has an unlimited stock level if setting is 0. You will need to maintain stock of set to 1.
• related_skus –SKUs separated with commas (e.g. 24-WG085,24-WG086,24-WG083). These are normally found bottom of product page.
• crosssell_skus – SKUs separated with commas (e.g. 24-WG085,24-WG086,24-WG083). These are normally found bottom of product page.
• upsell_skus – SKUs separated with commas (e.g. 24-WG085,24-WG086,24-WG083). These are normally found in cart at checkout.
• additional_images – Additional product images found in gallery. These are separated with commas in this single field.  We recommend using a direct URL of an image, such as “http://site.com/image2.jpg,http://site.com/image3.jpg”. As long as its accessible, Magento will consume the image for the product import. Images can be ran locally if uploaded into: uploaded to /pub/media/import.

Simple Product Imports

Here is a simple, and clean, product import example. Everything in Green is a required field. Everything in Yellow is a nice to have fields. Red item is a custom attribute. You can see that the custom attributes can be loaded in an individual column. We would not recommend adding the additional_attributes in a single column, as it can get messy!

Configurable Product Imports

Configurable product imports can be tricky to build, but once you have a method of creating the data, they can be managed via imports quite easily. We always recommend setting up a couple of your own products manually to see how they look after exported. This will give you a solid template to work from. The main configurable product will follow the same scheme as the simple products import, but there are three main differences in the layout:

1. product_type – Type of imported product. Use only lowercase letters “configurable”
2. configurable_variations – This indicates not only the skus associated with the configurable, but also what variation in the products that makes them unique.
3. configurable_variations_labels – Type of imported product. Use only lowercase letters (small, configurable etc.)

Everything in green is a required field, while the red items are there for you to see the relation in the data. Remember, you can import data in pieces, as long as sku is at the beginning of your import. We have found that it is easier to build all simple and configurable products together in a single file, but we will load the configurable_variations and configurable_variations_labels separate like in the screenshot below. This may seem like an extra step, but remember, all it takes is a missing comma or pipe and you will be digging through data trying to clean up your files or your database.

Base Image Imports

Like the other pieces of data previously discussed, its easier to manage data imports if you do them in pieces. This is important if you are also importing images via url, as your server will have to ingest the image from another server. The connection to the 3rd party server can cause your issues with your imports such as the lack of file permissions or session timeouts. If you are trying to do everything in one large file, you might be be able to track down if the import issue was related to formatting or server issues.

Everything in green below is a required field, while the yellow items are nice to have. You can have different images for base_image, small_image, and thumbnail_image, or they can be the same. We have seen clients use lifestyle images used for small_image throughout the categories, but base_image be the product on white background. If you use separate images for each they will still be available in the media gallery along with any other additional_images. When loading additional_images, make sure you separate each with a comma.

Product Inventory Imports

We typically break out inventory imports separately, as most people will want to automate this process in a live site by using flat files and a cron job or api. There are a lot of different inventory related fields, but we will cover the three main fields to consider if you are managing inventory.

Sku and qty are pretty self explanatory, but the is_in_stock flag is important to consider here. If you are importing values, and not updating this flag, it is possible to see an item with stock that is flagged Out of Stock unable to be purchased. A value of 1 indicates In Stock and value of 0 indicates Out of Stock.

Troubleshooting Errors

There are tons of errors you can run into when importing data into Magento. As mentioned above, always start with a working example by downloading a sample file, or exporting manually created products, so you can work off properly formatted file. Below are a few errors you might encounter in file creation:

Error: Product Type – this normally occurs when you are missing required fields or records are not properly formatted.

Error: Column Name – this will happen if the attribute you are trying to import isn’t setup properly in Store > Attributes > Product

Error: Attribute Value – this will happen if the values you are trying to import aren’t setup in the product attribute. This will occur with attributes that are not text values (e.g. dropdowns, multi-select, etc). Also remember that values need to identical to how they were setup including spelling, case, spacing, etc.

Error: URL key – this will occur if you trying to import records with URLs that have already been created. URLs need to be unique. A quick fix would be to add unique identifier to url_key like the sku. As mentioned above if you are importing just the name it will also trigger url_key creation, so it is a good idea to make sure you are import name + url_key to prevent errors like this.

Data can be extremely frustrating. 121eCommerce offers a variety of Data Services not limited to Extraction, Transformation, & Loading.

Product Data Integrations

Adobe Commerce & Magento are the most flexible and scalable platforms in the market. Supported by the largest marketplace and open-source community, you can find a variety of connectors or solutions. If you are looking to connect your CRM, ERP, or PIM check out our integrations page. We have a variety of partners we work with and experience needed to help with any project.