The method to use Replace in a DataTable help

Help Studio
1

I would like to know how to remove parentheses and whitespace from all the values in a DataTable while keeping the structure (rows and columns) intact. If I use System.Text.RegularExpressions.Regex.Replace(TestDT, "\s*\(.*?\)\s*", ""), I would need to convert it to a string, which would disrupt the rows and columns. Could you please provide a solution?
Thank you in advance for your response.

1 Like
2

Hi @22222222asas,

Based on the regex you provided refer the xaml attached below for the solution.

Test.xaml (8.4 KB)

The LINQ query iterates over each row in the DataTable and applies the Replace function to every item (cell) in the row, removing whitespaces and parentheses. It then adds the modified rows to a cloned DataTable, preserving the original structure but with the cleaned-up data.

Hope this helps , Thank you :grinning:

2 Likes
4

dt_test.AsEnumerable().Select(Function(row) dt_clone.LoadDataRow(row.ItemArray.Select(Function(item) item.ToString().Replace(" “, “”).Replace(”(“, “”).Replace(”)", “”)).ToArray(), False)).CopyToDataTable()

You can try this out instead of using regex.

2 Likes
5

@22222222asas
similar to

we can handle like

(From d In dtData.AsEnumerable
Let ra =d.ItemArray.Select(Function (x) If(isNothing(x), Nothing, Regex.Replace(x.toString,"YourPattern",""))).toArray
Select r = dtCorrected.Rows.Add(ra)).CopyToDataTable()

Maybe you can share some sample data with us, we could also check for simplification like a custom trim

1 Like
7

ensure that " is used for double quotes and not the inverted ones “” (e.g. coming from copy paste)

1 Like
8

@Shahazeer_Ummer_D

Thank you for your response! I used the Assign activity twice, and it worked perfectly. Have a great weekend!

9

@ppr
Thank you for your response!
The code you provided was perfect. Thank you!

10

Perfect, so you can close the topic by marking the solving post
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.