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

[SHAISTA]

Hi @Mr_JDavey

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

[Mr_JDavey (Joshua Davey)]

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:

Enumerable.Sum Method (System.Linq)

Thanks,
Josh

[SHAISTA]

Thanx but i have resolved it now.

[sara_s]

hi Please visit this

[AlexRPA (Alex)]

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

[ClaytonM]

@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.

[AlexRPA (Alex)]

@ClaytonM

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

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

[ClaytonM]

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

[AlexRPA (Alex)]

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

[sara_s]

please watch this

[sara_s]

it is ToString()

[Dave (David Wiebelhaus)]

@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)

[ClaytonM]

@AlexRPA

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

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

[AlexRPA (Alex)]

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

[balupad14 (Balamurugan)]

Hi @yogeshgyw,

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

How to sumup String values in a datatable

Hi @Phil_Wu, A single activity will do the convert the type and do the sump. Check it below. https://forum.uipath.com/t/balareva-datatable-activities/30768/4?u=balupad14 Regards Balamurugan

Regards
Balamurugan

[Dave (David Wiebelhaus)]

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

[balupad14 (Balamurugan)]

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.

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

Thank You
Balamurugan

[FlpVsg (Felipe Quintanilha)]

That worked out for me.
Thksss

[balupad14 (Balamurugan)]

Thank you @FlpVsg

[Fer]

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