Trim the string in datatable

Help Activities
,
1

I have 2 columns in datatable with string values
I want to remove all spaces in between & special characters from that two columns

Can anyone please suggest query

2

Hi @Demo_User

Can you provide the sample file or screen shot of the data, so that we can analyze your requirements?

Best Regards.

3

@Demo_User

Please try using this

  1. For eqch row in datatable on dt
  2. Use a assign activity as below
    Currentrow(0) = system.Text.RegularExpressions.Regex.Replace(currentrow(0),"[^A-Za-z0-9 ]","").Trim
  3. Use this again in assign
    Currentrow(1) = system.Text.RegularExpressions.Regex.Replace(currentrow(1),"[^A-Za-z0-9 ]","").Trim

Hope this helps

Cheers

4

Hi @Demo_User

You can use below approach as well

Use a “Replace” activity to remove spaces and special characters from each value in the two columns.
a. Drag and drop a “Replace” activity inside the “For Each Row” activity

.b. In the “Properties” panel of the “Replace” activity, set the following properties:

  • InputString: row(“ColumnName1”).ToString()
  • Pattern: “[^a-zA-Z0-9]”
  • Replacement: “”
    Replace “ColumnName1” with the actual name of the first column.

Repeat above for the second column,

5

Hi @Demo_User

You can try this approach using invoke code activity

  1. Read the excel file using read range and store the data in datatable dt1
  2. Use invoke code and in the argument side of invoke code, create a argument named dt1 with direction In/Out and pass value dt1 [got from step 1]
    Inside the invoke code, try this code (two lines of code)
dt1.AsEnumerable().ToList().ForEach(Sub(row) row("col1") = System.Text.RegularExpressions.Regex.Replace(row("col1").ToString.Trim,"[^A-Za-z0-9]","")

dt1.AsEnumerable().ToList().ForEach(Sub(row) row("col2") = System.Text.RegularExpressions.Regex.Replace(row("col2").ToString.Trim,"[^A-Za-z0-9]","")

Then outside the invoke code, use write range to write the updated dt1 to the excel file.

Note: Here col1, col2 are the representing the two columns from which u need to remove special characters and spaces, you can replace with desired column names

Hope this helps :slight_smile:

Thanks & Regards,
Nived N

1 Like
6

Exception has be thrown by target of an invocation … showing as invalid pattern

7

Hi @Demo_User

Just check my earlier response, i think issue was due to quotes

Just try the code once again and let me know if it worked as expected :slight_smile:

9

Could u share screenshot of invoke code and the arguments :slight_smile:

10

@Demo_User

did it worked for you :slight_smile:

11

Yes, thanks thats working

1 Like
12

Good to know :smiling_face:

Have a great day :smiling_face:

13

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