LINQ to replace values in datatable column

[jose.p.mendes (Jose Mendes)]

Hello,
I’m having some difficulties creating a LINQ query to replace certain values in a column. I have a table with more than 200k rows and in the column with index 0, named “Depósito”, there are values ranging from A001 to A999, B001 to B999, and C001 to C999. I want to replace “A” with “8810”, “B” with “8811”, and “C” with “8817”.
There is also a screenshot of my the first lines of my table.

Thanks in advance

[Shiva_Nikhil]

@jose.p.mendes

use invoke code activity and pass the below expression

For Each row As DataRow In dt.AsEnumerable
If row(0).ToString.StartsWith(“A”) Then
row(0)=row(0).ToString.Replace(“A”,“8810”)
Else
If row(0).ToString.StartsWith(“B”) Then
row(0)=row(0).ToString.Replace(“B”,“8811”)
Else
If row(0).ToString.StartsWith(“C”) Then
row(0)=row(0).ToString.Replace(“C”,“8817”)
Else
row(0)=row(0).ToString
End If
End If
End If
Next

I have tested with some of the sample data its working fine

Hope this helps

[sanjay3 (Sanjay)]

Hi @jose.p.mendes

Try This

Assign activity:
yourDataTable = yourDataTable.AsEnumerable().Select(
Function(row)
row.SetField(“Depósito”,
If(row.Field(Of String)(“Depósito”).StartsWith(“A”), “8810”,
If(row.Field(Of String)(“Depósito”).StartsWith(“B”), “8811”,
If(row.Field(Of String)(“Depósito”).StartsWith(“C”), “8817”,
row.Field(Of String)(“Depósito”)))))
End Function).CopyToDataTable()

[Yoichi]

Hi,

Can you try the following expression?

dict = New Dictionary(Of String,String)From{{"A","8810"},{"B","8811"},{"C","8817"}}

Then

dt = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow(new Object(){dict(r(0).ToString.Substring(0,1))+r(0).ToString.Substring(1)}.Concat(r.ItemArray.Skip(1)).ToArray,False)).CopyToDataTable()

note dict is Dictionary<string,string> type

Regards.

[Parvathy (PS Parvathy)]

Hi @jose.p.mendes

Try this:

YourDataTableVar = YourDataTableVar.AsEnumerable().Select(
    Function(row) 
        row("Depósito") = 
            If(row.Field(Of String)("Depósito").StartsWith("A"), "8810",
               If(row.Field(Of String)("Depósito").StartsWith("B"), "8811",
                  If(row.Field(Of String)("Depósito").StartsWith("C"), "8817", row.Field(Of String)("Depósito"))))
        row
    End Function).CopyToDataTable()

Hope it helps!!

[jose.p.mendes (Jose Mendes)]

Worked perfectly.
Thank you very much