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.
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.
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.
Seperate the Excel
syntax : DT.AsEnumerable.Where(Function(r) r(“Error Message”).ToString.Equals(“email1”)).CopyToDataTable
syntax : DT.AsEnumerable.Where(Function(r) r(“Error Message”).ToString.Equals(“email2”)).CopyToDataTable
Syntax :DT.AsEnumerable.Where(Function(r) String.IsNullOrEmpty(r(“Error Message”).ToString)).CopyToDataTable
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.
How to pass excel name dynamically.
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
Hi @ton I have attached the input sheet along with output screenshot for your reference.
Input Data (1).xlsx (21.3 KB)
How to do dynamically separate the different sheet based on email id column
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.