Write Sum at the end of every column where there is data using DataTable or Excel directly

Help Studio
, ,
1

Hi There,
I am stuck at scenario where I have DT without any Column Names.

image
image1756×211 15.3 KB

I need to Sum each column where we see the figures.
I have tried AsEnumerable and Compute, but no luck.
Data is huge and dyanmic, also various report would have various column names, so have removed all column names.

2

Hi @Rubot

First you have to create on double variable Ex:TotalCol1

Use this below Inside Assign activity

TotalCol1=YourDatatableVariale.Enumerable.Sum(Function(row) Convert.ToDouble(row(3).ToString)

It will sum the 4th column and store into TotalCol1 variable

Hope it solves your issue

Thanks
Robin S

3

@Robinnavinraj_S thanks for your prompt reply.

I am getting this error message
image

To overcome this issue - I went through the following post (AsEnumerable is not a member of 'System.Data.Datatable' - #3 by lucas.mcgonagle) , but I get error message when I open Main.xaml

4

YourDatatableVariale.AsEnumerable.Sum(Function(row) Convert.ToDouble(row(3).ToString))

fixed with AsEnumerable and ) on end

In case of you need some dynamics we can also create the last row with its sums less hardcoded. Just let us know, if you are interested on this

1 Like
5

I am already using this
DTSample.Enumerable.Sum(Function(row) Convert.ToDouble(row(3).ToString))

But getting error message
image

6

but we talked about AsEnumerable

7

Hi @Rubot
Put it like this
TotalCol1=YourDatatableVariale.AsEnumerable.Sum(Function(row) Convert.ToDouble(row(3).ToString)

8

Assign: Cannot find column 3.

9

Can you share your Input Excel

10

Got it thank you. I was reading wrong sheet. Now working.
Sorry one more help whilst we are on the topic.

if you see first 3 columns they are either text, number(invoice no), data, and all values.

As these columns are not fixed and varies from report to report.
I want to add total of each column at the end of the column. If column contains either text,date, not to caculate them or print 0 at the end of the column and total of those with valid values.

image
image1035×123 23.4 KB

If I run the current code on current columns in Image, I get following error message - Input string was not in a correct format.

I guess first column has text that is why. but reports will have text columns before and all later once will be values, but cannot define which report will have how many

11

Hi @Rubot

We can achieve this by using while method

First you have to create a integer variable Ex ColumnCount

ColumnCount=YourDatatable.Columns.Count
it will store the total number of columns in ColumnCount variable

drag and drop the while activity and create another integer variable like i, set condition as i=ColumnCount

And **Its difficult to explain each every step if you share your input excel file i will develop a workflow as per your requirement and send it to you asap

Thanks
Robin

12

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