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:
- Transform existing columns by making them Nullable, Hide, Duplicate, or Delete the column.
- Add derived columns to add new rules to the data.
- 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.
- Click the icon and select the Dataset Schema option.
- In the Schema window, click the icon to edit the schema.
The data type of the columns can be changed based on the user requirement.
Nullable, Hide, , and options are available against each column.
- By default, all the columns are nullable. You can make a column non-nullable by unchecking the Nullable option.
- By default, all the columns are visible. You can hide the column by
checking the Hide option.
Click the icon, the below options appear.
- 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.
- Give an intuitive name to the new column in the Alias field.
- 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.
- 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.
- Click the icon to apply the conditions.
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 icon to perform operations on the selected column.
- Click the + icon to add an expression.
- To add more expressions, click the icon and select Insert Above and Insert Below options.
- The inbuilt functions are listed based on the datatype of the selected column.
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.
Reload CSV Dataset
To refresh the dataset after applying the changes, click the icon.
To reload the dataset, click the icon and select the Reload Dataset option.
Publish CSV Dataset
After the applied changes are verified, click the icon to publish the Dataset. The published data can now be further accessed in the automation tasks.