Import inserts

This article presents advanced inserts that can be used in imports.

Changing values

You can replace the value in a column with another value.

To get a value from the CSV file, use the following syntax:

root['columnName']

The value is retrieved from the row that is the context of the action.

Example: change current value

The following replaces the value of paymentInfo:

  • if the value is cash, it is replaced with POS
  • any other value is replaced with WEB_DESKTOP
{% if root['paymentInfo'] == 'cash' %}POS{% else %}WEB_DESKTOP{% endif %}

where:

  • cash is the value to be replaced
  • POS and WEB_DESKTOP are the possible new values

Example: conditionally keep current value

The following code keeps cash as the value of paymentInfo, but replaces any other value with WEB_DESKTOP

{% if root['paymentInfo'] == 'cash' %}{{root['paymentInfo']}}{% else %}WEB_DESKTOP{% endif %}

Example: combining conditions

The following code replaces the value of paymentInfo:

  • cash is replaced with POS
  • online is replaced with WEB_DESKTOP
  • any other value is replaced with UNKNOWN
{% if root['paymentInfo'] == 'cash' %}POS{% elif root['paymentInfo'] == 'online' %}WEB_DESKTOP{% else %}UNKNOWN{% endif %}

Example: string replace

The following code takes the value of g:sale_price and replaces USD with an empty string, for example 256 USD becomes 256.

 {{ root["g:sale_price"]|replace(" USD", "") }}

Filling in empty values

If a row has no value (is an empty CSV cell), you can insert a value.

The following code replaces a missing value of paymentInfo with foo

{% if not root['paymentInfo'] %}foo{% else %}{{root['paymentInfo']}}{% endif %}

Event salt

Tip: This calculation is a part of Importing transactional data.

The event salt is a UUID generated on the basis of unique combination of at least two parameters. This allows you to avoid duplicating an event entry in the database if it is imported more than once.

  1. Select two or more parameters whose combination is unique.
    For example, you can use orderID and eventTimestamp. Even if one of them repeats for some reason, the chances of both being identical between two events are practically zero.

  2. In the import CSV, add an eventSalt column.

  3. In the eventSalt column, add the following insert (example according to step 1):

    {{root.orderId}}{{root.eventTimestamp}}

    where orderId and eventTimestamp are column names.

  4. When creating the import, map the eventSalt column as Event Salt.

Result: When the import is processed, the value combination in the eventSalt column is calculated into a UUID that is always the same. When other imports are performed and that UUID already exists in the database, the event is not imported as a duplicate.

Calculating revenue

Tip: This calculation is a part of Importing transactional data.

If the CSV import file does not have a column that calculates the total value of an imported transaction, you can use Jinja to perform that calculation. The column with the total value of the transaction must be mapped to Value and Revenue.

Important:

All items from the transaction must be contained in rows that immediately follow each other. Transactions are recognized by orderId and products are recognized by sku.

Examples of correct and incorrect order of items in an import
Example of correct and incorrect grouping of items according to the order they were included in

The following code uses the values from the productQuantityProduct and finalUnitPriceAmount to calculate the total value of an order.
The actual code must not include any line breaks, the example uses them for better readability.

{% set line_sums = [0] %}
{% for prod in root.products %}
{% if line_sums.append(prod["productQuantity"]|int*prod["finalUnitPriceAmount"]|replace(",", ".")|float) %}
{% endif %}
{% endfor %}
{{ line_sums|sum }}
😕

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.