How to use for each row when the variable is at the column instead of row

Help Studio
1

My excel sheet looks like this since I can’t upload it.

Item Number
Chicken 2
Ham 5
Fish 3

Sheet1 Sheet2 Sheet3
…

I already did the excel application scope and read range all 3 sheets and put in DataTable1/2/3 respectively. How do I sum for example ham across all 3sheets? I tried using For each row and it doesn’t work. After summing out the number of fish, I will then put the total sum of fish into a google form which I know how to do. And also what variable type I need to do cause I keep messing up between string and integer.

2

Hi @cxx ,

Logic :

  1. Merge all the Data Tables in 1 Data Table.

  2. On merged Data Table, Use Group By LINQ where Key=Item & Sum Number.

Refer below post for LINQ:

https://forum.uipath.com/t/linq-query-please-help/312601/2

1 Like
3

Hi @cxx

welcome to forum

You can try this way.

  1. After reading the datatable, u can merge the datatables into single one using Merge DataTable activity and store the result in resultant datatable , let’ say dt_result

  2. To get the sum of particular Item, u can try this assign activity [ below example is for taking the sum of item Ham.

item_sum = dt_result.AsEnumerable().Where(Function(row) row(“Item”).ToString.Equals(“Ham”)).Sum(Function(row) CInt(row(“Number”).ToString)).ToString

where item_type datatype is String.

if u need to check for any other item other than ham, u can replace “Ham” with other item name as per the assign activity.

Hope it helps

Regards,
Nived N
Happy Automation

2 Likes
4

Hi, thanks for your reply. i don’t understand item_sum = dt_result.AsEnumerable().Where(Function(row) row(“Item”).ToString.Equals(“Ham”)).Sum(Function(row) CInt(row(“Number”).ToString)).ToString

Is there anyways for me to sum every single item in another easier method? If i want to put in trycatch into my program,for eg, when there is no number beside my item, there will be a message box show warning and also stop the program.

5

Thanks for your help. Is there any simple way to sum my data without using LINQ method as i do not know how to do it

6

you can try following:

otherwise use filter datatable and implement the summing up by your own code

7

Give try to this package :

8

Hi @cxx
yes there is simple way to do it, but linq query will helps to do your thing much faster than normal way of doing

for doing normal way:

  1. after reading the excel file and storing in datatable, use filter datatable activity to filter the Item Column based on value u want to filter.Then store the output in dt3 datatable variable

  2. Now use assign activity to intialize a int32 variable sum_data =0

  3. now use for each row to loop through each row in dt3,
    inisde that use assign activity

sum_data = sum_data+Cint(row(“Number”).ToString)

In this way too u can try

Regards,
Nived N
Happy Automation