There is a scenario where I need to read the excel file and based on some pattern I need to update the Data table column.
Below are the sample
ID Name Price
1 FR123-Sale 238
2 JY001 SALE 654
3 FR9877 SALE 136
4 RT76523 SALE 456
5 JY0034 - Sale 132
6 TK0876 SALE. 412
7 RT5778 Sale 122
8 TK9764 SALE 98
9 RE111 - SALE 102
10 TU221 - SALE 165
Rule
If Name column start from FR,RT,RE and ends with Sale then in the discription column need to update Fruit.
If Name column start from JY,TK,TJ and ends with Sale then in the discription column need to update Vegi.
Below is the output
ID Name Price Discription
1 FR123-Sale 238 Fruit
2 JY001 SALE 654 Vegi
3 FR9877 SALE 136 Fruit
4 RT76523 SALE 456 Fruit
5 JY0034 - Sale 132 Vegi
6 TK0876 SALE 412 Vegi
7 RT5778 Sale 122 Fruit
8 TK9764 SALE 98 Vegi
9 RE111 - SALE 102 Fruit
10 TU221 - SALE 165 Vegi
Hello, Here you can use a for each row in datatable activity and loop through each row of data.
CurrentRow(“Name”).ToString will give the Name.
Use a substring function to get the first 2 characters. (suppose the variable is “val1”)
Use IF statement to check whether the val1 is equal to FR,RT,RE, then use a Update Row Item activity to update in Description
Since we have the data already in a Structured form, that is an Excel/ Datatable, we strive towards using this form for querying or modifying the same data.
In General, We would not want to switch to a less Structured form and perform modifications to it.
However, Let us know how many different patterns do you need to Search for like the ones mentioned above. We would first require to analyse if Converting to String and Performing Regex is a better way than the Normal Updation methods for a Datatable.
for developing the regex pattern we would need the sample for the different cases, then we can help. Depending on the complexity maybe we can work with regex groups.
Otherwise we can setup a config table with the different patterns and the replacement value, so we can use this for the detection, and handle it more dynamic.
depends on some factors, but doable e.g. with the row ItemArray reconstruction approach
Based on the above 2 pattern for fruite and vegi i have created the regex expression and tested it with match activity it is retrieving the matched value
Regex for Fruit : ^(FR|RT|RE)[a-zA-Z0-9- ]*Sale
Here I want to need to update the Data table whoes Name column is matching this pattern.
There is a way I am using to updated the col. By iterating for every row… that’s matches the pattern
But is there is any other optimize way to do this…