How to trim column cell values in For Each Loop

Hi All,

I have trimmed the headers of the columns in the whole data table but how to trim the white spaces in the cell values of the columns for the whole data table? Pl see the screenshot:

image

Column Headers are successfully trimmed but I want to trim the whole data table cells values also.

Many thanks,

hi @Dr_Raza_Abidi_PhD

Please look into these threads

Regards
Sudharsan

@Dr_Raza_Abidi_PhD

You can write as below

col(“ColumnName”) = col(“ColumnName”).ToString.Trim

Hope this may help you

Thanks

Hi @Dr_Raza_Abidi_PhD

You can try with the below LINQ here,

LHS:dtGood
RHS:
(From r In dtData.AsEnumerable
Select rac = r.ItemArray.Select(Function (x) x.toString.Trim).toArray
Select dtGood.Rows.Add(rac)).CopyToDataTable()

where dtData is your source Data Table.

Thank you.

1 Like

Hi @Dr_Raza_Abidi_PhD

In you syntax you need to clone the “dtData”
Assign Activity
LHS : dtGood
RHS : dtData.clone()

Regards
Gokul

1 Like

Thanks @Gokul001 for adding here. I just added the inline code only.

@Dr_Raza_Abidi_PhD please refer the attached codebase.
TrimAllSpaceInDT.zip (7.9 KB)

Thank you.

Thanks @Gokul001 and @Jobin_Joy : Issue is resolved but I could not understand the complex code i.e., (From r in dtData …)

But what should I do If I want to trim only one complete column instead of the whole data table?

Thanks,

Hi @Dr_Raza_Abidi_PhD

For Trim only one column Check with the below expression

dtData.AsEnumerable.Where(Function(x) x(“ColumnName”).ToString).CopyToDataTable

Or

(From r In dtData.AsEnumerable
Select rac = r.ItemArray.Select(Function (x) x(“Column Name”).toString.Trim).toArray
Select dtGood.Rows.Add(rac)).CopyToDataTable()

Regards
Gokul

Hi @Dr_Raza_Abidi_PhD

You can follow whatever @Gokul001 mentioned.

Thank you.

1 Like

@Gokul001 : Thanks but receiving the error “Option Strict On disallows late binding”.

Pl. guide.

Thanks,

Hi @Dr_Raza_Abidi_PhD

Can you share the screenshot shot of the error and also the variable panel.

Regards
Gokul

@Gokul001 : Pl find attached .xaml file. Thanks,

TrimAllSpaceInDT.xaml (6.3 KB)

Hi @Dr_Raza_Abidi_PhD

Instead of that expression you can try with this one

(from r in DataDT.AsEnumerable
Select ia = r.ItemArray.toList
Select ic = ia.ConvertAll(Function (e) e.ToString.Trim()).toArray()
Select GoodDT.Rows.Add(ic)).CopyToDataTable()

Regards
Gokul

@Gokul001 : The code file .xaml shared by @Jobin_Joy was perfectly okay with the all data table rows and columns. I am only trying to do trimming the values on single column.

I have shared the .xaml file in which I am trying on single column and it is giving an error. For all columns and rows the code is fine.

(From r In dtData.AsEnumerable
Select rac = r.ItemArray.Select(Function (x) x(“Column Name”).toString.Trim).toArray
Select dtGood.Rows.Add(rac)).CopyToDataTable()

This above code when I place my variable, it gives me an error:

“Option Strict On disallows late binding”.

Hi @Dr_Raza_Abidi_PhD

LINQ is less helping as solving in the one specific column scenario. It is more straightforward to do it with a for each row.

Thank you.

Hi @Dr_Raza_Abidi_PhD

Please use the below code to Trim specific cell value in the Data Table.

In the sample excel, I kept 2 columns(Name and Address) and in the code we are trimming just Name cell values alone.

Code:
TrimSpecificColumnInDT.zip (9.5 KB)

Hope this will fulfil your requirement. Thank you.

1 Like

Thanks @Jobin_Joy : Here is the screenshot of your .xaml file. The main activity is not loading properly.

Okay @Jobin_Joy : I have updated the package so, its running now. I check and let you know. Thanks,

Hi @Dr_Raza_Abidi_PhD,

I agree with @Jobin_Joy a simple for each row activity will help you here no need to fuss with linq. If you are having problems understanding the code, then others who have to maintain it might also have it. I am not against linq, but I am for keeping the code simple and easily maintainable.

Use the solution suggested by @Srini84. That is all you need.

For each row in YOURDATATABLE 
     # Use assign activity 
     row("YourColumnName") =  row("YourColumnName").ToString.Trim

That is it.

What does the above do.
On the left hand side is the row(“YourColumnName”) object
On the right hand side converts that object to string and trims empty spaces.

YOURDATATABLE will then have a column which is free of spaces (start and end), spaces in the middle of string will remain.

Trim for all columns

Sooner or later you will want to extend this logic to cover all columns in your datatable.
To make the above code even more reusable and cover all your columns, you can have an outer For each loop and iterate through YourDatatable.Columns which is of an array type.

For each column in YOURDATATABLE.Columns
    For each row in YOURDATATABLE 
        # Use assign activity 
        row(column.ToString) =  row(column.ToString).ToString.Trim

Here is a sample file :
TrimColumnValues.xaml (11.1 KB)

You can make the InputDatatable variable into an argument and use reuse this in future projects via invoke workflow activity.

2 Likes

Thank a lot for your swift response @jeevith. I am very grateful for the comprehensive explanation of the issue.

Many thanks & kind regards,