How to separate the rows information based on email column in Excel using uipath

[Baby123 (Baby)]

Hi Team,
I have one excel file. I want to separate the rows of data based on excel column and separated data need to write in separate excel sheets and send to separated Excel sheet to respective mail id.

I have attached the input data screenshot for your reference.

Kindly suggest.
In my input data I have 2 mails so I need to create the 2 sheets based on emails I’d and empty email id should be created with business email.xlsx
Ex. Separated like this ,
Email1.xlsx in this excel have only email1 rows data
Email2.xlsx in this excel have only email2 relaty rows information.
If emild I’d blank - need to create the Excel file and along with rows information.

Kindly suggest

[ton (Anthony)]

Try using Filter Data Table activity then Excel Application Scope Activities (Read Range, Write Range):

Filter Data Table - https://docs.uipath.com/activities/other/latest/workflow/filter-data-table
Excel Application Scope - https://docs.uipath.com/activities/other/latest/productivity/excel-application-scope

If you could share that excel file you’ve shown, I might be able to make a sample workflow.

[Dinesh_Guptil]

Hi @Baby123

Seperate the Excel

For Email1

syntax : DT.AsEnumerable.Where(Function(r) r(“Error Message”).ToString.Equals(“email1”)).CopyToDataTable

For Email2

syntax : DT.AsEnumerable.Where(Function(r) r(“Error Message”).ToString.Equals(“email2”)).CopyToDataTable

For Null
Syntax :DT.AsEnumerable.Where(Function(r) String.IsNullOrEmpty(r(“Error Message”).ToString)).CopyToDataTable

[Baby123 (Baby)]

Hi @Dinesh_Guptil @ton ,

I have gave the example only.
In real time we don’t know which mails are there in e-mail I’d column.

If we have different email id in e-mail I’d column then need to create the separated Excel file.
For example in my excel have 5 different email ids and 2 blank emails in email id column.
I want to i want 7 excel sheet with respective rows information.

Kindly suggest.
How to pass excel name dynamically.

Thanks,
Baby

[Dinesh_Guptil]

Hi @Baby123

same as the above syntax

DT.AsEnumerable.Where(Function(r) r(“Error Message”).ToString.Equals(“Type Of Email”)).CopyToDataTable

Give the type of email So you will get 5 sheets and 2 sheets for null

[Baby123 (Baby)]

Hi @ton I have attached the input sheet along with output screenshot for your reference.
Input Data (1).xlsx (21.3 KB)
Output -

Kindly suggest.
How to do dynamically separate the different sheet based on email id column

[ton (Anthony)]

Hi @Baby123

Put this on your ‘My Documents’ or ‘Documents’ to serve as template for the workflow presented below, or you can just simply put a new excel file with filename ‘ExcelFile.xlsx’ and a sheet on it ‘Template’
ExcelFile.xlsx (8.1 KB)

This is the sample workflow I created:
Dynamic Sheet by Column Value.xaml (25.1 KB)

The output will be created on your local ‘Documents’ with filename ‘Output_ExcelFile.xlsx’

I did put annotations to explain briefly what the certain activity does.
If you or someone find something that can be optimized in the solution, let me know too.

Best Regards,
Anthony