Search

Transform CSV Datasets

Articles

The data in the CSV Dataset can be cleansed or transformed as needed to use in the automation process. The following transformations can be done to make the data more usable:

  1. Transform existing columns by making them Nullable, Hide, Duplicate, or Delete the column.
  2. Add derived columns to add new rules to the data.
  3. Transform the data in the existing columns using expressions.

Transform Existing Column

You can manipulate the existing columns in the CSV Dataset to clean-up the data.

  1. Click the Image description icon and select the Dataset Schema option. Image description
  2. In the Schema window, click the Image description icon to edit the schema. Image description

    The data type of the columns can be changed based on the user requirement.

  3. Nullable, Hide, Image description, and Image description options are available against each column. Image description

    1. By default, all the columns are nullable. You can make a column non-nullable by unchecking the Nullable option.
    2. By default, all the columns are visible. You can hide the column by checking the Hide option.
    3. Click the Image description icon, the below options appear. Image description

      • Duplicate Column: Duplicate the selected column and manipulate it using the expressions.
      • Add Expression Above
      • Add Expression Below
      • Delete Column: Delete the selected column.

Adding a Derived Column

You can create a derived column to the left or right of the existing column using either option Add Expression Above or Add Expression Below to transform the column data using an expression.

  1. Give an intuitive name to the new column in the Alias field.
  2. Specify the expression. Two types of expressions are available.
    • String Expression: Specify the string manipulations in the Expression field.

      Create a new column Discount using expression Fare - FinalFare.


      Image description
    • If Expression: Specify the conditions to be checked and actions to be taken in the IF, THEN, and ELSE fields.

      Create a new column FinalFare with a discounted fare calculated based on the condition,
      If the value in the existing column Fare is greater than 80000 then 20% discount is applied. Else 10% discount is applied.


      Image description
  3. Click the Image description icon to apply the conditions. Image description

    To provide column name as input, enter the column name as is.
    To provide input as a string, give the text in single quotes.

Transform Data within Column

You can transform the data in a column by performing various operations on the column data using the inbuilt expressions.

Click the Image description icon to perform operations on the selected column.

  1. Click the + icon to add an expression.
  2. To add more expressions, click the Image description icon and select Insert Above and Insert Below options.
    Image description
  3. The inbuilt functions are listed based on the datatype of the selected column. Image description The following are the available categories of expressions:

In addition, you can also write your own expression as desired using the Expression option shown below. Image description

Reload CSV Dataset

To refresh the dataset after applying the changes, click the Image description icon.
To reload the dataset, click the Image description icon and select the Reload Dataset option. Image description

Publish CSV Dataset

After the applied changes are verified, click the Image description icon to publish the Dataset. The published data can now be further accessed in the automation tasks.

Did you find what you were looking for?