Remove Extra charcters after certain limit from a specific column in datatable

Hi,

I want to remove extra characters present from a certain column in datable by not using for each row activity.
For example in the below Role column which should be fixed to 5 characters
image

@karthik_kulkarni1

Please try this in invoke code …send dt as in/out argument

Dt.AsEnumerable.ToList.ForEach(sub(r) r("Role") = If(r("Role").ToString.Length>5,r("Role").ToString.SubString(0,5),r("Role").ToString))

Cheerd

Hi @karthik_kulkarni1

=> Use Read Range Workbook to read the excel and store it in an datatable say dtInput
Input:


=> You the below Invoke Code acitivity:

dtOutput = dtInput.Clone()

For Each row As DataRow In dtInput.Rows
    Dim newName As String = row("Name").ToString()
    Dim role As String = row("ROLE").ToString()

    If role.Length > 5 Then
        row("ROLE") = role.Substring(0, 5)
    End If

    dtOutput.ImportRow(row)
Next

=> Below are the invoked arguments:


=> Use Write Range Workbook to write the modified datatable i.e., dtOutput to excel.
Output:

Workflow:


xaml:
Sequence5.xaml (8.0 KB)

Hope it helps

Hey @karthik_kulkarni1
you can use it:

newDataTable = originalDataTable.Clone()

newDataTable = originalDataTable.AsEnumerable().Select(Function(row) newDataTable.LoadDataRow(new Object() {row.Field(Of String)(“Name”), row.Field(Of String)(“ROLE”).Substring(0, Math.Min(5, row.Field(Of String)(“ROLE”).Length))}, False)).CopyToDataTable()

image

Here is a project example: BlankProcess59.zip (9.1 KB)

Hi @karthik_kulkarni1

You can simply use below LinQ Query

DT.AsEnumerable().Select(Function(row) DT.Clone().LoadDataRow({
    row.Field(Of String)("Name"),
    If(row.Field(Of String)("Role").Length > 5, row.Field(Of String)("Role").Substring(0, 5), row.Field(Of String)("Role"))
}, False)).CopyToDataTable()

Input:

image

Output:

image

Cheers!!

thing is above is working but it is eliminating other columns I have more number of columns.

@karthik_kulkarni1

Could you try the following?

Code:

DT.AsEnumerable().ToList().ForEach(Sub(row)
                                       row.SetField("Role", If(row.Field(Of String)("Role").Length > 5, row.Field(Of String)("Role").Substring(0, 5), row.Field(Of String)("Role")))
                                   End Sub)

Input:

image

Output:

image

Cheers!!

You can try this too. This should work.

Happy to help if not sorted.

Regards

@karthik_kulkarni1

As per formula it eould not remove any columns…

Please check it properly…the formula is only to update

Cheers

Hi @karthik_kulkarni1
Check out the below process n Studio X. This should help you.

RemoveExtraCharacters.zip (233.6 KB)

Hope this helps you.

Happy Automation
Regards