Segregating an excel file depending on its "Branch" column using LinQ

Help Activities
, ,
1

Hi,

Good Day.

May I know how to segregate this excel file depending on its branch using LinQ to make the execution faster where each Branch will be put on a separate sheet? Kindly see below for the desired outputs and attached file (Sample_Reports.xlsx)

Desired outputs:

The image is a spreadsheet showing payment transactions for Branch 1, including details such as payment date, type, reference number, amount, transaction status, and date for the period around early February 2025. (Captioned by AI)
The image is a spreadsheet showing payment transactions for Branch 1, including details such as payment date, type, reference number, amount, transaction status, and date for the period around early February 2025. (Captioned by AI)1182×784 21.2 KB

This is a spreadsheet showing the payment records for Branch 2, detailing payment dates, types, reference numbers, amounts in PHP, transaction statuses, and corresponding dates. (Captioned by AI)
This is a spreadsheet showing the payment records for Branch 2, detailing payment dates, types, reference numbers, amounts in PHP, transaction statuses, and corresponding dates. (Captioned by AI)1099×782 14.6 KB

The image shows a spreadsheet detailing payment transactions, including dates, branch information, payment types, reference numbers, amounts in PHP, transaction statuses, and corresponding dates for Branch 3. (Captioned by AI)
The image shows a spreadsheet detailing payment transactions, including dates, branch information, payment types, reference numbers, amounts in PHP, transaction statuses, and corresponding dates for Branch 3. (Captioned by AI)1038×784 18.3 KB

The image shows a spreadsheet detailing payment transactions for Branch 4, including columns for payment date, branch, type, reference number, amount in PHP, transaction status, and date, with a variety of statuses such as pending, successful, and failed. (Captioned by AI)
The image shows a spreadsheet detailing payment transactions for Branch 4, including columns for payment date, branch, type, reference number, amount in PHP, transaction status, and date, with a variety of statuses such as pending, successful, and failed. (Captioned by AI)1095×781 21.3 KB

The image shows a spreadsheet detailing multiple transactions at Branch 5, including the payment date and time, payment type, reference number, amount in PHP, transaction status, and the transaction date, with a highlighted tab for 'Branch 5' at the bottom. (Captioned by AI)
The image shows a spreadsheet detailing multiple transactions at Branch 5, including the payment date and time, payment type, reference number, amount in PHP, transaction status, and the transaction date, with a highlighted tab for 'Branch 5' at the bottom. (Captioned by AI)1036×783 14.3 KB

Sample_Report.xlsx (28.9 KB)

Thank you.

Best regards,
Llessur

2

@Llessur
have a look here for some general concept options:
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

Linq Approach:

Assign Activity:
TableList | Datatype: List(Of DataTable) =

(From d in YourInputDataTableVar.AsEnumerable
Group d by k=d("Branch").toString.Trim into grp=Group
Select t=grp.CopyToDataTable).toList

Then loop over TableList (e.g For each activity) and write it down to excel

3

Hi @Llessur

Yes you can use the LINQ Expression, check the below steps,
→ Use the Read range workbook activity to read the Input excel and store in a datatable called dt_Input.
→ Then use the For each activity and give the below LINQ Expression in For each,

dt_Input.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("Branch")).Select(Function(group) group.CopyToDataTable()).ToList()

→ Open properties of For each and in the argument type dropdown select the System.Data.Datatable.
→ Inside for each insert the assign activity to get the name of the branch by using LINQ Expression,

- Assign -> SheetName = dt_Input.AsEnumerable.FirstOrDefault(Function(x) Not String.IsNullOrEmpty(x("Branch").ToString()))("Branch").ToString()

→ After assign activity insert the Write range workbook activity and give the path of the input excel file, SheetName variable in the Sheet name field and output of for each activity in datatable field.

Check the below workflow for your reference,

This image depicts a sequence workflow in UiPath that reads data from an Excel workbook, processes it in a loop to group by a specific attribute, and then writes the processed data back to specified sheets in an Excel workbook. (Captioned by AI)
This image depicts a sequence workflow in UiPath that reads data from an Excel workbook, processes it in a loop to group by a specific attribute, and then writes the processed data back to specified sheets in an Excel workbook. (Captioned by AI)909×668 51.5 KB

Hope it helps!!

1 Like
4

Hi @Llessur

BranchSeperation.zip (44.0 KB)

The requirement works!

Hope this helps

1 Like
5

Hi @Sanjay_Bhat what would be the work around if I will try to segregate the Branches to different excel files? Thank you

6

@Llessur

image
image343×121 2.84 KB

You can pass something like this!

Hope this helps!

1 Like
7

I tried to do the same thing. Thank you so much.

1 Like
8

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