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

[karthik_kulkarni1 (karthik kulkarni)]

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

[Anil_G (Anil Gorthi)]

@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

[Parvathy (PS Parvathy)]

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

[pikorpa (Piotr Kołakowski)]

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()

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

[lrtetala (Lakshman Reddy)]

@karthik_kulkarni1

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

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:

Output:

Cheers!!

[karthik_kulkarni1 (karthik kulkarni)]

@Anil_G

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

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

[lrtetala (Lakshman Reddy)]

@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:

Output:

Cheers!!

[Parvathy (PS Parvathy)]

You can try this too. This should work.

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

Hi @karthik_kulkarni1 => Use Read Range Workbook to read the excel and store it in an datatable say dtInput Input: [image] => 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: [image] …

Happy to help if not sorted.

Regards

[Anil_G (Anil Gorthi)]

@karthik_kulkarni1

As per formula it eould not remove any columns…

Please check it properly…the formula is only to update

Cheers

[Parvathy (PS Parvathy)]

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