Linq to .trim all values in datatable’s all rows & all columns?

[wija (Fredy Wijaya)]

Linq to .trim all values in datatable’s all rows & all columns?

With easy to understand variable name so i can understand.

[mkankatala (Mahesh Kankatala)]

Hi @wija

Use the below Invoke Code,

newDt = New DataTable()

For Each col As DataColumn In dt.Columns
    newDt.Columns.Add(col.ColumnName, col.DataType)
Next

' Populate the new DataTable with trimmed values
For Each row In dt.AsEnumerable()
    Dim newRow As DataRow = newDt.Rows.Add()
    For Each col As DataColumn In dt.Columns
        Dim value = row(col)
        If TypeOf value Is String Then
            newRow(col.ColumnName) = DirectCast(value, String).Trim()
        Else
            newRow(col.ColumnName) = value
        End If
    Next
Next

Invoked Arguments:

Hope it helps!!

[Jayesh_678 (Jayesh)]

Hi,

Try this:-
In invoke code activity.

dataTable.AsEnumerable().ToList().ForEach(Sub(row) row.ItemArray = row.ItemArray.Select(Function(item) If(item IsNot DBNull.Value, item.ToString().Trim(), item)).ToArray())

Thanks

[ptrobot]

With two Assign statements:

trimmedDataTable = originalDataTable.Clone
trimmedDataTable = originalDataTable.AsEnumerable.Select(Function(row) row.ItemArray.Select(Function(cell) cell.ToString().Trim()).ToArray()).Select(Function(cells) trimmedDataTable.Rows.Add(cells)).CopyToDataTable

[wija (Fredy Wijaya)]

This one seems easy to understand & make sense.
Will try this tomorrow, sleeping now

[ppr (Peter Preuss)]

Variation - Handles only String Datatype Columns and keeps other DataTypes

(From d In dtData.AsEnumerable()
Let ra =d.ItemArray.Select(Function (x, i) If(d.Table.Columns(i).DataType.Equals( GetType( String )), x.ToString().Trim() , x)).ToArray()
Select r = dtCorrected.Rows.Add(ra)).CopyToDataTable()

taken from:

LINQ query to update(replace)datatable in uipath

@Rachel7 this is maybe a too quick shot as it replace any space in any column to NA and returns a datatable. But you can give a try (From r In dtData.AsEnumerable Select ia = r.ItemArray.toList Select ic = ia.ConvertAll(Function (e) e.ToString.Trim.Replace(" ","NA")).toArray() Select dtCorrected.Rows.Add(ic)).CopyToDataTable() NEW VERSION, simplified - replace all cols with only String.Empty content: (From d In dtData.AsEnumerable Let ra =d.ItemArray.Select(Function (x) If(isNothing(x), Not…

[wija (Fredy Wijaya)]

I don’t understand the ra ia i etc etc x.x

[ppr (Peter Preuss)]

all ra, ia …are used like local variable names

have a look at the different LINQ Starter helps, as we recommend to have certain LINQ knowledge when using LINQ within implementations
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

[wija (Fredy Wijaya)]

ok this one works with easy to understand variable name.