Mastering Magento’s product Import/Export system is a feat in itself. If you don’t follow Magento’s constraints, the results can be disastrous.
There are two ways to import products into Magento. This article will refer to Import/Export, not Dataflow Profiles.
Magento’s out-of-the-box import functionality has a lot of room for error. First and foremost, I cannot stress the importance of importing to a development site prior to importing your live site. If you absolutely have to import to a live site, our best piece of advice is to upload one product to test that everything looks alright. If there is an error occurring, uploading multiple products all at once can potentially take a long time to fix.
There is a long list of reasons why Magento can report an error after the Data Check.
The most common reasons we’ve encountered are:
- Case Sensitivity
A default Magento attribute like product type (under the “_type” column of the CSV) are case sensitive. In the “_type” column, a simple product will be displayed as “simple”. If you change this to “Simple”, Magento will not recognize it. Magento has a list of what the options under these columns should be (which you can refer to here). This applies to any custom attributes you might have created as well. As long as you enter your data the same way it’s set up in the Admin end, it should be fine. - Wrong or Missing Columns
When you export a file with Import/Export, it is absolutely imperative to leave the column’s names looking exactly as they are. “SKU” will not be recognized — it will only be accepted as “sku”.
Magento exports a lot of columns. Even more so if you have custom attributes. This can get pretty user-unfriendly if you have hundreds of custom attributes.
For updating products, I usually recommend using a few columns. Magento will relate any new data with an existing product based on SKU, so always leave the “_sku” column.
For adding new products, it’s best to keep all the columns.
If you are missing columns, Magento will usually tell you what’s missing. - Saving a CSV in a program like Microsoft Excel
A lot of Magento Developers will strongly recommend using an alternative to Microsoft Excel. This is because Excel makes it hard to save a CSV with specific filters or won’t let you pick the encoding. Magento requires saving a CSV with a “ for the text delimiter” and a “for the field delimiter”. Excel does not save with the double quotes. I strongly recommend using OpenOffice Calc for anything CSV-related in Magento.
After Magento reports the data as being correct, there is still a lot of room for error.
One of the most common issues I’ve seen with importing is small differences in similar characters. For example, the apostrophe vs the single quote. It might not look like a big difference, but importing the wrong character can end up showing a string of garbled text, which is sometimes the case with the single quote.
There are a couple of reasons why this happens:
- The CSV’s file encoding is wrong
Magento looks for Unicode UTF-8 character encoding. Character Encoding is essentially the way a character is interpreted by the computer. The different types of encoding determine how a character will appear visually.
“Hello!” in Unicode UTF-8 converted to Unicode will display as “效汬Ⅿ℡”.
Typically, you’ll see different countries use different encoding, but Magento is specifically using Unicode UTF-8. When you save your file, make sure it’s using this. - The characters are two different entities
The W3C has a list of characters that are used for typography.
https://www.w3.org/wiki/Common_HTML_entities_used_for_typography
These entities are typically used for aesthetic reasons. Along with this list, the W3C includes a list of preferred entities. HTML entities are a series of characters you can use as opposed to typing the character. This is usually done so the browser doesn’t interpret the single character as a command — for example, if you want to use a “<” sign and type it in just like that, an internet browser might think of it as an opening tag.
To stop this, you’d use the HTML entity, which, for this particular character, would be “<”. The browser will interpret this as “<” but not as an opening tag.
In short, Magento’s default Import/Export can seem finicky and difficult to work with. It’s great for bulk updating items without going through the trouble of installing a new extension. If you’re going to go this route, I recommend saving a “reference” CSV of a Magento export, since these are the fields it looks for and using that template to reference any default data you might want to keep. As long as your columns are the same as in your reference file, and the encoding is right, all you have to worry about is how the content (like Descriptions) is written. Like mentioned before, test your CSVs before updating a live site to make sure everything is coming in correctly.
Support Operations Manager
