Delete unwanted spaces in all cells in Datatable

Hi,

I need to remove all the whitespaces in all cells in a datatable. I don’t have any column name. there are nearly 100 columns in the table. So how could i trim all the whitespaces in every cell.

@soorya_prasad

By default the column names will be there like column1 you can use that

@soorya_prasad

You can use the column numbers instead of column names.
You can get the number of columns present using DataTableName.Columns.Count
You can then use a while loop inside the ForEachRow activity to trim the values of each cell.

PFA the sample workflow.

Forum_DtWhitespaceFilter.zip (27.9 KB)

3 Likes

@soorya_prasad
as an alternate can be done with linq

Assign: dtCorrected= yourDatatbleVar.Clone

Assing: dtCorrected =

(From r In dtData.AsEnumerable
Select ia = r.ItemArray.toList
Select ic = ia.ConvertAll(Function (e) e.ToString.Trim.Replace(" “,”")).toArray()
Select dtCorrected.Rows.Add(ic)).CopyToDataTable()

4 Likes

Hi @soorya_prasad,

Use following linq code in assign activity to replace all white spaces with null in the datatable.

NewDT =
(From q In DT.Select Let x=String.Join(",",From p In DT.Columns.Cast(Of System.Data.DataColumn) Select If( String.IsNullOrWhiteSpace(q(p).ToString),"",q(p).ToString.Trim)) Select DT.Rows.Add(x.Split({","},StringSplitOptions.None))).ToArray.CopyToDataTable

3 Likes

@manishjagtap Thanks, this is the easiest methood compared to Linq

hi @samir this too works well. But I prefer going with simple while loop activity. So that even beginners could understand the code

Yes, that’s right @soorya_prasad
:+1:

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