Trim datatable column

Help
1

Hie guys.
I have an excel with 2 columns name and age. with age having details like #A20#, #A3# etc. i want to trim characters on age leaving details which are in between A and # for example #A20# will be 20. what i have done so far is
-read range and used for each row
-used assign row.Item(1).toString which is age column
-txtvalue.Substring(1,4) to trim

My question is is there any way fast to trim the details because its taking too long for 200 rows. and also how can i write the value i have trimmed on that same cell.
TRIM.xlsx (7.9 KB)

2

@Tapiwa

Try below Linq Query.

    newDT = DT.AsEnumerable.Where(Function(x) x("ColumnName").ToString.Substring(1,4)).CopyToDataTable

Then use Write Range activity to write into Excel file and pass newDT to it.

2 Likes
3

Option Strict on disallows implicit conversions from String to Boolean

5

If it is in a datatable, you can do a regex to get only the digits (if you are sure that the digitswill always be the age, nothing else). Add an assign activity in a “For Each Row” loop:

row("Column2") = system.Text.RegularExpressions.Regex.Replace(row("Column2").ToString,"(\D)*","")

This should find any non-digit in the column, and replace it with an empty string

1 Like
6

I there any faster means. It takes a while while running 30000 rows

7

Assuming your original datatable is called originalDT.
In a first assign, set newDT to originalDT.clone().
Then in a second assign, set new DT to originalDT.AsEnumerable.Select(Function(x) modifiedDT.LoadDataRow(New Object() {x(0), x(1).ToString.Split({“#A”,“#”}, StringSplitOptions.none)(1)}, False)).CopyToDataTable

This also assumes name is in your first column, and age is in the second

2 Likes
8

Hi, Magali_Philippe

Can you please explain me how its work in assing activity I am getting array index outofbond exception.

9

Hi, Magali_Philippe

Can you please explain me how its work in assing activity I am getting array index outofbond exception.

10

Hi ,friend

You got the above solution can you pls share me the work flow to trim the datatable column

11

Hi @vivek.gupta

PFA the working solution. Trim_Column.xaml (8.1 KB) .Hope this help.