Bulk update of a data table column in one go

Help Activities
, ,
1

Hi Team,

Am trying to update a data table column in one go that has huge data by using below assign statement:

Datatable.Columns[“Column”].Expression=“‘********’”

By doing this, it will do bulk update for all the filled cells and blank cells as well for a column.

But the requirement is it should do a bulk update of all the filled cells & not for blank cells.

Any idea how we do this?

1 Like
3

@MANISHA_V_ARORA - You mean all the filled cells in a particular columns should be replaced with ****** right? Once you confirmed, I can share you the invoke code method…

4

Yes only the filled cells of a particular column should be filled in one go.

Please note the data can be huge.

5

@MANISHA_V_ARORA -

Noted… After read range your sheet and store the output as Dt. use the invoke code below…

image
image1062×548 17.1 KB

dt.AsEnumerable().where(function(row) not row("YourColName").ToString.Equals("")).ToList.ForEach(sub(row) row("YourColName") = "********")

Then, Write Range your Results to different sheet and verify the results…

My Sample Input

image
image173×608 3.73 KB

Output

image
image170×617 2.17 KB

Please try and let us know…

2 Likes
6

Thank you for quick reply.

I get below error:

error CS1026: ) expected At line 0
error CS1528: Expected ; or = (cannot specify constructor arguments in declaration) At line 0
error CS1002: ; expected At line 0
error CS1525: Invalid expression term ‘.’ At line 0
error CS1002: ; expected At line 0
error CS1002: ; expected At line 0
error CS1525: Invalid expression term ‘)’ At line 0
error CS1026: ) expected At line 0
error CS1002: ; expected At line 0
error CS1525: Invalid expression term ‘)’ At line 0
error CS1002: ; expected At line 0

Capture
Capture863×151 6.51 KB

7

@MANISHA_V_ARORA - Did you choose Language as VBNet in the invoke code? Code I provided is VBNet.

8

My bad :woman_facepalming:

Changed it to VB

Get this error though now:
Invoke code: Exception has been thrown by the target of an invocation.

9

please check your data. I guess something wrong with the column values…

10

The data is fine. The column has numeric values.

11

@MANISHA_V_ARORA - Please share the screenshot of your column values(Sample).

12

Tried on a column with text value and also on a column with numeric values.
It doesnt work.

Sample here:
column

13

Code is working fine for me, no issues(Shared the results of the screenshot already). Please share sample data with at least some 50-100 rows(if any variations there) …will try here.,

14

Here you go:
Test.xlsx (9.6 KB)

15

@MANISHA_V_ARORA - Just pointed your sheet to read your sheet1 and Changed the column name…works fine here…

image
image291×617 5.89 KB

Code used:

dt.AsEnumerable().where(Function(row) Not row("c_tier_cd").ToString.Equals("")).ToList.ForEach(Sub(row) row("c_tier_cd") = "********")

Workflow

image
image524×646 13.9 KB

16

my project is in C# and not VB.
Does this require any external dependent files to be downloaded?

For me here it doesnt seem to work.

Uipath version is 2020.10.2 on my side

17

Hmm…this should be first item added in your initial query…

1 Like
18

You know it what it worked.

Thank you so much :slight_smile:

In arguments I had to pass the dt variable as below:
Which i didnt enter earlier :woman_facepalming: :woman_facepalming:
capture

2 Likes
19

Omg…I thought you had it since I shared the screenshot of the arguments in my initial post…

But glad to know it worked. :beers:

Please mark my post as solution, that will close this thread and others looking for solved post might easily benefited.

Also wanted to check…what is total # rows you have and what is the execution time ?

1 Like
20

The sample sheet that I have taken has around 1500 rows of data and it takes
00:00:06 duration

2 Likes
21

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