"Regex Replace" node

If you need to collectively edit values in the columns, you can use Regex Replace node. It allows you to replace a group of values that share the same pattern with a new value or just a part of the value.

Node configuration

1. Click Add rule.
2. Select one of the two available options:
   • Include these - the default; it allows you to select the columns that will be considered.
   • Include all except these - this option excludes values that match the regular expression in a selected column.
3. Click Add column.
4. From the dropdown list, select the columns whose values you want to transform.
5. In the Regular expression field, enter the regular expression formula.
6. From the Replace dropdown list, select one of the options:
   • Matching pattern - only the part of the value which matches the regular expression is replaced.
   • Entire value in the cell - the entire content of the cell is replaced in one of the following ways:
     • Static value - when you select this option, all values in matching cells are replaced with a defined string.
       Important: If you leave an empty field for the Static value option, the values in the column will be empty strings.
     • Dynamic value - when you select this option, all values matching cells are replaced with the value which is the result of an insert (Jinjava). Full list of inserts is available here.
     • No value - when you select this option, all values in matching cells are removed and the cells remain empty.
       Important: If you choose the No value option, the values in the column will be null. It means that if a corresponding attribute exists in the database, it will be deleted.
     • UUID - when you select this option, all values in matching cells are replaced with a randomly generated UUID v4 (unique for each row).
     • Current date - when you select this option, all values in matching cells are replaced with the current date (the date and time are generated on an ongoing basis during the transformation).
7. In the Type value field, enter the value, insert, date format, or regular expression you want to replace the original value with.
8. To add more rules, click Add rule and repeat steps from 2 to 7.
   
   Tip: You can preview the results in the Output data tab.
9. To define what happens if rows contain errors, select one of the options available in the Error handling section.
10. Click Apply.

Current date option

Using the Current date option, you can enrich your data with a dynamically injected date and time which can be used, for example, to build an event salt. To define a rule with Current date:

1. Select a timezone.
2. Select one of the available formats:
   • ISO (for example 2022-10-05T05:55:16.786596Z)
   • Timestamp (for example 1664949342323)
   • MMM d, YYYY (for example Oct 5, 2022)
   • MMM d, YYYY, HH:mm:ss (for example Oct 5, 2022, 05:57:14)
   • YYYY-MM-dd HH:mm (for example 2022-10-05 05:57)
   • Custom - you can create your own date and time format.

Custom date format

You can use the following elements to build a custom format. The example values use October 05, 2022 as the date.

• MMM - name of month (Oct)
• MM - number of month (10)
• DD - day of year (278)
• dd - day of month (05)
• YYYY - year (2022)
• YY - year (22)

Handle errors

You can define what to do with failed values (for example, as a result of Jinja rendering or in other cases) in cells where data type modification failed. In the Error handling section, select one of the following options:

• Skip row if error occurred - It omits the row when an exception occurs.
• Skip if result is null or empty string - It omits rows with empty strings and null (which may be the result of rendering Jinja)
• Stop further transformation - The transformation stops at the row with an error. The data before the line that caused the error is transformed (and imported if the transformation rule is used in the Data Transformation node).
• Insert null if error occurred - Wrong values are replaced with null. If you use such a transformation for the import of profiles, the existing value of an attribute is cleared and replaced with the null value.
• Insert empty string if error occurred - Wrong value is removed and the cell remains empty.

Example of use

You can replace the part of the domain in the email addresses of your customers - from .com to .co.uk. You can use this transformation in the further processes, for example, in importing customers to Synerise.

1. Start the transformation with the Data Input node.
2. Click the node.
3. Upload a file on which you want to perform data transformation.
4. Add the Regex Replace node and join it with the Data Output node.
5. In the configuration of the Regex Replace node:
   1. Click Add rule.
   2. Select Include these
   3. Select the column with email addresses.
   4. In the Expression field, enter \.com$
   5. From the Replace dropdown list, select Matching pattern.
   6. In the Value field, enter .co.uk

      

6. Click Apply.
7. Add the Data Output node.
8. Join the node with the Regex Replace node.