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