In this article you will learn how to import and export simple products in Magento 2. This article was written for merchants with very little or no knowledge of Magento 2. Feel free to contact me if you require help or have any questions @alexcorradi.
This article was updated on July 8th 2017
1. get a Magento formatted CSV file
To avoid issues when importing your data, It's important to start the process with a well formatted CSV (comma separated values) file.
Option 1: Manually create your own CSV file - If you decide to start with a blank CSV file, ensure your file is formatted correctly for a Magento 2 site. Here are a few tips:
- Your CSV must be UTF-8 format. Therefore it will be preferable to create the CSV in either Google Docs or OpenOffice, both of which provide native UTF-8 compatibility. Do not use Excel to generate the CSV if at all possible.
- Ensure the attribute code and not attribute label is used when populating the headings (columns) in your spreadsheet. TIP: The attribute code must always have lower case characters and individuals words are usually connected by an underscore e.g category_gear (the attribute label would be Category Gear).
- When saving the file, save it as .CSV and not as .xls.
- I would recommend sticking with default options e.g tab and comma separated columns. See below for my standard settings.
Option 2: Get hold of a sample file (my preferred option) - A pre-formatted file containing the relevant attributes and a sample data set can easily be exported from within the Magento 2 admin. Go to SYSTEM > IMPORT > ENTITY TYPE (Products) > DOWNLOAD SAMPLE FILE.
This sample file will provide you with a complete list of all available product attributes. By default, there are 84 attributes in Magento 2. In my honest opinion, the majority of those attributes aren't needed, especially if your product data is non-complex and your custom attributes limited. So, let me show you how to keep things simple...
In the admin go to SYSTEM > EXPORT (Select Entity Type = Products)
The product export page lists all available product attributes for export. By default, Magento will export all 84 available attributes. However, there is an easy way to selectively choose which attributes you want to export.
On the above screengrab you'll notice a Exclude column on the left hand side of the grid. Ticking the box next to a given attribute will exclude this attribute from the product export.
Skimming through one row at the time, remove as many unnecessary attributes as possible. It's entirely up to you of course to decide which product attributes are relevant (to you) and which aren't. For the purpose of this exercise, I'm going to export a CSV file that only contains the mandatory attributes required to create a simple product in a Magento 2 store.
These mandatory attributes are:
- Attribute Set
- Product Type
- Product Websites
- Product Online
- Tax Class
Once the unnecessary attributes have been selected, click the Continue button to download your CSV file. The file will download straight away (no need to have SFTP access to get hold of the file).
If you would like to download the template file as mention above, you can do so by clicking on the link below.
2. Add a product to your spreadsheet
I would highly recommend using Open Office as your spreadsheet of choice. Once installed (If you are using a Mac), right click on the CSV file you've exported from Magento and open it with Open Office.
Before we go any further, it's worth understanding how data validation works in Magento 2. In simple terms, data must be validated before it can be imported into your store. When the import process is run, Magento verifies the following:
- Attributes: Column header names must match corresponding attributes in the database.
- Complex Data: Data found in dropdowns or multi-select input type attributes. Magento needs to verify that the values entered in your spreadsheet exist in the defined set within the Magento database.
- Required Values: Magento checks for mandatory attributes e.g SKU.
- Separators: Magento checks to ensure the correct separators are used in your CSV file. Data values must be separated by comma, and text values must be enclosed in double-quotes.
Do bear the above in mind before populating your spreadsheet, as it may help you better understand why errors occur during the data importing process. Something we'll discuss in step 3 (below).
Now that we understand the basics of how Magento handles data, let's open our sample spreadsheet and populate it with a new product.
In the diagram below, and highlighted in orange, are the attributes from complex data sets (i.e dropdowns or multi-selects). The values found in these attributes need to exists in the defined set in order for them to be successfully imported.
For example, if you want a new product to be associated to the Default Attribute set, ensure the value inputed in the attribute_set_code column is 'Default', with a capital letter D, and not 'default', with a lower case d, as otherwise Magento will throw an error during the import process! Magento is sensitive like that!
The attributes I've left highlighted in white (i.e sku, name, weight) will accept any value, so long as the syntax is correct. In other words, you can add anything in those fields (just watch out for characters such as: , : | as these may be used to separate the columns in your spreadsheet).
In the diagram below I've added a new product called Green Hat to my spreadsheet.
TIP: Notice how I've set the product_online attribute value to 0. This will ensure the product won't automatically appear in my store after the upload process has run. This is good practice and I advice all merchants to do so too! Manually reviewing your product set before they are enabled in store is the only way to be 100% certain that there are no mistakes or issues!
Once you are done populating your spreadsheet, SAVE your file.
If you want to learn more about attributes in Magento 2 , I suggest you check out the official Magento Product Attribute Reference guide.
NB: Handling Magento's Additional Attributes!
Additional Attributes in Mgento 2, are NEW attributes that have been created in your webstore. For example, in the screengrab below, under the additional_attributes column, the attributes cities, colour, designer, display_style_com, frame_style, are all Additional Attributes used by one of my clients in their webstore.
When you run a product export in Magento, unfortunately all these Additional Attributes are grouped together into a single column called additional_attributes. Each one of these attributes is separated by a comma.
This is all very well, but it makes it difficult to manage.
However, you are able to reconfigure your spreadsheet in such a way that each one of these Additional Attribute's can be separated into their own column. The screengrab below shows you how this would be done
3. upload your data to your magento 2 store
To import products from your CSV file, go to SYSTEM > DATA TRANSFER > IMPORT.
Then, in the import settings, select PRODUCTS.
Next, choose your Import Behaviour. There are 3 options available:
- Add/Update - New data will be added to existing products or new products will be created if they don't already exist in store. The only field that can't be updated is SKU.
- Replace - Existing data (from a product already in store) will be replaced with the new data from the spreadsheet.
- Delete - If a product in the spreadsheet (unique identifier being SKU) matches a product in store, this product will be deleted from the database.
Some welcome improvements have been made with regard to how Magento 2 handles data validation. For example, you can check your data (dry run) before running the real import or choose whether or not the import process should stop on error.
Next, select your Field Separator and Multiple Value Separator. Although Magento, by default, will use a comma as a Field Separator, try using a pipe (|) instead. Let me explain why...
The problem with separators is that those characters (i.e commas) may also be used as values in your actual data fields (hint! - if you're uploading a Product Description, it's very likely that you will be using commas in your copy!). If this is the case, you may unintentionally cause what's known as a delimiter collision.
In other words, Magento won't know if the comma used in your spreadsheet is part of text or whether it's meant to be used a separator. I can guarantee that 9 out of 10 times this will mess up your data import!
Using a pipe (|) as a separator is good practice because this character isn't used as often as a comma in plain text. Therefore using it as a separator will considerably diminishes your chances of causing a delimiter collision during the data import.
If you are using a pipe as the field separator, remember to also configure your CSV file to work with a pipe.
Once that's done, select your file and press Check Data.
Once Magento has validated your data you should receive an 'ok go' message, press import.
Once completed, you'll receive the following message 'import successfully done'.
That's that, but if you would like to read more about data import in Magento, read their official documentation by clicking on the link below.
4. Upload product images & final tweaks
Make any final amendments to your product data...
It's possible to associate images to products as part of the import process. I will discuss this process in detail in a further blog post. For now, it's assumed the reader knows how to manually upload images to products through the Magento admin.
You may also want to manually associate products to one or more categories or only make these new products available in a specific storeview.
This is the time to make any of these changes..
5. TEST, TEST, TEST...
In an ideal world, one would review each product before making them live. I appreciate this may not always be possible – so if you're pressed for time, spot-check your data instead.
When I run spot-checks, I always review the first and last product in my list as well as a sample of 5 to 10 other products which have been randomly selected.
As a rule of thumb, if you find an error in your first round of spot-checking you should run a second test. If more errors are uncovered, it's clear that it was not an isolated incident, and you should review your entire data set!
6. enable your products
Make your products live in store. This can be done by clicking on the Enable Product toggle.