REplacing Values in a column of datatable

Learning Hub Academy Feedback
, , ,
1

How do i replace values in a column in the datatable?
I’ve the following which i got from another side, but it keeps returning me an error, about strict on disallowing implicit conversion from string to boolean…

dt = dt.AsEnumerable().Where(Function(a) a.Field(of string)(“yourcolumnname”).ToString.Replace(“your old value”,” your new value”).ToString).CopyToDatatable()

2 Likes
2

@Username95
the exception comes from following:

  • the where method expects a lambda function which will return a booleant
  • the statement returns a string instead

we can do

  • for each row activitiy
  • assign activity row(“YourColName”) = row(“YourColName”).ToString.Replace(“your old value”,” your new value”)

or using a LINQ within a invoke code activity

3

Ohh. How do we use linq? Cos there would be thousands of rows.

4

e.g. invoke code and:

yourDataTable.AsEnumerable.ToList.ForEach(Sub(row) row(“ColNameOrIndex”)= row(“ColNameOrIndex”).ToString.Replace(“your old value”,” your new value”))

7 Likes
5

Another question… How do i skip blank fields?

6

e.g check on 1 col:

(From d In dtData.AsEnumerable
Where Not If(isNothing(d(0)), True, String.IsNullOrWhiteSpace(d(0).toString.Trim))
Select d).CopyToDataTable

Variant:
(From d In dtData.AsEnumerable
Where Not isNothing(d(0)) OrElse String.IsNullOrWhiteSpace(d(0).toString.Trim)
Select d).CopyToDataTable

or using Filter Datatable

7

using the same replace linq, how do i replace empty value with something else?
like this?
dt.AsEnumerable.ToList.ForEach(Function(row) If(IsNothing(row(colName).toString) OrElse String.IsNullOrWhiteSpace(row(colName).toString), "NULL", row(colName).toString))

8

Hi @Username95
You can skip the blank by adding the small code in @ppr response

yourDataTable.AsEnumerable().Where(Function(row) Not row(“ColNameOrIndex”).ToString.Equals(“”)).ToList.ForEach(Sub(row) row(“ColNameOrIndex”)= row(“ColNameOrIndex”).ToString.Replace(“your old value”,” your new value”))

Make sure the variable yourDataTable should be passed to invoke code with argument type as In/Out

Regards,
Nived N
Happy Automation

1 Like
9

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