How could i deal with the excel file problem？ summary the row data

Help Studio
, , , , , , , ,
1

dear all , i have an excel file as follow :slight_smile:
1
in column a ，if the cell is same and in column c the data1 is the same， then add the data2 into one cell ，the result like following picture
2
anyone could help me ？ give me a demo xaml code， thanks a lot in advance！！！

pay attention： only the row name is the same ， and the number in row data1 is the same， we just add the number in data2， if name is the same，but the number in data1 is not the same ，we do not add， anyone could help me ？ thanks a lot in advance！！！

anybody could give me a help？

2

Hi @chrisjiyiwei

Can you try the following?

image
image900×581 27.5 KB

Code:

(From row In dt_Input.AsEnumerable()
                       Group row By Name = row.Field(Of String)("Name") Into Group
                       Let Time = CInt(Group.First().Field(Of Double)("Time"))
                       Let Data1 = Group.First().Field(Of Double)("Data1")
                       Let Data2Sum = Group.Sum(Function(r) r.Field(Of Double)("Data2"))
                       Select dt_Input.Clone().Rows.Add(Name, Time, Data1, Data2Sum)).CopyToDataTable()

Input:
image

Output:
image

Sequence7.xaml (10.4 KB)

Cheers!!

3

hey Irtetala ,

thanks a lot . maybe you misunderstand me , in the row data2 i just need the data 30+3.4 not the sum number 33.4 . how the code change.

4

Hi @chrisjiyiwei

Here is other approach

Forum.zip (246.4 KB)

image
image874×1366 56.8 KB

Hope it helps!!

5

@chrisjiyiwei

You can try this

(From row In dt_Input.AsEnumerable()
                       Group row By Name = row.Field(Of String)("Name") Into Group
                       Let Time = CInt(Group.First().Field(Of Double)("Time"))
                       Let Data1 = Group.First().Field(Of Double)("Data1")
                       Let Data2 = String.Join("+", Group.Select(Function(r) r.Field(Of Double)("Data2")))
                       Select dt_Input.Clone().Rows.Add(Name, Time, Data1, Data2)).CopyToDataTable()

Output:
image

6

what is the bug Assign: Specified cast is not valid."

7

Can you share your Excel file @chrisjiyiwei

Try this

Sequence7.xaml (10.4 KB)

8

hey irtetala,

when i open the xaml file , it generates this error

111
1111232×596 27.8 KB

9

Hi @chrisjiyiwei

=> Use Rad Range Workbook to read the excel and store it in an DataTable say dt.
=> Use the below syntax in Assign activity:

dt= (From row In dt.AsEnumerable()
                       Group row By Name = row.Field(Of String)("Name") Into Group
                       Let Time = CInt(Group.First().Field(Of Double)("Time"))
                       Let Data1 = Group.First().Field(Of Double)("Data1")
                       Let Data2 = String.Join("+", Group.Select(Function(r) r.Field(Of Double)("Data2")))
                       Select dt.Clone().Rows.Add(Name, Time, Data1, Data2)).CopyToDataTable()

=> Use Write Range Workbook to write it back to excel in different sheet.

Workflow:

image
image880×581 24.5 KB

xaml:
Sequence.xaml (7.8 KB)
Input:
image
image514×757 16.8 KB

Output:
image
image489×758 12.7 KB

Hope it helps!!

10

hey i use the Assign activity, it meets error spcified cast is not valid what is this bug?

11

Hi @chrisjiyiwei

Make sure you replcae the datatable variables with yours. Try updating UiPath.System.Activities to latest version. I have shared the workflow file xaml for your reference too.

Regards

12

when i open your shared xaml file , it meets document is invalid , how could i fix it ?

13

Hi @chrisjiyiwei .

May be you could replicate the process flow with the help of workflow image. I have the output variable of the Read Range Workbook Excel data in assign acitivty.

Regards

14

Hi @chrisjiyiwei

I am sharing the zip file you can extract it and run the file

Summary Row Data.zip (49.7 KB)

Data.xlsx (9.7 KB)

Cheers!!

15

Hi @chrisjiyiwei

Please Use This Query

DT.AsEnumerable().GroupBy(Function(r) Tuple.Create(r(0).ToString,r(2).ToString)).Select(Function(g) DT.Clone.Rows.Add({g.Key.item1,g.First().item(1),g.Key.item2}.Concat({String.Join(“+”,g.Select(function(x) x(3).ToString))}).Toarray)).Copytodatatable

image
image795×182 10.6 KB

16

only the row name is the same ， and the number in row data1 is the same， we just add the number in data2， if name is the same，but the number in data1 is not the same ，we do not add， anyone could help me ？

17

hey irtetala , we have to pay attention only the row name is the same ， and the number in row data1 is the same， we just add the number in data2， if name is the same，but the number in data1 is not the same ，we do not add.

18

@chrisjiyiwei

image
image1152×763 108 KB

Please check the below output as you already given the expected output it meets the same

image

19

I use this zip file , but it not right.
for example , if the excel table like follow, the name b have two row, but the number in row data1 is not same , it is 2.32% and 2.27% , we need not add the data2 , but your code , the output b is 10E+20E in row data2
|Name|Time|Data1|Data2|
|a|3M|2.25%|1E|
|b|3M|2.32%|10E|
|b|3M|2.27%|20E|
|c|3M|2.28%|0.5E|
|d|3M|2.35%|4E|
|e|3M|2.35%|1E|

20

for example , if the excel table like follow, the name b have two row, but the number in row data1 is not same , it is 2.32% and 2.27% , we need not add the data2 , but your code , the output b is 10E+20E in row data2
|Name|Time|Data1|Data2|
|a|3M|2.25%|1E|
|b|3M|2.32%|10E|
|b|3M|2.27%|20E|
|c|3M|2.28%|0.5E|
|d|3M|2.35%|4E|
|e|3M|2.35%|1E|