Replace values of a column of datatable with another value

Hi,

I have columns in my datatable with 10,000 rows. I would like to change the values of a column
called “Standard” which is “X” (a string) to int32 value i.e. 1. I tried below Linq query but it is giving error “Option strict on disallows implicit conversion from “string” to “boolean””.

dt_tempSql.AsEnumerable().Where(Function(a) a.Field(Of String)(“Standard”).ToString.Replace(“X”,“1”).ToString).CopyToDatatable()

I am using assign activity. Any idea how to resolve this?

Hi @WASEEM_KHAN ,

Is column sequence is same in this case or it’s dynamic ?

If sequence remain same then you can use this

(From r In dtInput Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“Standard”)},False)).CopyToDataTable

@WASEEM_KHAN

You can try this as well!!

In_DT (Your Input Datatable)

image

(From d In In_DT.AsEnumerable()
Let r1 = d.ItemArray.Select(Function (x) x.toString.Replace(“X”, ”1”)).toArray
Select r2 =Out_DT.Rows.Add(r1)).CopyToDataTable

The column has the constant value “X” at some rows which I would like to replace with 1.

I am sorry but I could not understand your solution. You are not replacing “X” value with 1 in your expression.

Sorry i misunderstood your question i thought you want to change column name, now i get it.

it would be great If you could show us, how the column looks like …i.e. sample data.

It looks like this

Here the column name is “#” but assume it’s name is “standard”

Try with your query just change string to object

dt_tempSql.AsEnumerable().Where(Function(a) a.Field(Of Object)(“Standard”).ToString.Replace(“X”,“1”).ToString).CopyToDatatable()

OR

image

(From r In dtInput Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“columnName”).ToString.Replace(“X”,“1”) },False) ).CopyToDataTable

1 Like

The change from string to object does not make difference. It is producing the same error.

For second solution: Should I write it like this:


It gives me error that “No compiled code to run error BC30035:syntax error at line 1”

Instead of using invoke code use directly Assign activity.

To the left just use dt_out and to the right linq query.

For Reference
image

1 Like

Thanks for replying. First, the clone method is only copying the structure(i.e. names of column). I used dt.copy afterwards, so that it copies all the column values. Now your query seems to replace “X” with 1 but the problem is, it is updating values in first column not under “Standard” column and all the other column’s value are gone as well. I need complete datatable with only X values replaced by 1. Here is the output:

You need to declare all column in same query just like we declare for first column.

@WASEEM_KHAN - please check , whether this meets your expectation…

My Build Datatable

Invoke Code

dtinput.Select.ToList.ForEach(Sub(Row) Row("Standard") = Row("Standard").tostring.Replace("X","1"))

invoke Code Arguments

Hope this helps…

1 Like

Hi,

@prasath17 I tried your solution and it’s working. Thanks for your effort. I would also like to replace empty cells with “0”. In my case, the data type of standard column is object. I tried your query for example:

dtinput.Select.ToList.ForEach(Sub(Row) Row(“Standard”) = Row(“Standard”).tostring.Replace("",“0”))

but It gives an exception i.e. “Invoke code: Exception has been thrown by the target of an invocation.”

Any solution in this regard?

we can chain replace statements:

Row(“Standard”).tostring.Replace(“X”,“1”).Replace("",“0”)

Thanks for replying:). It raised the same exception “Invoke code: Exception has been thrown by the target of an invocation.”

is not coming from the replace chain.

Inspect the used datatable and check the arguments from the invoke code.
The origin replace was working, right?

“X” is replaced by “1” correctly but problem is to replace empty cells with “0”.