Using LINQ to replace specific value in DataTable without For-Each

Hi All

I attempted to follow the link to replace some values in one specific column in my dataset. The error message, however, popped up “Option Strict On disallows implicit conversions from ‘String’ to ‘Boolean’”.

The LINQ I used in Assign Activity is…
dt_Combined.AsEnumerable().Where(Function(a) a.Field(of string)(“PhoneNo”).ToString.Replace(“-”, ””).ToString).CopyToDatatable()

Would be greatly helpful to advise me what triggers the error message…

Thanks in advance!

unfortunately we cannot refer to your sample data.

The issue could occur from following:

  • the where method expects an evaluation function (predicate, lambda function) that is returning a boolean

currently in the the where block a string is returned from the replace method.

give a try on:
Assign activity:
leftside: dtCorrected (DataType: DataTable)
right side: t_Combined.Clone()

assign activity:
left side dtCorrected (datatype: Datatable)
rigth side:
(From d in t_Combined.AsEnumerable()
let ra = d.ItemArray.Select(Function (x) x.toString.Replace(“-”, ””)).toArray
Select rc = dtCorrected .Rows.Add(ra)).CopyToDataTable

Hi @u2018dem0528

You can try this in invoke code with dt1 as In/Out argument

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“PhoneNo”)= row(“PhoneNo”).ToString.Replace("-",""))

Thank you for replying. The original data (highly confidential) is consisted of some columns such as “PhoneNo”, “Postal Code” which sometimes contains “-”, sometimes not (for instance, 1112223333 or 123-456-7890 or 2223334444). And to join with other DataTable which doesn’t have any “-”, I would need to remove all “-” from those columns.

While following the instruction, my DataTable got a same value for all the rows like the screenshot attached to this…

LINQ - Capture

Thank you for answering. Actually this worked well! Just wondering if there’re multiple columns to which I should apply the same logic, am I supposed to duplicate and coy same lines? Or any beautiful way to write…?

hust crosscheck to opening and closing brackets in your statements (maybe share it with us)

Hi, this is the right side

(From d In dt_Combined.AsEnumerable()
Let ra = d.ItemArray.Select(Function (x) x.ToString.Replace("-","").ToArray)
Select rc = dt_Corrected.Rows.Add(ra)).CopytoDataTable

kindly note the too late closing bracket )
please change to:
let ra = d.ItemArray.Select(Function (x) x.toString.Replace(“-”, ””)).toArray