Reorder Columns

The Reorder Columns transformation step lets you change the order of the columns in a file.

By maintaining a standardized and consistent column order in the files, you ensure that the data can be easily exported into the inventory system without manual adjustments or errors.

Step configuration

1. In the Data Transformation Studio, add the Reorder Columns step.
2. Indicate the columns to move.

3. From the Move columns dropdown list, select where to move the columns.
   The examples below show how each option moves the name column in a file with the following columns: country, email, name, surname

   • To beginning - The columns are moved to the beginning of the file (far left).
     Result: name, country, email, surname
   • To end - The columns are moved to the end of the file (far right).
     Result: country, email, surname, name
   • Before - The columns are moved immediately before (to the left of) the column you select. For example, if you select email:
     Result: country, name, email, surname
   • After - The columns are moved immediately after (to the right of) the column you select. For example, if you select surname:
     Result: country, email, surname, name
   • To position - The columns are moved to the position you enter. Positions are counted from 0, which is the first column. For example, if you enter 1:
     Result: country, name, email, surname

   If you move more than one column, they are moved in the order in which you added them to the rule. For an example, see Example of use.

4. To add more rules, click Add rule, and repeat steps 2-3.
   Rules are joined with the AND logical operator.
5. To define what happens if rows contain errors, from the dropdown list next to Add rule, select one of the error handling options.
6. Confirm by clicking Apply.
7. You can check the result in the Output tab.

Selecting columns

In each rule of the step, you indicate the columns on which the operation is performed.

1. In the header of the rule, click these and select one of the options:
   • these - the operation is performed on the columns you indicate.
   • all except these - the operation is performed on all columns except the ones you indicate.
2. From the first dropdown list in the rule, select how you want to indicate the columns:
   • Columns - from the Select column dropdown list, select one or more columns.
   • Where name - define a condition (a dynamic condition) that the column names must meet. Select a logical operator (Ends with, Starts with, Contain, Not contain, or Regex) and enter the value. For example, if you select Starts with and enter pro, the operation is performed on the columns whose names start with pro
     Column names are case-sensitive, for example, product doesn't match ProductCode. A regular expression must match the entire column name, for example, product.* matches productName, but product doesn't. To ignore the letter case in a regular expression, add (?i) at the beginning, for example, (?i)product.*

You can't combine dynamic conditions with the all except these option.

Dynamic conditions are especially helpful when you use a JSON file as sample data. When a JSON file contains an object deeper than the root object, its items are transferred to the transformation as separate columns named {object}.{parameter}. You can't add those columns from the Select column list, but you can include them with a dynamic condition, for example, by including columns whose names contain the name of the object they were created from.

Handle errors

You can define what happens with rows in which the step fails (for example, as a result of Jinja rendering). From the dropdown list next to Add rule, 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 is used in the Data Transformation Studio 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

In the following example, we will organize columns in a file so email, name, surname columns will be at the beginning of the file. The initial state of the file is as follows:

marketing_agreement,country,surname,name,email
false,USA,Doe,John,john.doe@example.com
true,USA,Smith,John,john.smith@example.com
false,England,Williams,Jane,j.williams@example.com
true,Wales,Brown,Rachel,rachel.brown@example.com

1. In the Data Transformation Studio, add sample data.
2. Click Add step and select Reorder Columns.
3. In the configuration of the step:
   1. Leave the these option at default.
   2. From the Select column dropdown list, select the columns in the preferred order, in this case: email, name, surname.
   3. Leave the Move columns option at default (To beginning).
4. Confirm the settings by clicking Apply.
   Result: In the Output tab, the email, name, and surname columns are at the beginning of the file.