How can I calculate a total (Sum) over a DataTable created by a "Read Range" Activity?

Help
,
1

Hello Everybody,

I have problems calculating a column-total over a whole (unfiltered) column of a DataTable, created by an Excel “Read Range” activity. Sadly None of the solutions I found searching the forum (and google) worked for us.

My Problem: Column_Total.zip (16.4 KB)

I tried:
ExcelData.Compute(“Sum(Numbers)”, String.Empty).ToString

It throws an Error:
“Assign: Invalid usage of aggregate function Sum() and Type: Object”

Then I tried:
ExcelData.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“Numbers”).ToString.Trim) ).ToString

And it throws an Error:
“AsEnumerable” is not a member of “System.Data.DataTable”

As far as I understand it, the “Read Range” activity transforms all Excel-Data into type “String”, when it creates the DataTable. I think that’s the reason why the “compute”-Statement isn’t working. Unfortunately I couldn’t find a way controlling the output of the “Read Range” activity, forcing it to create numeric data.

Does anyone have a solution that doesn’t contain a “For Each Row” activity, looping over the entire DataTable, converting every single cell into “double”, adding them piece by piece to a global “Total” variable?

I would be very grateful for any help.

2

This error doesn’t sound right. It could be a glitch, and are you on a newer version of Studio? I would try deleting the .AsEnumerable, then type the period again then AsEnumerable.
ExcelData.AsEnumerable, and see if the error resolves. The .Sum() you were using look correct.

3

I downloaded your workflow and looked at it. After deleting .AsEnumerable it saw it as a member correctly.

image
image904×136 17.2 KB

And, if it doesn’t list it, then you might be on an older version, but it should still work if you type it out.

EDIT: fyi, you’ll need to change the line of code, press OK, then go back and fix it (because it won’t let you click OK if the line of code is exactly the same.)

4

Thanks for your response.

Im running on the latest 2018.3 Enterprise Edition with all Packages having the newest Versions. UiPath 2018.4 hasn’t passed our company’s it-security controls yet.

grafik

It seems like the Compiler of my UiPath Studio doesn’t support the “asEnumerable” Funktion.

grafik

grafik

As you can see “.asEnumerable” is not even listed in the dropdown-box.

Update:
I have just seen that the purple package icon shown in your Expression Editor has a small, black down-arrow on the right side. Since my System doesn’t show any functions with this kind of icon, maybe this function has been unlocked by a downloaded package?

Is there a need of installing a special package to gain full access to the complete set of .NET functions inside UiPath Studio? At the moment I’m only useing the packages you can see in my first screenshot above.

5

Are you on a recent version of .NET framework? I wonder if being on 4.6 or 4.7 will resolve the issue.

6

Also check this:

image
image705×401 54.3 KB

7

Hi @B.Fehlau

See here for a workaround:

1 Like
8

The manual change of the XAML file helped. In the meantime I also got a solution from UiPath-Support:

Issue:-
Some .Net commands are missing from the studio and can not be used in code.

Resolution:-
We had a known issue with the assembly not loading properly for the version 2018.2 and 2018.3. This has been identified and fixed by the dev team.
If you will use the latest version of UiPath studio you will be able to use the function without any issue.
ExcelData.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“Numbers”).ToString.Trim) ).ToString

So the issue occurred just because of our “old” 2018.3 Enterprise Version. @ClaytonM and @loginerror thanks for your Help.

2 Likes
9

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