How to export Products from the commercetools platform with the IMPEX tool?
- imported in another project again, e.g. from staging project to production project or
- to be used with other tools of your choice, like a spreadsheet editor, etc.
These tutorial will work in the same way for the ↗ Impex Tools and API Playground US .
- Example use case
- Create template file
- Export all products
- Export certain products only
Example use case
Let’s say we want to export following products from our CTP project to a CSV file:
- red wines
- white wines
Create template file
The ↗ Product Export command requires a template that defines which attributes of our Product should be exported.
How to define a Product type
Let’s assume we define the ProductType “ExportProducts” in our CTP project that has some attributes that are characteristic for wines:
color and flavor.
countryattribute of LocalizableEnumType on which values for countries France and Germany are defined in English and German language, because our project shall support both languages.
colorattribute we have modeled as localized enum type too with the fixed values
white, again for values in English and German language.
flavorattribute is modeled as SetType of localizable enums to allow selection of multiple localized enum values for English and German language.
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 our 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 our example use case we are interested in the standard product information, like
sku, so we add those columns to the template file that should look like this for now:
Furthermore we’d like to export the information for the fields
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 our template file so that we’ll have the columns
description.de in our template file that should look like the following now:
Of course, we are interested in our custom attributes
flavor that characterize our 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 our example here.
With these additions our template now has the following structure:
We also want to export the
prices of each ProductVariant together with the
taxCategory that is assigned to each Product. So we add another two columns to the export template, one named
prices and the other named
tax column is an exception in the naming convention of table headers. Normally, the column name is identical to the field name of the API object.
From ProductData we’d like to export 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 our 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:
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 ↗ Product Export command by dropping it to the respective area on the IMPEX tool:
and trigger the export. After successful export we can download the CSV file:
The exported file contains all the published products in your project. Meaning, if you had other products than the wines from our example you’ll find those in this file too.
The following table shows the products the file should at least contain:
Exported example products
|ExportProducts||1||merlot-2012||merlot||Merlot||<p>The classic red wine from France.</p>||<p>Der Klassiker aus Frankreich.</p>||red||France||Frankreich||dry||EUR 1499||Standard tax category||drinks;wines;red-wines;french-wines||0.3||merlot||https://c31361.ssl.cf3.rackcdn.com/merlot2012-1.jpg;https://c31361.ssl.cf3.rackcdn.com/merlot2012-2.jpg|
|2||merlot-2013||red||France||Frankreich||dry;smoky||EUR 999;EUR 0#gift-items||https://c31361.ssl.cf3.rackcdn.com/merlot-2013-1.jpg;https://c31361.ssl.cf3.rackcdn.com/merlot-2013-2.jpg|
|ExportProducts||1||riesling-2012||riesling||Riesling||<p>The classic white wine from Germany.</p>||<p>Der Klassiker aus Deutschland.</p>||white||Germany||Deutschland||dry||DE-EUR 999 Default||Standard tax category||wines;drinks;white-wines;german-wines||0.24||riesling||https://c31361.ssl.cf3.rackcdn.com/riesling-2012-1.jpg|
|2||riesling-2013||white||Germany||Deutschland||dry;fruity||DE-EUR 799 Default||https://c31361.ssl.cf3.rackcdn.com/riesling_2012-1.png;https://c31361.ssl.cf3.rackcdn.com/riesling_2012-2.png|
As we can see, 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
slug are common to all the variants of a product and are thus not repeated for the other variants.
description in the example contain some HTML markup (<p>) around the text, that has been introduced by the Admin Center’s text editor automatically.
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;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.
prices in the exported file can be interpreted as follows:
Let’s have a look at the ↗ Admin Center EU how the prices of the Merlot product are modeled:
We can find the respective entries in the table rows one and two:
EUR 1499shows the price in Euro cents for the master variant
EUR 999;EUR 0#gift-itemsrepresents the two prices for variant 2 separated by a semicolon. The first part stands for the price of
9.99 EURfor all countries and all customer groups, the part after the semicolon represents the price of
0cents for the channel
On the Riesling product there are prices defined for a particular country and for particular customer groups:
In the exported data for our Riesling product we can see how the price information country and the customer group is represented:
DE-EUR 999 Defaultstands for the price of
9.99 Eurosfor customers in
DE-EUR 599 LoyalCardHoldersis indicating a price of
5.99 Eurosfor customers in
Germanybeing a member of the
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 our 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 ↗ Admin Center EU
. 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:
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:
This expression we’ll now use as query string in the ↗ Product Export command :
Please keep in mind that only published products will be exported, staged-only products will not appear in the file.