Update Excel Report

Hi Good day,
I have Employee Name | Status Column

image

Case 1.
if in Employee Name dont have any Status Bot must write/Fill the status “Active”.

Case 2.
If the status is inactive, Discontinue and Graduated
Bot must delete The entire Row and Transfer the record to another Sheet.

and the record must be auto adjusted so that no empty Row record in the Excel Sheet.

the output Should looks like this
Sheet 1
image

Sheet 2.
image

Thank you

Hi @Vincent_Nuestro
is yellow cell color is there for all cells whose status is Inactive, graduated or discontnued?

May i know about that ?

Regards,
Nived N

@NIVED_NAMBIAR
Yes, I just Highlighted them only. But it does not matter to the data.
Ex:
Vincent 3 - Inactive
Vincent 6 - Discontinue
Vincent 7 - Graduated

I must get off them to Sheet 1 and transfer to Sheet 2.

Hi @Vincent_Nuestro
as per your requirement, i had designed the workflow
Main.xaml (13.4 KB)
excel sample i used
sample.xlsx (9.5 KB)

Hope it helps you

Mark it as solution if it solves ur query

Regards,
Nived N
Happy Automation

1 Like

@NIVED_NAMBIAR
Hi can I have instead the screenshots of the workflows, I can’t Open the activities because my the uipath version that were using is 2018.4.1. :frowning:

thank you

Hi @Vincent_Nuestro
this is the logic i had implemented

  1. Read the excel file and store in dt1

  2. Use Invoke code activity with dt1 as In/Out argument with below code
    dt1.AsEnumerable().Where(Function(row) row(“Status”).ToString.Equals("")).ToList.ForEach(Sub(row) row(“Status”)=“Active”)

  3. Use the below assign activities to filter the datatables having status column as Active or other as follows

For filtering rows having Active Status column
use the assign activity

dt2= dt1.AsEnumerable().Where(Function(row) row(“Status”).ToString.Equals(“Active”)).CopyToDataTable

simmilarly for filtering the rows having status as Inactive , Discontinued or Graduated

use the assign activity
dt3=dt1.AsEnumerable().Where(Function(row) row(“Status”).ToString.Equals(“Inactive”) Or row(“Status”).ToString.Equals(“Discontinued”) Or row(“Status”).ToString.Equals(“Graduated”)).CopyToDataTable

write the dt2 and dt3 into excel using write range

Result will be obtained as below
image

image

Hope it helps you

Regards,
Nived N
Happy Automation

3 Likes

Actually He need to delete the row you did like save it in another excel sheet but he needs to delete the row in the same sheet.

Actually I also try to delete the row but am fail so i also need solution

I use if condition instead of LINQ and add data row to save these files in the another sheet2

Thanks
Chethan P

Hi @copy_writes
instead of deleting, we can do the filtering of datatable and then he can write in same excel sheet which would be simmilar to the same effect of deleting

Hope you got the logic @copy_writes

Regards,
Nived N

2 Likes

Am Impress every time when you give the solution This time am fail to think like you
Genius :clap: :raised_hands:

Thanks You very much :love_you_gesture:

1 Like

@NIVED_NAMBIAR
Hi Sir, Im just wondering where is the Code or activity to delete/Remove the entire row?
Your activity worked on me, but the thing is it just only copy the row that contains(Inactive, Discontinue,Graduated) to new Sheet. But it did not removed to the original sheet

Hi @Vincent_Nuestro
You can delete the data, but i will tell the log what i had implemented.

Let’s say u had original data in sheet1. Now using read range, u read the excel and store in dt1

Now you update the status column as Active and then filtering the datatable so that it split into two tables, one table contains only rows having Status as Active , let’s say it is in dt2 other table is storing the rows having the Status as Inactive, Discontinued, Graduated as dt3

Now u are writing to the original sheet Sheet1, it will be having same effect of deleting, it will overwrite the data in the Sheet1 excel to the data contained in dt2. thus sheet1 contains only data which is of Active Status

Hi @NIVED_NAMBIAR

I did exactly the same as per your instruction.
And this is the Output

SHEET 1
-It Did not remove the record from Sheet 1.

image

SHEET 2
-works perfectly in Sheet 2.
image

need to remove the record with (Inactive, Discontinued, Graduated) from SHEET 1

Hi @Vincent_Nuestro
i think before writing to Sheet1, first delete the sheet and then write the dt2 into Sheet1 using write range

Hi @NIVED_NAMBIAR ,

I already did sir, But it did not work. :frowning:

hi @NIVED_NAMBIAR @copy_writes

Can i ask for your Help again?

Sure

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