Consolidate Rows in Data Table

I’d say just do a couple of loops through the datatable using a counter either with while loops in a sequence or using decisions in a flowchart. Reference the rows like datatable(counter)(0).

First run, just get all the unique names. If it’s a new name put in an array. Check the array each loop and keep adding the uniques to the end.

The loop through the array, match the name in the datatable column 0 and then if it matches have a second array that matches the first (but of type integer or double).

At the end, just write out the arrays or else put into a new datatable or whatever you need to do with them.

Try this (googled and modified)

DataTable x = new DataTable(); //read range
IEnumerable<DataRow> dtrow = x.AsEnumerable();
listdt = dtrow.GroupBy(dtl => dtl.Field<string>("Name"))
                .Select(dtg => new dtg
                {
                     dtl.Field<string>("Name") = dtg.Key,
                     dtrow.Sum(d => d.Field<double>("#Apples"))                    
                }).ToList();

That’s neat - would that be done in a code snippet activity?

Hello,

I would recommend you to use datable.Compute() For this type of operation.

Here is an example that you could put in an Invoke Code Activity with two arguments (dtIn and dtOut ) Both as System.Data.Datatable

Note that i had to change your DataCollum Title from “#Apples” to “Apples” Since it conflicted for the calculation

'Create a Datatable with distinct names
dtOut = dtIn.DefaultView.ToTable(True,“Name”)

'Add Column for Total Apples
dtOut.Columns.Add(“Total Apples”

‘Fill Total Collumn For each Row
For Each row As DataRow In dtOut.Rows
row(1) = dtIn.Compute(“sum(Apples)”,"Name=’" & row(0).ToString & “'”)
Next

Let me know if you need further explanation or example without using Code activity.

Cheers

Thank you @vvaidya , @David_Johnston and @Florent_Salendres

An xaml example of @vvaidya 's and @Florent_Salendres solutions would be a great help.

I cannot find invoke code activity. Would these codes use the invoke com method?

Hi,

Find here your workflow that I modified.consolidateTableCompute.xaml (12.6 KB)

Cheers

4 Likes

Brilliant. Thank you very much @Florent_Salendres


Hi @Florent_Salendres Sorry to ask so many questions but…

How would the following line need to be changed to build in a second “Oranges” column which also needs to be summed?

dtAllRows.Compute(“sum([#Apples])”,“Name='” & row(0).ToString & “'”)

(Updated xaml attached)consolidateTableCompute_app&orang.xaml (13.2 KB)

Hi @Florent_Salendres

I worked it out so please ignore my previuos post. Again, thank you for your help.

consolidateTableCompute_app&orang.xaml (13.6 KB)

The apples and oranges xaml is attached for reference

1 Like

Hi @tobor @Florent_Salendres, @vvaidya

the above solution is when a datatable is actually built/configured with in uipath, but in case when a datatable is a result of a read from a csv/xls file, in which case the columns are by default of strings…

How to calculate for a specific column in that case which has a int/double values…

Below code is not working,

row(1) = datatable.Compute(“sum(Cdbl([Total Count]))”, “TranType='” &row(0).ToString & “'”)

with below error,

Assign : Syntax error in aggregate argument: Expecting a single column argument with possible ‘Child’ qualifier.

If I remove Cdbl conversion, row(1) = datatable.Compute(“sum([Total Count])”, “TranType='” &row(0).ToString & “'”)
The error is, Assign : Invalid usage of aggregate function Sum() and Type: String.

This is my workflow,Main.xaml (10.8 KB)

Any help ?

Thanks!

Is there also way how to get sum to one variable not to DT?

I have

2
3
4

not depending on Column header. Also I dont know how many rows should there be.

Hi @Mario. Do you mean like this?sumToVariable.xaml (7.2 KB)

1 Like

Working!!! :slight_smile:

@Dilli right, I have the exactly same issue as he did, is there any updated solution?

any updates ?

Hi @Janani_Durai,
In build data table you can create a column.
If you want there it self you can add the column values.

In add data row you can add row in the datatable.

Regards,
Arivu

HI @Janani_Durai,

Refer this xaml file.
DataTable.xaml (12.6 KB)

Refer this links

https://activities.uipath.com/docs/add-data-row

Regards,
Arivu

@arivu96 - this is not were I have stucked. Am filtering the data’s from one excel sheet by column specific and adding the data’s dynamically to datatable and after that I have to write those data’s to excel sheet. Its working fine but its overwriting the data in the excel sheet row. So I just want to how to add multiple rows into a data table using add data row.

Hi @Dilli
Did you find the solution I am facing the same issue.

Hi,

This works perfectly when the type of the data column is predefined as int32. My situation is that I am outputting a datatable from Excel Application Scope and the data type of the column is Object. Using the solution that you provided, I got an error saying “assign invalid usage of aggregate function sum()”. could you provide an example showing how/where can I change the data type of the column while simulating your solution? Thanks a lot!

Best reagrds,
Lavina