Exporting Products

This tutorial shows how to export products of your commercetools project to a comma separated file (CSV) file via the "Product Export" command on IMPEX to be

  • imported in another project again, for example from staging project to production project or
  • to be used with other tools of your choice, like a spreadsheet editor, etc.

Example use case

Let's say we want to export the following products from the commercetools project to a CSV file:

  • red wines
  • white wines

Create template file

The "Product Export" command requires a template that defines which attributes of the Product should be exported.

How to define a Product type

Let's assume we define the ProductType "ExportProducts" in the commercetools project that has some attributes that are characteristic for wines: country and color and flavor.

  • The country attribute of LocalizableEnumType on which values for countries France and Germany are defined in English and German language, because the project shall support both languages.
  • The color attribute we have modeled as localized enum type too with the fixed values red and white, again for values in English and German language.
  • The flavor attribute is modeled as SetType of localizable enums to allow selection of multiple localized enum values for English and German language.

The flavor attribute has the following settings:

  • It is a SetType of localizable enums to allow selection of multiple localized enum values for English and German language
  • It has a constraint value of Combination unique
  • It is searchable and not required

We set all those attributes as searchable to get them included in the search index, otherwise we couldn't search for products based on these attributes later on. We are now ready to create the template for the export tool that is a comma separated file (CSV) file with one line describing the column headers for the file we'd like to get as a result.

For the example use case we are interested in the standard product information, like productType, variantid, sku, so we add those columns to the template file that should look like this for now:

productType, variantId, sku

Furthermore we'd like to export the information for the fields name and description. These attributes are localized strings that have values for English as well as German language. For both fields we'll dedicate a column for each language in the template file so that we'll have the columns name.en & name.de and description.en + description.de in the template file that should look like the following now:

productType, variantId, sku, name.en, name.de, description.en, description.de

Of course, we are interested in the custom attributes color, country and flavor that characterize the wines. For the country attribute we again provide a column for each language. These columns will be filled with the label of the LocalizedEnumValue that are defined for each language. For the color as well as the flavor attributes we put just one column without the language identifier. This way these columns will be filled with the key of the localized enum values, which is sufficient for the example here.

With these additions the template now has the following structure:

productType, variantId, sku, name.en, name.de, description.en, description.de, color, country.en, country.de, flavor

We also want to export the prices of each ProductVariant together with the taxCategory that is assigned to each Product. For this we add another two columns to the export template, one named prices and the other named tax.

The tax column is an exception in the naming convention of table headers. Usually, the column name is identical to the field name of the API object.

From ProductData we'd like to export the categories, the categoryOrderHints and the slug, whereby we are interested in the English slug only for this example. For this we add the slug.de column to the template. The product images we cannot export to a CSV file, but by adding the images column we'll get the URL's to the location where the product images are stored.

Our template finally has the following structure:

productType, variantId, sku, name.en, name.de, description.en, description.de, color, country.en, country.de, flavor, prices, tax, categories, categoryOrderHints, slug.en, images

Please make sure the terms are exactly written as the fields they represent, the product exporter is case sensitive.

Export all products

Now let's use this template with the IMPEX Product Export command by dropping it to the respective area on the IMPEX tool:

Image Drop Template File

and trigger the export. After successful export we can download the CSV file:

Image Export Successful

The exported file contains all the published products in your project. Meaning, if you had other products than the wines from the example you'll find those in this file too.
The following table shows the products the file should at least contain:

Exported example products

As we can see in this table, many values are easy to read, others need some more explanation on how to interpret them:

First thing to mention is that some values are listed in the rows for the master variant (variantId=1) of each product only.
Some information, such as productType, name, description, tax, categories, categoryOrderHints and slug are common to all the variants of a product and are thus not repeated for the other variants.

The product description in the example can contain HTML markup (<p>) around the text that has been introduced by the Merchant Center's text editor automatically.

The color column contains the key of the localized enum, in contrast to the origin columns, which contain the localized labels of the enum values instead.

In case more than one value has been given for the flavor attribute of a variant its values are separated by semicolons, as it is shown for dry;smoky and dry;fruity in the exported table.
The same semantics applies to the categories and the images column, listing all the categories the product belongs to and the images being assigned to each variant.

The prices in the exported file can be interpreted as follows:

Let's have a look at the Merchant Center how the prices of the Merlot product are modeled:

Image Product Prices Merlot 2012

Image Product Prices Merlot 2013

  • EUR 1499 shows the price in Euro cents for the master variant
  • EUR 999;EUR 0#gift-items represents the two prices for variant 2 separated by a semicolon. The first part stands for the price of 9.99 EUR for all countries and all customer groups, the part after the semicolon represents the price of 0 cents for the channel #gift-items

On the Riesling product there are prices defined for a particular country and for particular customer groups:

Image Product Prices Riesling 2012

Image Product Prices Riesling 2013

In the exported data for the Riesling product we can see how the price information country and the customer group is represented:

  • DE-EUR 999 Default stands for the price of 9.99 Euros for customers in Germany of the Default customer group
  • DE-EUR 599 LoyalCardHolders is indicating a price of 5.99 Euros for customers in Germany being a member of the LoyalCardHolders customer group.

These two prices appear in the same table cell of the prices column, separated by a semicolon.

In a real project the number of products can easily grow into thousands. In such cases you might want to export certain products only, and you would be happy to have a way to specify which products should be exported. The IMPEX tool allows you to specify a filter that is set in the where parameter of the tool.

Export certain products only

Let's say we have several product types in the project and we'd like to export products of a particular product type only.
For specifying the filter expression for where parameter, we need to determine the id of the product type we would like to export. This id we can find out easily in the Merchant Center. When we browse to the ExportProducts type we'll get the id from the URL of the page as marked in the red rectangle in the picture below:

Image Determine ProductType ID

Based on this example we'd use the id b2d637cd-19bc-4677-8c37-64ac31431686, but this will be different in your project.
To complete the filter expression we'll surround the id with the productType keyword, like so:

where=productType(id="b2d637cd-19bc-4677-8c37-64ac31431686")

This expression we'll now use as query string in the IMPEX Product Export command:

Image Filter By ProductType

After executing the product export with the same template used in section Export all products we will get the file with the content shown in the table, but nothing more.

Please keep in mind that only published products will be exported, staged-only products will not appear in the file.