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

Hi @Mr_JDavey

Can i add the data of two or more rows in a data table through this method?

Hi there @Shaista,
Certainly, this will allow you to add all data for a specific column for each row.

More information can be found here:

Thanks,
Josh

1 Like

Thanx but i have resolved it now. :slight_smile:

1 Like

hi Please visit this

Hi Sara_s,
I’m not sure I am reading from the video correctly but wonder if you could confirm the command please… I read it at this:

tblaccount.Compute(“Sum(AVAIL_BALANCE)”,"").ToString

but I am getting an error: “Assign : Invalid usage of aggregate function Sum() and Type: Object.”

I cant work out if the last two characters are {} or , I don’t think it is ()

Think my eyesight is poor.

Hope you can advise
Many Thanks
Alex

1 Like

@AlexRPA
If you just need to sum a column, I would suggest using the syntax like this:

tblaccount.AsEnumerable.Sum(Function(x) Convert.ToDouble(x("AVAIL_BALANCE").ToString.Trim) ).ToString

You can filter to only certain columns also using .Where, like this:

tblaccount.AsEnumerable.Where(Function(x) x("column").ToString.Trim.Equals("123456")).Sum(Function(x) Convert.ToDouble(x("AVAIL_BALANCE").ToString.Trim) ).ToString

I hope this helps.

Regards.

14 Likes

Brilliant, Thanks Clayton, that worked perfectly. Could I ask for another bit of advice please? Ive watched all the UIPath vid’s now but I find I am still struggling with data manipulations, is this .NET coding I need to delve deeper into on its own or something else?

Many Thanks again
Alex

1 Like

UiPath works with vb.net syntax (it’s similar to C# too). Lambda and LINQ are what I find the most useful. I’m not an expert but you can look up how to do most things with Data Tables, Lists, and Arrays using these methods through online sources. The ones I use the most are Where (to filter down the data) and Select (to pull certain parts of an item).

EDIT: also, include vb.net in your online searches

3 Likes

Thanks Clayton, I will start looking at these three topics in my searches.
Many Thanks
Alex

please watch this

it is ToString()

1 Like

@AlexRPA

I’d highly recommend throwing an if statement in your function to check if the items in “avail_balance” are in fact numbers. If it isn’t a number, change it to 0.

This way if a null value or other non-number somehow finds its way into that column, it will just convert that value to 0 instead of throwing an error (assuming you don’t want an error thrown)

1 Like

Good point by @Dave

Adjustment to the .Sum() would be like:

tblaccount.AsEnumerable.Sum(Function(x) If(IsNumeric(x(“AVAIL_BALANCE”).ToString.Trim), Convert.ToDouble(x(“AVAIL_BALANCE”).ToString.Trim), 0) ).ToString

so it adds 0 if the value is not numeric

5 Likes

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

Hi @yogeshgyw,

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

Regards
Balamurugan

1 Like

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

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

That worked out for me.
Thksss

1 Like

Thank you @FlpVsg

1 Like

Hi @ClaytonM,

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

I do not have any option regarding to AsEnumerable.

Thanks