Cleansing data in read range

Hello,

I’m using Read Range to load excel files into a SQL database.

Can someone explain how I can trim() data in two columns in the read Range variable?

Sometimes the spreadsheet mistakenly has extra spaces in two columns that I want to clean up the Insert Activity.

Any help would be greatly appreciated.

Thanks

Tim

@timothy.mullady , Follow the below steps :-

  1. After Read Range, create a datatable variable called - RemovedDt and assign it with Read Range input datatable as shown below,
    → RemovedDt(type DataTable) = InputDt.Clone(output of read range).
  2. Then assign InputDt with this below linq query,
    → InputDt = (From r In InputDt.AsEnumerable Select ia = r.ItemArray.toList Select ic =
    ia.ConvertAll(Function (e) e.ToString.Trim).ToArray() Select
    RemovedDt.Rows.Add(ic)).CopyToDataTable().
  3. Now you can insert InputDt to SQL Database.

Hope this may help you :slight_smile:

1 Like

yes its working thank you so much could you please give brief of this query I am new in linq

In the above linq query,
First we convert each row values as a collection of list and assign it to variable called = “ia”, that is thus code - “Select ia = r.ItemArray.toList”.
Then each value inside a list we will remove the white spaces and convert back to Array, then finally after converting it to array we will add it to Datatable called “RemovedDt”, that is this code - “Select ic =
ia.ConvertAll(Function (e) e.ToString.Trim).ToArray() Select
RemovedDt.Rows.Add(ic)”
Hope this may help you :slight_smile:

@Manish540 Hi Manish,
Is there anyway that you could send me screenshots of the workflow?

I think a visual will help me understand it much better.

Thanks,

Tim

Remove space in excel sheet.zip (8.5 KB)
please find the workflow please mark as solution

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