How to sum all values in a column of an excel/Datatable?

Help
, ,
#21

Thanks Dave and Clayton, these tips are UIPath gold!!!

#22

Hi @yogeshgyw,

Take a look. Don’t use the vb.net code . Only the activity will do all .

Regards
Balamurugan

1 Like
#23

custom activities have their place, but I am not a fan of using them extensively for everything.

It can be difficult for version control on the machines the robots run since they have to be manually moved and updated. The more robots and machines you have, the more difficult it is for upkeep.

I usually prefer to use the library of static sequences for things like this, or an invoked workflow library for more involved process. Of course, there are places where custom activities are nice to use, I just try not to over-rely on them

#24

Hi ,
BalaReva.DataTable.Activities this package comes from the “https://gallery.uipath.com/”. The gallery is having lot of packages. These Packages are to do the process to very easy without struggling to do the code by vb.net .I don’t think so it is difficult for version control. Below I have shown a published package which is opened from the nuget package manager.

image

So @yogeshgyw and friends , Let me know in case see any issues.

Thank You
Balamurugan

3 Likes
How can we calculate the entire column values in a excel file and paste that result at the end of the data
#25

That worked out for me.
Thksss

1 Like
#26

Thank you @FlpVsg

1 Like
Sum the values in the column
#27

Hi @ClaytonM,

Does your tblaccount is defined as datatable? Because when I try to do the same, it appears as follows:

Capture
Capture816×184 15.3 KB

I do not have any option regarding to AsEnumerable.

Thanks

#28

Hi @Fer
If you are on version 2017 or older, it won’t be listed. It should be listed in 2018. Even if it isn’t listed though, it should still be recognized as correct syntax. You can probably also use .select like tblaccount.Select.Sum() … i think.

But like I said, even if it’s not listed, you can type it out and it should still work.

Regards.

#29

@ClaytonM, my version is older than 2017, that’s why is not working. I tried with tblaccount.Select.Sum() and also is not working.

#30

Can you show what errors you get?

#31

Hi,

Try like this,

yourDataTable.AsEnumerable().Sum(Function(row) row.Field(Of Integer)(“yourColumnName”))

I hope the column may integer datatype

#32

@sarathi125, and none of the methods appear when I call them. Is it due to UiPath version?

#33

its not method, simple linq query.

Use an Assign activity.
Declare a Integer variable and put this query to that variable

#34

Hello @Fer,
can you check the variable is look like this or different ?

image

image
image1061×551 46.2 KB

Simple aproch is like this

Convert.ToInt32(alphaLetterExcel.Compute(“SUM(Salary)”, String.Empty))

you can take knowledge from here (follow only the vb.net code)

http://learndotnetown.blogspot.com/2015/04/calculate-sum-total-of-datatable.html

Regards
Balamurugan.S

1 Like
#35

Hello @Fer,

Check it that you have this name space in your imports.

image

Regards
Balamurugan.S

1 Like
#36

You might need to just type it out even if it is not listed. If you get a validation error (like blue mark), then let us know. Also @balupad14 makes good suggestions.

1 Like
#37

Hello @balupad14, by using your approach I get this error:

Capture

Capture
Capture718×527 29.8 KB

#38

Hi @Fer,

I have tested your scenario. Yes I am getting the same error. Why ? Because when you read the data from excel to datatable everything is string. So here you are trying to do the sum with string column. That is the reason you are getting this error. If the column is integer , you won’t get this error. you have 2 solution for this.

Solution 1 (More work.)

  1. You have to convert the column as integer
  2. Or else add new column (tempVal) with datatype as integer.and convert all values to integer to this column(tempVal)

Solution 2 (Single Drag and drop.)

  1. Use the sum activity from “datatable extension” bellow video attached.

You can get it from UiPath Go also.

Sample Project : Fer.zip (7.9 KB) (The sample studio version is 2018.4.0)

Regards
Balamurugan.S

1 Like
#39

@balupad14,

Can you send me a printscreen of what is presented in the red area?

Capture
Capture984×622 59.8 KB

#40

Anyway I am doing the sums as follows:

Capture
Capture715×649 43.9 KB