Remove preceding and following spaces in excel

In my automation, I need to check and remove any preceding and following spaces from the cell values in a downloaded excel file.

The excel contains 2k records and it’s taking too much time to iterate through each row’s cells and trim the cell value.

Can someone suggest me quicker method. I tried Replace activity. That too is taking time.


Usually for 2k records it won’t take more than 5 seconds with for each row activity being single column
I believe it would have taken more time considering the entire datatable

In that case if you would like to do it linq query then the below expression would help you resolve this

Use a assign activity like this

dt = (From a In dt.AsEnumerable
let ra = a.ItemArray.Select(Function (x) x.ToString.Trim).ToArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

Cheers @pandeypriyanka1312

@ppr @Palaniyappan Thanks for the Link code.
With both the solutions that you have provided, I am facing same error. I think it is because how I am trying to implement the Linq in workflow.

Here are the steps I am following.

Read Range = output to DT
DT = Linq

For Assign, I am getting exception of Object reference not set to an instance of an object.

Is there something else I need to set???

you need to prepare the dtCorrected DataTable

it can be done by following:
Assign Activity
LHS: dtCorrected | DataType DataTable
RHS: yourDatatbleVar.Clone

@ppr Thank you… It is working now…

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