Magento product import: from a supplier spreadsheet to a CSV that passes Check Data

What a Magento product import expects

A supplier’s spreadsheet lists a linen duvet cover on one row, with Sizes: Double/King in one cell and £69/£79 in the next. A Magento product import cannot take that row as it stands. It wants a row for each size, each a simple product with its own SKU, and a configurable product row that holds them together, with a configurable_variations cell reading sku=LDC-GRY-D,size=Double|sku=LDC-GRY-K,size=King.⁶⁷

The import runs from one screen in Magento Open Source and Adobe Commerce alike: System > Data Transfer > Import, with Entity Type set to Products.¹¹¹ It reads a CSV, or a ZIP holding one, and “Each column heading corresponds to the Attribute Code of the field”.³ In Adobe’s attribute reference, “the sku is the only required value”, and a file can carry “only the columns with changes”.²

A new product needs more. Validation checks that every attribute the store requires has a value, and Adobe’s walkthrough for a simple product asks for a product type, an attribute set, a Product Name, a SKU and a Price.⁴¹⁰ In the file, those are product_type, attribute_set_code, name, sku and price.²

So the work is in the spreadsheet, not on the import screen. It is the Magento end of product data enrichment.

The Import page and what Check Data checks

Adobe’s steps run in order: set Entity Type to Products, click Download Sample File, choose the behaviour and what happens on an error, choose the file, click Check Data and, if the file is valid, Import.¹

Check Data is the step that matters. It checks that each heading matches an attribute, that each value meets its attribute’s data type (decimal, integer, varchar, text or datetime), that values for drop-down and multiple select attributes “exist in the defined set”, and that a new product has its required values.⁴

Adobe’s Import API reference, for a REST import exclusive to Adobe Commerce that it says mirrors the Admin’s, is plain about skipped errors: valid rows are imported, but once errors pass the allowed count, “the import is halted (even valid rows will not be processed)”.¹² Import History, under the same menu, keeps each run with an error file to download.¹

Add/Update, Replace and Delete

Rows are matched to products by SKU, and the behaviour decides what a match does.¹

Add/Update is the one for a supplier file, with two habits to know. It creates what it does not recognise: “New product categories that are specified in the import file are created automatically”, and new SKUs are too.¹ It also cannot take a value out of a field that holds several: “Multiple field values, such as websites or categories, cannot be removed using the Add/Update import behavior.”¹ A product filed in the wrong category stays there after the next import.

Replace is not a stronger update: a matching product loses “all fields, including the SKU” and is created again from the row.¹ On a large catalogue, leave categories and url_key out of any update that is not changing them, because either column makes the import regenerate URL rewrites “for every product in the file”.¹

The columns that matter

In Adobe’s attribute reference, the sku “should be the first column, but the order of the rest of the attributes doesn’t matter”.²

One rule there depends on which Adobe page you read. For a product in several categories, the attribute reference says to “separate each path with a pipe”.² The CSV page, the Import API reference and the Import page’s own default split them with the multiple value separator, a comma, and Magento’s sample import file writes “Default Category/Gear,Default Category/Gear/Fitness Equipment”.³¹²¹¹³ Export a product in two categories and copy what your store writes.

Where supplier files break it

These faults all arrive in the supplier’s spreadsheet, and Check Data names most of them.

Headings in the supplier’s words. Product Name, Colour, RRP. Each heading is read as an attribute code, and codes are created “in lowercase characters and without spaces”: name, color, price.³⁸ A heading that matches nothing loses its column: the attribute “is not created and its values are not imported”, and “Imported attribute %attribute name% does not exist in the system.” is among Adobe’s validation messages.¹⁴ Supplier data onboarding sets out the map.

Category paths from the supplier’s tree. Bed Linen > Pillowcases. The path Magento wants “includes the root category, followed by a forward slash (/) between each level”: Default Category/Bedding/Pillowcases.³ Under Add/Update an unknown path is created rather than refused, so a misspelt Beding becomes a new branch of your tree.¹

Sprawl is quicker to build than to undo: our own first pass spread one bathroom retailer’s 62 wall panels over 15 categories, sofas among them, before we brought them into one range. Mapping supplier categories comes before the path syntax.

Values the attribute does not list. The store’s color attribute lists Grey; the supplier writes Gray, Gry and Grey/Charcoal. Values for drop-down and multiple select attributes are checked against the attribute’s list, and “Value is not allowed. Attribute value does not exist in the system.” is among the messages.⁴ Fold spellings onto the store’s values first, as normalising attribute values describes, and add any genuinely new value to the attribute, under Stores > Attributes > Product, before the import.⁶

Symbols and units in number columns. £29.00 in price, 450g in weight. Each value is tested against its attribute’s data type, and Adobe’s sample file writes price and weight as bare numbers, 14 and 1.23.⁴¹³ The unit is not in the file, so convert every weight to the store’s Weight Unit, lbs or kgs, before the unit comes off.⁹

Images the import cannot reach. A thumbnail, a dead link, or a file name with no file behind it. An image on the server has to be in var/import/images, or a subfolder of it, before the import; one elsewhere is written as a full URL.⁵ Each failure has its own message: “Imported resource (image) could not be downloaded from external resource due to timeout or access permissions.” and “Imported resource (image) does not exist in the local media storage.”⁴

Codes the spreadsheet has changed. Excel “automatically removes leading zeros”, and Microsoft names product codes among the values it does it to.¹⁴ Because Add/Update matches on SKU and creates any SKU it does not know, a supplier’s 004512 saved as 4512 comes in as a new product instead of updating yours.¹ Adobe also warns that Excel “inserts additional characters into the column header of the CSV file”.³

Before and after: one simple product, one configurable

First, a pillowcase pair from the same file, a product with no sizes.

In stock is the one cell that needs a person: it is not a quantity, and no rule makes it one.

The duvet cover changes shape. The supplier sent one row, LDC-GRY, with Sizes: Double/King and £69/£79; Magento wants “a separate row for each simple product variation, and one row for the configurable product”.⁶

The configurable row also carries configurable_variations: sku=LDC-GRY-D,size=Double|sku=LDC-GRY-K,size=King. “Each SKU is separated by a pipe symbol (|), and attributes are separated by a comma”, each written as its code, size, “rather than the attribute label”.⁶ Not Visible Individually keeps a simple product out of listings, “although it might be available as a variation of another product”.²

Three things decide whether the rows join up. Two are in the attribute. The varying attribute “must have a global scope”, must require shoppers to choose a value, must be a Dropdown, Visual Swatch or Text Swatch, and must be in the product’s attribute set.⁷ Double and King must already be on its list.⁴

The third is names. Give each simple product one of its own: the default url_key is made from the name, “URL key for specified store already exists” is among Adobe’s messages, and Adobe’s example changes sku, name, url_key and additional_attributes on every new variation.²⁴⁶ Variants: size and colour covers what counts as a variation.

Preparing the file, step by step

1. Export before you build. Export a few of your own products, a configurable one among them, and keep the header row: Adobe’s advice is to “first export the data from your store as a CSV file”.³
2. Open the supplier’s file without letting Excel convert it. Load it through Data > From Text/CSV and set the code columns to Text, as Microsoft describes.¹⁴
3. Map every heading to an attribute code, or to nothing, and create any missing attribute, with its options, first: the import cannot add one.³⁸
4. Give every row the basics that Adobe’s sample file gives its simple products: sku, attribute_set_code, product_type, product_websites, name, price, product_online and visibility.¹³
5. Rewrite the values. Categories become whole paths from the root, values are folded onto each attribute’s list, compound cells such as Colour: GREY / Fabric: 100% linen are split, and weights are converted to the store’s Weight Unit before symbols and units come off.³⁴⁹
6. Lay sizes out as simple rows and one configurable row, with configurable_variations naming every simple product.⁶
7. Put images where the import can reach them. Server files go into var/import/images and arrive in a second file that “must include only the sku column and the related image columns”; images elsewhere go in as full URLs.⁵
8. Save as UTF-8 and run Check Data on a few products first. Adobe supports UTF-8 and Windows-1252, and warns that a UTF-8 file with a byte order mark may not be recognised. After configurable products, refresh the invalid caches under Cache Management.⁴¹⁶

What to do next

Part of that work is what RefynData’s product data enrichment service does with a supplier file: each SKU or barcode is matched to the real product, every item is filed into your own category tree, compound cells are split, spellings are folded onto one value, measures are converted to one unit and the full-size original is found behind each thumbnail, with every value linked to its source. We prepare supplier spreadsheets for Magento: the file comes back laid out for Magento’s own importer, and nothing goes live without your sign-off.

Export a product of each kind first and build on its header row. Run Check Data on a handful of products before the rest, and keep the map from the supplier’s headings to your attribute codes: the next delivery will bring the same headings and the same Sizes: Double/King.