How to build data in excel with condition

Help Studio
, , , , ,
1

Dear Community,

Currently i build the workflow as below whereby i use get text tool to extract data from web browser and add row to be written into the excel file:
image

However, my outcome in the excel will be something like this:
image

There will be empty row if no result found from web since i only put in the one side (left side) of the workflow as per screenshot below:
image

May i know how can i build the workflow on the right side as per screenshot below so that if does not contain " one record found", fill in the excel cell with o instead.
image

This is my desired outcome in excel as per screenshot below:
image

Is it possible to do this in this case?

Thank you.

2

Hi @SH_94 .

Have some queries,

If you are adding a row only if particular text is found how was the other rows get added with empty status value

If we want to update records after all the scrapping is done then these are two methods we can follow,

Method1

  1. Before writing the datatable to the excel do the steps below,

Let’s assume dt your datatable variable name

  1. Drag and drop the invoke code activity.

  2. In edit arguments, create a variable dt, set direction as in/out and pass value as dt.

  3. In edit code type the following,

dt.asenumerable.where(function(row) string.isnullorempty(row(“Status”).tostring)).tolist.foreach(sub(row) row(“Status”)= 0)

Write the datatable into the excel using write range activity.

Method2

  1. Use for each row activity and pass the datatable variable.

  2. Use an if activity inside for each row.

  3. Condition for if condition

string isnullorempty(Currentrow(“Status”).tostring)

  1. In then part of if activity use an assign activity

Currentrow(“Status”) = 0

Write the datatable into the excel using write range activity.

Hope this helps.

Thanks

1 Like
3

Hi @prasath_S ,

I quite interesting on the method 2. Could you screenshot of workflow based on the step you have mentioned so that i better visualize on how to do it.

However, currently i use “get text” follow by “add row” and then write arrange activity.

May i know how to change to method 2 ? Appreciate if you can screenshot on how to do this process.

Many thanks again your help.

4

Hi @prasath_S ,

Reason being i consider adding “0” for those no result found is i noted that the result generated will be not correct because UiPath will add row by row whereby it is not accurate result.

Or u may ignore on what i did because i may do the wrong way. Need some guidance on how to do it on the right way.

Basically the whole process is i ask UiPath to read the excel data and key in into the web browser. After that i want to get some text if there was record found. But this cause the problem whereby data generated not accurate as i did not consider those no result found.

Hence i am thinking of adding hardcoded value "0 " for those no result found to try if the data generate would be accurate since i consider both scenario.

Not sure if you understand it or not… I will try to explain it if you not really understand the logic that i have explain.

Thank you.

5

@SH_94 I don’t have laptop with me so I can’t help with the screenshots,but If I understand correctly the process is in for each row activity,if it is we don’t have to use add datarow activity(remove add datarow activity), instead use an assign activity and give currentrow(“Status”) = “one record found” in then part and in else part give currentrow(“Status”) = 0

And write the datatable (variable used in read range activity) back to the excel.

You can also try the method1 I gave as it is easy and faster.

Thanks

6

Hi @prasath_S

Just want to recap on this question, if no particular text found, i want to make it as zero. Hence, is it possible to use method 1?

Thank you.

7

@SH_94 yes it will change all the empty value in Status column to 0, hope you are using the write range at the end of the process use the invoke code before that.

I have solved similar query to this on other topic , you can refer that for how to use the invoke code,

Thanks

8

Hi @SH_94

You can try this another add data row in else like this
image

Regards
Sudharsan

9

Hi @prasath_S ,

Do i need to perform this step - to remove the add datarow activity if choose to try method 1?

Thank you.

10

@SH_94 keep everything, at the end (before write range)put the invoke code activity.

You are using write range activity at the end of the process ?

Thanks

11

Hi @prasath_S ,

Yes, i use the write range in this case and i have error after trying the first method. May i know how to fix in this case for the screenshot below?

image
image1280Ă—720 73 KB

This is my formula under the edit code:
dt.asenumerable.where(function(row) string.isnullorempty(row(“Attachment Found”).tostring)).tolist.foreach(sub(row) row(“Attachment Found”)= 0)

While for argument, i put as below:
image

Actually why i want to keep as zero for No records found is UiPath seem to give me wrong data as per screenshot below ( this is before i use the invoke method).
image

Technically, if no records found, it should give me blank . But in this case, Uipath seem to capture wrongly.

Do you understand on what i am saying and trying to do here? Let me know if you not understand on it and i will present it in more understanding and clear way.

Many thanks in advance

12

Hi @Sudharsan_Ka ,

Could you briefly explain how this concept work?

13

@SH_94

For the error, I can see that you have passed string, we have to pass datatable extractdetail.

Create as argument called dt of type datatable and pass extractdetail.

And as for the wrong details updated on the excel, I could not find where it is getting wrong with the screenshots, can you share some more screenshots of the process and what are doing with add datarow.

Thanks

14

You have already added condition to check the rows whether it contains “one record”
If yes you will add column 1 and column 2 value
If not you want to add 0 in the second column which is blank

So In add data row in else added the same column 1 and in column 2 as “0”

{Correct me if I misunderstood the task }

Regards
Sudharsan

15

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