Update column based on pattern

Help Activities
1

Hello,

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

Please help me in this

Thanks in advance

2

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

3

Thanks Rahul for your response

But here I have given the sample example in real I have more pattern to identify.

I want this to be done with regex or something where I can optimize my code as well with minimal code…

Like

Update the data table colum where it found the correct match based on regex pattern

4

Hi @Harbansh_Singh ,

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.

5

Hi @supermanPunch

Thanks for your response…

There are 5 groups like fruit, vegie, cloths etc

Under every group we have 5-6 patterns to look

Like in fruit and vegi we have 3 pattern like FR,RT,RE etc and total data we need to update is 1k

Let me know if more details is required

6

Is there is any possibility to use regex in the enumeratable or in LINQ to update the columns

7

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

How to Update Data Column Values of a Data Table | Community Blog

8

Hi @ppr

Thanks for your response

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…

Like by passing regex in linq or any other way

Thanks

9

Have a check if Group names will help for the implementation (Kindly note the Case insensitive option was used)

grafik
grafik699×455 29.1 KB

grafik
grafik432×877 19.1 KB

And based on this building block we do have variant options for the implementation e.g. handling multiple additional groups.

For updating the column value we would recommend to start within a for each row

10

Yeah , thanks @ppr i will check and try to implement grouped regex.