Status Column Update in Excel

Build
, ,
#1

Hello All,

My requirement is like from excel I need to add entries to a web application and update the status like “PASS” or “FAIL” in status column of excel.

I have achieved this part. The problem comes when I again process the sheet I only want to pick the row which have status as “FAIL” and then add entries to the web application update the status from FAIL to PASS and the same status needs to be updated for that particular row in the Original Excel. Please help me with the last piece like: As I have filtered the datatable and performing the action and updating the status but how to update the status for that row in Original Excel sheet.

#2

Hi
Welcome back to uipath community
I hope you would have obtained a Datatable and then iterated through each row one by one with a for each row loop and then entered to a web application
—so inside the for each row loop use IF as first activity and mention like this
row(“yourcolumnname”).ToString.ToUpper.Equals(“FAIL”)
If the above condition passes it will go to THEN part where we can keep all the activities so that it will update only the rows with Fail as value in status

Cheers @

#3

@sushantk,

I assume you are working with REFramework, inside the Process state, you can check the transactionitem(“yourStatusColumn”).Tostring.Trim.ToLower = “fail” or transactionitem(“yourStatusColumn”).Tostring.Trim = string.Empty then you can proceed with your process for that row.

Simply you can check in other method as

transactionitem(“yourStatusColumn”).Tostring.Trim.ToLower <> “pass”

#4

Sorry @sarathi125 I am not using ReFramework for this process. Ideally I should have used the same.

#5

If you have not completed the development fully, then I recommend you to migrate this process to ReFramework with Tabular data for more stability during production time.

If you want to continue withour REFramework then you have to follow the steps mentioned by @Palaniyappan in this thread

#6

Hi @Palaniyappan,

Expecting you to be the first to reply on my topic! You are doing an amazing job!!!
Now coming to the point:

I am using Read CSV activity reads the excel into a Datatable A and then filtering it on the basis of status “FAIL” and storing it into Datatable B, performing the operation into web application and updating the status in Datatable B.

Now what I want is as the Datatable B rows are updated from Fail to Pass the same should br updated to Datatable A for corresponding rows so that I can write it into CSV file usinf write CSV activity.

#7

@sarathi125 I have just replied to @Palaniyappan what I am doing actually. Can you please go through it and help me out.

Sorry as of now we can’t migrate to ReFramework but in later phase will do that for sure.
@sarathi125 @Palaniyappan you guys are doing a wonderful job in community as in most of the post I see your comments and solutions.

Thanks In Advance.

#8

Yah this can be done with lookup datatable activity
So now we have datatable A with old values and datatable B with updated values
—use a for each row loop and mention the datatable A as input
—then inside the loop use a LOOKUP DATATABLE ACTIVITY where in the property panel mention
Input - row(“Status”).ToString
Datatable - datatable B
Column name - “column name common in both datatable”
Target Name - “Status”
Output result - str_output

—now followed by this in a assign activity
row(“Status”) = str_output.ToString

Cheers @sushantk

1 Like
#9

Do we have any Key column with unique values in the csv file or in the datatableA?

#10

Yes it’s PERIOD START COLUMN

#11

Then this would work
We can mention any one columnname in lookup

Cheers @sushantk

#12

Just a quick check @Palaniyappan where this Output result should be put. I guess it should be in Cell Value.

1 Like
#13

Exactly
@sushantk

#14

@PalaniyappanThe execution is failing at Assign activity. So I tried removing To String function. The execution was completed but the status column was completely blank.

#15

Can I have a view in the property panel of lookup datatable activity with a screenshot
Cheers @sushantk

#16

Sure @Palaniyappan. I am not allowed to share the workflow otherwise I would have done so. I have attached the LookUp Datatable properties as aasked.

SnapShot
SnapShot.PNG629×675 19.2 KB

#17

Blank.7z (9.1 KB)

@Palaniyappan

I am attaching a dummy one though where I am modifying the excels manually to test the logic.

1 Like
#18

here you go with the xaml
hope its resolved
Blank.zip (12.0 KB)

Cheers @sushantk

2 Likes
#19

Let me try this @Palaniyappan!!
Thanks for your help will get back to you asap.

1 Like
#20

Thanks @Palaniyappan It’s working!!!

1 Like