How to create a linq query to replace strings

i need a linq query to replace in shift column, where in shift column i want to replace as E01 as A, E02 as B, E03 as C, EG1 as GEN, EGE as GEN


YourDataTable = (From row In YourDataTable.AsEnumerable()
                Let shiftValue = row.Field(Of String)("shift")
                Let newValue = If(shiftValue = "E01", "A", If(shiftValue = "E02", "B", If(shiftValue = "E03", "C", If(shiftValue = "EG1", "GEN", If(shiftValue = "EGE", "GEN", shiftValue))))))
                Select NewRow = row.Table.Rows(row.Table.Rows.IndexOf(row))(0) = newValue).CopyToDataTable()

1 Like

Hi im getting error like this

Have a look, as the case is about updating column value
How to Update Data Column Values of a Data Table | Community Blog

One of many options could be

  • create a Lookup Dictionary
  • incorporate a ContainsKey check for handling unmapped values

In general this case is less a LINQ case and other options described in the blog can be used


Can you try the following sample?

dict = New Dictionary(Of String,String)From{ {"E01","A"}, {"E02","B"}, {"E03","C"}, {"EG1","GEN"},{"EGE","GEN"}}

Then use the following in InvokeCode

If(dict.ContainsKey(r(0).ToString)) Then
r(0) = dict(r(0).ToString)
End If
End Sub

Sample (13.5 KB)


1 Like

we can only use LINQ within an assign, when every part is returning a value

Thats why we mentioned:

also with the relation that Black-Boxing code within an Invoke Code is less to see as a LINQ Statement

1 Like

Hi @sathish_Kumar6
→ Read Range Workbook:

Output: dt
→ Use the below syntax in Assign activity:

dt= (From row In dt.AsEnumerable()
                 Let shiftValue = If(row.Field(Of String)("Shift") = "E01", "A",
                                    If(row.Field(Of String)("Shift") = "E02", "B",
                                       If(row.Field(Of String)("Shift") = "E03", "C",
                                          If(row.Field(Of String)("Shift") = "EG1" Or row.Field(Of String)("Shift") = "EGE", "GEN", 
                                             row.Field(Of String)("Shift")
                                          ) ) ) )Select dt.Clone.LoadDataRow({shiftValue}, False)).CopyToDataTable()



(From row In Dt.AsEnumerable()
                 Let newShift = If(row.Field(Of String)("shift") = "E01", "A",
                                  If(row.Field(Of String)("shift") = "E02", "B",
                                     If(row.Field(Of String)("shift") = "E03", "C",
                                        If(row.Field(Of String)("shift") = "EG1" OrElse row.Field(Of String)("shift") = "EGE", "GEN",
                                           row.Field(Of String)("shift")))))
                 Select Dt.Clone().Rows.Add(row.ItemArray.Take(Dt.Columns.IndexOf("shift")).Concat({newShift}).Concat(row.ItemArray.Skip(Dt.Columns.IndexOf("shift") + 1)).ToArray())


1 Like

it worked thanks for the response.

1 Like

Thanks for the response

1 Like

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