How to update the values for a particular column in DT with LINQ

Help Activities
, ,
1

Hello,

I have a DataTable that exceeds 100K rows and about 20 columns.
I would like to have 2 types of queries :

  • One that updates a particular column with a specific value unconditionally
  • A second one that checks the existence of a specific character and replaces it with another string value

Both queries should be for columns of type string

2

@SONYC
if you want to update a single value in all the rows of a specific column use the below code in Invoke Code Activity

For Each row As datarow In dt1.AsEnumerable
row(“YourColumnName”)=“Value”
If row(“ColumnName”).ToString.trim.Contains(“/”) Then
row(“ColumnName”).ToString=row(“ColumnName”).ToString.Replace(“/”,“”)
End If
Next

can you explain which value you need to update for all the rows in a particular

3

Thank you for your promptitude.
Yes, I need to concatenate the values of other columns. For example : newValue = row(1)+row(4)

4

@SONYC

can you also share are there any fixed character which you need to replace

and also you need to replace the special character in any specific column or all

check with the below code and Try it once in invoke code

dt1.Columns.add(“NewColumn”)
For Each row As datarow In dt1.AsEnumerable
row(“NewColumn”)=row(1).ToString+row(4).ToString
If row(“ColumnName”).ToString.trim.Contains(“/”) Then
row(“ColumnName”)=row(“ColumnName”).ToString.Replace(“/”,“”)
End If
Next

5

@SONYC

Update a particular column unconditionally:

YourDataTable.AsEnumerable().ToList().ForEach(Sub(row) row.SetField(Of String)("ColumnName", "SpecificValue"))

Check existence and replace if found:

YourDataTable.AsEnumerable().ToList().ForEach(Sub(row) row.SetField(Of String)("ColumnName", If(row.Field(Of String)("ColumnName").Contains("SpecificCharacter"), "ReplacementValue", row.Field(Of String)("ColumnName"))))

cheers…!

6

Hi @SONYC

Unconditional Update:

For Each Row activity (TypeArgument: DataRow, Values: YourDataTable)
Assign activity:
row(“ColumnName”) = “YourSpecificValue”

Conditional Update

For Each Row activity (TypeArgument: DataRow, Values: YourDataTable)
Assign activity:
If row(“ColumnName”).ToString().Contains(“YourSpecificCharacter”)
Then
row(“ColumnName”) = row(“ColumnName”).ToString().Replace(“YourSpecificCharacter”, “ReplacementString”)
End If

Hope this helps

7

Hello,
I tried implementing the solution, but I get the error below :

For Each row As datarow In dtData.AsEnumerable
If row(“BBPP”).ToString.Trim.Contains(““) Then
row(“BBPP”)=row(“BBPP”).ToString.Replace(””,“x”)
End If
Next

image

The code doesn’t seem to evoke any syntax error

image

8

While trying to execute the solution that you suggested I get the same error :

image

9

Thank you @sanjay3
This is the basic solution, but unfortunately it’s not the optimal one while working with large data tables.

10

@SONYC

Can you exception message , the error message will be present in the locals ,when the exception occurs click on the locals panel

Before to that can you try this

dt1.Columns.add(“NewColumn”)
For Each row As datarow In dt1.AsEnumerable
row(“NewColumn”)=row(1).ToString+row(4).ToString
If row(“ColumnName”).ToString.trim.Contains(“/”) Then
row(“ColumnName”)=row(“ColumnName”).ToString.Replace(“/”,“”)
Else
row(“ColumnName”)=row(“ColumnName”).tostring
End If
Next

11

Just tried it and got the same exception