Those familiar with Magento will know that the platform has two methods of creating products: manually filling in information in the back-end and running a product import with spreadsheets. Now, the preferred method is to use the native import feature on Magento, but the process is not a simple one. The product spreadsheet must be flawless to run a successful import, otherwise Magento will spit out error messages. Cleaning up the spreadsheet is the easiest way to make the import process smooth. Before you attempt an import and troubleshoot, take a look at the following:
This one is difficult to detect, because it’s not easy combing through the spreadsheet to find an invalid character. What can cause an invalid character? Well, Excel, and to an extent Open Office, have issue with certain characters, such as apostrophes and commas. If data is moved between the two platforms or saved in the wrong format, the data can get messed up. In most cases, invalid characters appear in descriptions and short descriptions. Take a moment and glance over these columns. You don’t need to read every single cell of every single row. Just scan over the columns and see if anything sticks out or grabs your attention.
Look at Attribute Values
Doing this will be slightly different between M1 and M2, but the concept is the same. Most attributes in the back-end will have values attached to them (i.e. the “color” attribute can have Blue, Red, etc. as values). When these values are attached to a specific item, the attribute code and the corresponding values will be attached to the product on the spreadsheet.
Now, the main difference between M1 and M2 sheets boils down to this: the attribute codes and values are all stored in one cell of one column on a Magento 2 sheet, while each attribute gets its own column one a M1. Both versions of Magento, however, require the values and codes to be exactly how they are listed in the back-end. If the “color” attribute value is “Blue” in the back-end and listed as “blue” in the sheet, Magento will not accept it in the import. Browse through your sheet and make sure the values match those listed in the back-end.
Okay, this isn’t to say you need to breakdown every aspect of a sheet, but it is a good idea to take a look at the formatting. Take a look at some configurable products to see if the simple SKUs are listed in the correct columns. Make sure the names and attribute sets are correct. Avoid using any extra spacing (see previous tip). If you’re going to keep track of inventory, ensure accurate quantities are filled in. Not every column and cell needs to be filled in, but the format should be sound.
When you think it’s ready, start the import process. Don’t be alarmed if a few errors pop-up after you click “Check Data.” It’s rare to have a flawless import on the first go. Don’t panic, fix any errors the system flags, and tighten up the sheet. Over time, you will get the hang of things and will have the know-how to avoid errors. Until you are a product import master, just follow these tips and learn from your mistakes.