Transform and import product data to a catalog

Published January 23, 2023
Modules
Difficulty
Selected Clients
mediaexpert logoeobuwie logo

In this use case, we will focus on the process of importing and transforming product data from an .XML file using data transformation. This method of data transformation is beneficial as it eliminates the need for the data to be in a predetermined, structured format.

In this use case, we will make the following modifications to the file:

  • Add 2 new columns:
    • discounted: this column will contain the true value for all products which have a value (price) assigned in the g:sale_price column.
    • percentage_discount: this column contains the percentage value of the discount based on g:price and g:sale_price columns.
  • Edit values: Replace the value of g:availability column from 1/0 to in stock/out of stock.

Input data in use case


In this use case, we use two files:

  • the complete .XML file in the Google Merchant Format which contains the full set of products. The file contains the following attributes: g:id,g:title,g:description,g:image_link,g:price,g:sale_price,g:availability.
  • the sample of the product data in the .XML format. To reproduce this scenario in your workspace, create a catalog and prepare the files.
    Note: If your product data is complete, you can skip transforming data. But if you need to modify the file with product data before an import to Synerise, you can modify the data in Automation > Data Transformation. To do so, create a sample of your product data and include all attributes you want to modify. If you miss the attributes in the sample file, but import the actual product data with them, the data will be imported as delivered in the actual file.
Example XML file

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
<channel>
<title>Your Store Name</title>
<link>https://www.yourstore.com</link>
<description>Your Store with clothes</description>

<item>
<g:id>1</g:id>
<g:title>Shirt</g:title>
<g:description>white cotton shirt with a round neck</g:description>
<g:link>https://www.yourstore.com/product-1</g:link>
<g:image_link>https://www.yourstore.com/product-1-image.jpg</g:image_link>
<g:price>19.99</g:price>
<g:sale_price>10.99</g:sale_price>
<g:availability>1</g:availability>
</item>

<item>
<g:id>2</g:id>
<g:title>Jeans</g:title>
<g:description>blue cotton jeans</g:description>
<g:link>https://www.yourstore.com/product-2</g:link>
<g:image_link>https://www.yourstore.com/product-2-image.jpg</g:image_link>
<g:price>29.99</g:price>
<g:sale_price>20.99</g:sale_price>
<g:availability>0</g:availability>
</item>
</channel>
</rss>

Process


In this use case, you will go through the following steps:

  1. Create a data transformation rule to transform the data in the sample file.
  2. Create a workflow to import the .XML file to Synerise.

Create a data transformation


In this part of the process, you define the rules of modifying data before sending it to the Synerise based on the sample file. Each of the following sub-steps describes the individual changes performed on the file. We will add follwing rules:

  • Add 2 new columns:
    • discounted: this column will contain the true value for all products which have a value (price) assigned in the g:sale_price column.
    • percentage_discount: this column contains the percentage value of the discount based on g:price and g:sale_price columns.
  • Edit values: Replace the value of g:availability column from β€œ1/0β€œ to β€œin stock/out of stockβ€œ
  1. Go to Automation icon Automation > Data Transformation > Create transformation.
  2. Enter the name of the transformation.
  3. Click Add input.

Before you proceed with selecting sample data and defining transformation rules, optionally, you can select a goal to help you structure the data. If you want to create a transformation diagram without a specific goal and you know the structure of the output data, skip this step.

Goals will suggest you the required data for the import into Synerise.

Add file with sample data

This node allows you to add a data sample. In further steps, you define how the data will be modified. Later, when this transformation is used in the Automation workflow, the system uses the rules created for the sample data as a pattern for modifying actual data.

  1. On the canvas, click the Add input node.
  2. On the pop-up, click Upload a new file or drag one here.
  3. Upload the .XML file created as the part of prerequisites.
  4. You can preview the file, then click Apply.

Add the new column

  1. On the Data Input node, click the grey dot.
  2. From the dropdown list, select Add column.
  3. Click the Add column node.
  4. In the configuration of the node:
    1. In the Add column field, enter the name of the column. In this use case, it’s discounted.
    2. From the dropdown list, select Dynamic value.
    3. In the Type value box, add the Jinja code, which adds the true value for all products with g:sale_price attribute in this new column. You can use the code presented below:
              {% if root["g:sale_price"] is defined %}true{% endif %}
              
    4. Leave Handle incomplete data at default (Skip row if error occurred) to skip missing or invalid data which may occur during transformation.
    5. Confirm by clicking Apply.

Add the new column

  1. On the Add column node, click THEN.
  2. From the dropdown list, select Add column.
  3. Click the Add column node.
  4. In the configuration of the node:
    1. In the Add column field, enter the name of the column. In this use case, it’s percentage_discount.
    2. From the dropdown list, select Dynamic value.
    3. In the Type value box, add the Jinja code, which counts the percentage value of the discount based on g:price and g:sale_price attributes and adds it to this new column. You can use the code presented below:
              {% if root["g:sale_price"] is defined %}{{ root["g:sale_price"]*100/root["g:price"] }}{% endif %}
             
    4. Leave Handle incomplete data at default (Skip row if error occurred) to skip missing or invalid data which may occur during transformation.
    5. Confirm by clicking Apply.

Edit values

  1. On the Add column node, click THEN.
  2. From the dropdown list, select Edit values.
  3. Click the Edit values node. In the configuration of the node:
  4. Click Add rule.
  5. Click Add column.
  6. From the dropdown list, select column name, in this case it’s g:availability.
  7. Set the Edit values values option to:
    1. Replacing
    2. Dynamic value.
  8. In the Type value field, enter the Jinja code which replaces the value of g:availability attribute from 1 and 0 to in stock/out of stock. You can use the code presented below:
            {% if root['g:availability'] == 1 %}in stock{% else %}out of stock{% endif %}
            
  9. Leave Handle incomplete data at default (Skip row if error occurred) to skip missing or invalid data which may occur during transformation.
  10. Confirm by clicking Apply.

Add the finishing node

This node lets you preview the output of the modifications to the sample data.

  1. On the Edit values node, click THEN.
  2. From the dropdown list, select Data Output.
  3. To preview the results, click the Data Output node.
    The preview of modifications to the file
    The preview of modifications to the file
  4. Close the preview
  5. In the upper right corner, click Save and publish.
    Result:
    The diagram of data transformation
    The diagram of data transformation

Create a workflow


The scenario for this use case describes a one-time import of the .XML file with a product database to Synerise.

  1. Go to Automation icon Automation > Workflows > New workflow.
  2. Enter the name of the workflow.

Define the launch date

  1. As the trigger node, add Scheduled Run.

  2. In the configuration of the node:

    1. Change the Run trigger option to one time.
    2. Select Immediately.
    3. Confirm by clicking Apply.
    The configuration of the Scheduled Run node
    The configuration of the Scheduled Run node

Select file to import

  1. Add a Local file node.
  2. In the configuration of the node:
    1. Upload the file.
    2. Confirm by clicking Apply.
Local file transfer
Local file transfer

Add data transformation node

  1. Add a Data transformation node.
  2. In the configuration of the node:
    1. Choose the name of the data transformation, which you have created in the previous part of the process.
    2. Confirm by clicking Apply.
Data transformation node
Data transformation node

Add import to catalog

  1. On the Data transformation node, click THEN.
  2. From the list that opens, select Synerise > Import to catalog.
  3. Open Import to catalog node.
  4. Choose the catalog from the list.
  5. As a primary key, choose g:id parameter.
  6. Click Apply.

Add the finishing node

  1. Add the End node.
  2. In the upper right corner, click Save & Run.
    The workflow configuration
    The workflow configuration

You can monitor the flow of the workflow in the Transformation logs tab. It contains information about the execution of the workflow.

The logs for the workflow
The logs for the workflow

Check the use case set up on the Synerise Demo workspace


You can check the data transformation and automation process directly in Synerise Demo workspace.

If you don’t have access to the Synerise Demo workspace, please leave your contact details in this form, and our representative will contact you shortly.

Read more


πŸ˜•

We are sorry to hear that

Thank you for helping improve out documentation. If you need help or have any questions, please consider contacting support.

πŸ˜‰

Awesome!

Thank you for helping improve out documentation. If you need help or have any questions, please consider contacting support.

Close modal icon Placeholder alt for modal to satisfy link checker