How to import products with combinations into PrestaShop: two files from one supplier spreadsheet

How PrestaShop imports products with combinations

A supplier’s spreadsheet for a wool rug arrives as six rows, one for each colour and size, with the variant typed into one cell three ways: Ochre / 120x170, OCHRE 200X290, Slate / 120 x 170cm. PrestaShop does not want six rugs. It wants one product with six combinations, taken in two imports.

To import products with combinations into PrestaShop, import the products first and the combinations second, both from Advanced Parameters > Import.¹² The products file has a row per product, and the combinations file a row per combination, with the product’s ID, its attributes as Name:Type:Position and its values as Value:Position, as in the sample file every installation includes.³¹

The order follows from what each file points at. A product points at its categories by ID, and “Categories are to be specified using their existing IDs (so you should have imported them first)”.¹ A combination points at its product the same way, in the sample’s first column, Product ID.³ Attributes come before the combinations: in the back office, “You cannot create combinations if you do not already have product attributes properly set in PrestaShop.”⁴ So set up the attributes and their values under Catalog > Attributes & Features and have the categories in place, then import the products, then the combinations.⁵

The import screen, and the files to start from

The first screen asks “What do you want to import?”, and Combinations is one of the choices; the Available fields panel then lists what its file should contain.¹ The page also offers a sample file for each type, stored “in the /docs/csv_import folder of your installation of PrestaShop”.¹ Build on them: the documentation asks for files that follow “the naming scheme and order of the presented fields”.¹

The other settings:

• File type. CSV, or since version 1.7 “.xls, .xlsx, .xlst, .ods, and .ots”, though with Excel “you cannot set the field or multiple value delimiters”.¹
• Language of the file. One language per file: “Data can only be imported for a single language at a time”.¹
• Separators and encoding. A semicolon between fields and a comma between multiple values, which PrestaShop suggests keeping, in a file saved as UTF-8 (or failing that ISO-8859-1).¹
• Force all ID numbers. “You can either keep the imported IDs, or let the importer auto-increment them.”¹ So the combinations file takes its product IDs either from your products file or from PrestaShop.

The second screen matches your columns to PrestaShop’s, six at a time; enter 1 in Rows to skip to leave out the header row, and save the mapping for the supplier’s next file.¹ It “makes sure that you don’t miss any required fields”, PrestaShop’s developer documentation says.⁶ A clean run ends at “100% validated” and “100% imported”; otherwise you get “a notice of all the encountered problems”.¹

The two files, column by column

The products file

PrestaShop’s sample products file has 68 columns.⁷ These are the ones a supplier file trips on:

• Categories (x,y,z...): category IDs, “separated with a comma (by default)”.¹
• Price tax excluded: a bare number, which “will use your store’s default currency”.¹
• Tax rules ID: a number from your shop, not a rate, because “You cannot directly apply a tax to a product; you can only apply tax rules.”⁸
• Reference #: your own code for the product as a whole; each combination can carry its own.⁴³
• Manufacturer: the brand. Brands “used to be called manufacturers in previous versions of PrestaShop”, and the sample keeps the old name.⁹⁷
• Weight: a number in the shop’s default weight unit, set on the Localization page.⁴
• Feature(Name:Value:Position): every specification, in one cell.

The sample’s header names three parts of the Feature column. The documentation names four, featurename:value:position:customizedvalue, with positions “starting at 1”, commas between features and no colon or comma inside a part: “you cannot use a colon (:) or commas (,)”.⁷¹ The last part is 1 for a customised value, one “not selectable from a list of predefined values”, and 0 for one from that list.¹ With 0, a name or value that does not exist yet is not refused: “the feature and/or value will be added for you.”¹

The combinations file

The sample combinations file has 24 columns and marks three with an asterisk: Product ID, Attribute (Name:Type:Position) and Value (Value:Position).³ The import documentation does not describe the last two, so the sample’s rows are the guide: Color:color:0, Disk space:select:1 in the attribute column and Blue:0, 16GB:1 in the value column, one value per attribute in the same order, with commas between pairs.³ The type is the attribute’s type, which the attributes page describes as a choice between “a drop-down list, a radio button list, or a color (or texture) picker”; the sample uses color and select, and counts positions from 0.⁵³

A price list gives each size its own price, so Impact on price needs arithmetic. PrestaShop wants the difference: “fill in the field that appears with the value of that impact (e.g. ‘-2’ if the price should be decreased by 2)”.⁴ In its sample files, the iPod touch is priced 240, and its 8GB, 16GB and 32GB combinations carry 0, 75 and 150, with the 8GB as the default.⁷³ Impact on weight works the same way, from the product’s weight.⁴

Where supplier files break it

Supplier files are laid out for ordering, one row per item picked. Six things in them break PrestaShop’s two files.

Every colour and size as a product of its own. FENWALD WOOL RUG OCHRE 120X170CM and FENWALD WOOL RUG SLATE 200X290CM are one rug, but imported as they stand they are six products. For versions that differ by colour, capacity or size, PrestaShop says “you should create a product combination for the current product rather than duplicate it X times.”⁴ Variants: size and colour covers where one product ends and the next begins.

Colour and size in one cell, spelt several ways. Ochre / 120x170 and OCHRE 200X290 have to become value cells such as Ochre:0, 120 × 170 cm:1, with each size spelt one way on every row and one value per attribute: “it is impossible to have both ‘Color: Blue’ and ‘Color: Red’ in your pairs”.⁴ Spelling matters past the import, too. A Type column on one bathroom retailer’s 329 baths held 12 spellings of 8 real types, and each extra spelling became one more box for shoppers to tick. Normalising attribute values shows how to fold them.

A full price and weight for every size. The price list says £75.00, £125.00 and £190.00 + VAT, with weights of 4200g, 7000g and 10.3kg. The product takes one price, without tax, and the larger sizes take the differences, 50.00 and 115.00. The weights, once in the shop’s unit, become 4.2 on the product and 2.8 and 6.1 on the others.⁷¹⁴

Separators inside the text. A description reading Hand-tufted; reversible; low shedding holds two field separators. PrestaShop says to remove them or choose another separator, and “There must be the same number of columns on each row.”¹ In a Feature cell, the comma in Material: 100% wool, pile 12mm starts a second feature, because colons and commas “will always be interpreted as so”.¹ Keep both out of attribute values too, where the sample uses them as separators.³

Barcodes Excel has rewritten. Each combination can carry its own EAN13, “the product’s 13-digit international article number”.⁴³ Excel “automatically removes leading zeros, and converts large numbers to scientific notation”, so 5012345670010 comes to read 5.01235E+12 and a barcode loses any zero it starts with.¹¹ GTIN, EAN, UPC and MPN explained covers getting the digits back.

Names where PrestaShop wants numbers and links. Home > Rugs > Wool Rugs has to become the IDs your categories already have, such as 3,27, and the file name fen-och-120.jpg an absolute link a browser can open.¹ Mapping supplier categories covers fitting the supplier’s tree to yours.

One supplier product, before and after

Here is the first row, the ochre rug in 120 × 170 cm, against the products file. Its colour and size leave the name for the combinations.

All six rows then become combinations of product 1042, each with the attribute cell Colour:color:0, Size:select:1, which the table leaves out with the product ID. Colour stands for your shop’s own name for the attribute.

The 1042 is whichever ID the rug ends up with: the one in your products file if you import with Force all ID numbers, or the one PrestaShop gives it if you do not.¹

How to build the two files, step by step

1. Set up the attributes and their values, spelt as the files spell them, and check that every category exists and has an ID.⁵¹
2. Open the supplier file with barcodes as text. Use the Data tab’s From Text/CSV and set those columns to Text as they load; formatting them as Text afterwards “will not change numbers that have already been entered”.¹¹
3. Group the rows into products. Rows that differ only by colour or size are one product, and their differences become its combinations.⁴
4. Build both files on the samples’ headers: one row per product, then one per combination, with prices and weights as differences and one default.⁷³⁴
5. Clear the separators and save as UTF-8. No semicolons in text, no colons or commas inside feature or attribute values, and the same number of columns on every row.¹
6. Import the products, then the combinations. Leave Delete all unticked, and tick Force all ID numbers only if the combinations file uses the IDs in your products file.¹
7. Try a few products first, and read the notice of problems if a run stops short of “100% imported”.¹

When it is not obvious

Attribute or feature? If shoppers choose it, it is an attribute; if it is the same on every colour and size, it is a feature. In PrestaShop’s words, attributes “make it possible to select among product combinations”, while features “remain the same throughout the product’s variations”.⁵¹⁰ It is the first judgment in product data enrichment, and the file follows from it.

Two colours in one rug. An ochre and slate rug cannot take two Colour values in one combination. PrestaShop’s answer is two attributes, “for instance, ‘Primary color’ and ‘Secondary color’”.⁴

Where the sample and the documentation disagree. In two places that matter, both in the products file. The sample’s Feature header names three parts, where the documentation names four, as in its example Date of Origin::1:1, so write four. And the sample writes category names, such as Accessories, where the documentation asks for existing IDs and does not say what the importer does with a name, so write IDs.⁷¹

Before you click Import

All of this happens in a spreadsheet, before PrestaShop reads a row, and it is the work behind the files we prepare from supplier spreadsheets for PrestaShop. Each SKU or barcode is matched to the real product, gaps are filled with a link to each value’s source, spellings are folded onto one value, measures are converted to one unit and every item is filed into your own category tree. The files come back laid out for PrestaShop’s own importer, the products first and then their sizes and colours as combinations, for you to check before anything is imported. That is RefynData’s product data enrichment service.

However the files are made, import a handful of products and their combinations before the whole range. Then keep the column map and the list of spellings: the next delivery will bring the same OCHRE 200X290.