How to update specific values in datatable without using for each row

[111962 (YU.)]

Hi, guys.

In a DataTable like the one below, I want to update the value in column a to 0 in the row where the values in column Name are CC and FF.

Is there any solution to resolve this without using for each row.
Thank you very much.

[pravin_calvin (pravin kumar)]

Hi @111962

We can try with below LINQ Expression

Assign
dtClone= ReadDt.Clone

(From r In ReadDt.AsEnumerable
let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(“”,“0”)).toArray()
Select dtClone.Rows.Add(ra)).CopyToDataTable()

Regards

[Karuna (Karuna Devanagavi)]

@111962
Using “For Each Row”

1. Read Excel – output - dtSheet1

3. Write Range – dtSheet1

[111962 (YU.)]

Thank you for your reply.
In real world, since there are billion rows of data, I’m a little worried that for each row might affect the processing speed of the robot, so I’m looking for a more efficient way.

[pravin_calvin (pravin kumar)]

Hi @111962

Is there a any other rule by which we can sort it out by seeing the length of the value in column a

If length is greater than 1 we can update as 0?

Regards

[111962 (YU.)]

Thank you for your reply.
And I’m sorry for that I didn’t explian this clearly.

I can’t predict whether it is blank or some other value.
I want to update the value in column a with 0 for a row that has values CC or FF in the Name column.

[pravin_calvin (pravin kumar)]

Hi @111962

Try with the below LINQ expression as by first post!

(From d In testDt.AsEnumerable
Let u = If(d(1).toString.Equals(“CC”) Or d(1).toString.Equals(“FF”),“0”,d(2).ToString)
Select assignDt.Rows.Add(New Object(){d(0),d(1),u,d(3)})).CopyToDataTable

Regards

[111962 (YU.)]

Thank you very much.
I will try this LINQ expression out.

[pravin_calvin (pravin kumar)]

Hey @111962

Does it works for you?

If yes then please close the thread so that it may be useful to some who searches similar!

Regards

[111962 (YU.)]

I have some other problems to solve, and I’m so sorry for the delay in my reply.
I tried to solved this problem by assign as below. but I got an error: [Collection was modified; enumeration operation might not execute]
I’m looking into this now.

(From d In Dt_test.AsEnumerable
Let u = If(d(1).toString.Equals(“CC”) Or d(1).toString.Equals(“FF”),“0”,d(2).ToString)
Select Dt_test.Rows.Add(New Object(){d(0),d(1),u,d(3)})).CopyToDataTable

[pravin_calvin (pravin kumar)]

Hi @111962

assign

dtClone=ReadDt.Clone (Vartype datatable)

Assign
newDt=
(From d In ReadDt.AsEnumerable
Let u = If(d(1).toString.Equals(“CC”) Or d(1).toString.Equals(“FF”),“0”,d(2).ToString)
Select dtClone.Rows.Add(New Object(){d(0),d(1),u,d(3)})).CopyToDataTable

Write range the new Dt

Regards

[111962 (YU.)]

I solved this error by using defined different datatable you taught me.
Thank you very much.