Troubleshooting data-import in Magento 2

Working with Magento 2 import can be a complex matter, and we often see  that wrong data and settings can result in your import not working as intended. In this article you will learn how to handle character encoding in your import-files and also find a comprehensive list of common errors and how to handle them.

Are you interested in learning to work with Magento 2 import? Read our beginner friendly guide

Character Encoding

A crucial aspect to keep in mind while working with data sheets in Magento 2 is encoding. Especially when working with data that involves special characters or symbols beyond the standard UTF-8 character set.

One of the most common encoding issues can occur when data is copied and pasted between different spreadsheet software, such as transferring data from Excel to OpenOffice, or vice versa.

Here, some of the characters might not be encoded correctly and will be displayed as garbled values such as:’Ã<9c>’ Ã� etc.

This is due to the different spreadsheet softwares using language-specific characters. When you save a file in one software and open it in another, the languages might not be aligned, hence the encoding issues.

Usually, the UTF-8 encoding is used to represent almost all the characters in the Unicode standard. But they behave differently depending on which language the software is encoded in.

This is seen in fx Excel and OpenOffice which may not consistently manage character encoding, especially when dealing with non-ASCII characters, including accented letters and characters from non-Latin scripts, like À ÈƗ ØŐǤ Ħ Ł ẘ ẙƶ¿Ě etc.

For instance, if you attempt to open a CSV file containing non-ASCII characters, particularly from languages like Chinese, Russian, Arabic, and more, in Excel or OpenOffice, the applications may misinterpret the encoding, leading to garbled og incorrect data display.

Therefore, being mindful of these encoding intricacies is crucial when working with CSV files in Magento 2’s Import-Export function. It ensures that your data is accurately represented and maintains its integrity throughout the import and export processes.

Preventing character encoding issues

These character encoding issues can easily be prevented if you know how to handle it, and the good news is, we got you covered. Here are the steps you can follow to proactively avoid any character encoding hiccups:

• UTF-8 Encoding is Key: Save your import files in UTF-8 encoding. This is particularly important if your files contain non-ASCII characters or data from different languages. Most modern applications, including Excel and OpenOffice, fully support UTF-8 encoding and handle it correctly.
• Review in Text Editors: It’s advisable to inspect your files in a plain text editor like Notepad or TextEditor before uploading or sharing them. Text editors present the file’s raw content without any special formatting or character interpretation which allows you to spot potential encoding issues or unexpected characters.
• Spotting Encoding Problems: If you come across garbled or unexpected characters in the text editor, it could indicate encoding problems. In such instances, it’s crucial to reassess your data handling process and ensure consistent usage of the appropriate encoding (such as UTF-8) throughout your workflow.

Error messages when importing

Product Type is invalid or not supported in row(s):

• Make sure that all the SKU’s products exist in the shop
• Ensure that there are no empty values for SKUs in the CSV file.
• Make sure that SKU doesn’t contain marginal white space

“Value for ‘price’ attribute contains incorrect”

• Price separator should be in the correct format
• Replace all the Comma (,) to Decimal (.) Separator

SKU contains marginal whitespace”

Make sure your SKUs don’t contain marginal white space.

Columns number: “2” have empty headers

• Make sure that the column’s header is not empty. Remember to label the columns correctly

Column names: “Price” are invalid

• Make sure that the columns name are labelled as per system database

Invalid value in Store column (store doesn’t exist?)

• Make sure that you have added the correct store_view_code
• This error comes when you import to multiple store views.

Bad Gateway

Sometimes you can see  “Bad Gateway” , “Time out” errors

• To solve this, you can split the CSV file into some parts. Fx if the CSV file contains 5000 products, you can split them into two parts of 2500 products
• If you have more products then you can split it accordingly

Are you interested in learning to work with Magento 2 import? Read our beginner friendly guide