How to get the specific dynamic data from main data table

Help Studio
, , , ,
1

Hello everyone, I have a question about the excel data, I would like to share my workflow first

  1. Scrapping data (main Datatable) from the website.
    image
  2. For the example, I would like to copy the dynamic range by using the data from the excel sheet (the data is specific date 2022/10/07) and the range that I want to paste in the formatted excel is 2022/10/07 - 2022/10/13 ( always +7 days)+ 7 Passwords including also.
    image
    Thank you for your advance.
    Regards
2

You can pick random number or current seconds from system time. and check that value is less than or equal to number of elements in datatable.
eg.
you got 4 as random number
value = 4+7 =11

you have 20 elements in excel so this number consider as correct and aply your further logic.

you got 17 as random number
value = 17+7 = 24

you have 20 elements in excel so it is not valid so you can try to get another number and to the same check again.

1 Like
3

@dhanraj.nimbalkar Many thanks for your Idea, I will try this

4

Hi @poomnattawat ,

Please do mark the appropriate post as the Solution if you get your topic solved so that others can benefit from this topic.

If the topic is still open, Could we ask you to provide sample input data and it’s expected output in the form of excel files.

We would be help you faster and provide/suggest you with the solution approach.

5

@supermanPunch Hello and thank you for your suggestion,

I already created the excel file as per your request, please find the details,

Example.xlsx (10.0 KB)

Many Thanks in advance

Regards

6

@poomnattawat ,

Could you let us know How is the Date 16/10/2022 the Starting date in Output ? Is it 7 days from current Date then we need to pick 7 Dates from that date ?

image

7

@supermanPunch Hello and sorry for the late reply
Usually, I use the read-cell function from customer documents, It will show the detail on the cell like this
image
and the data may come out in the string data
I would like to match the date from the customer file with the date from the website and copy the data like the example excel that I uploaded

Regards

8

@poomnattawat ,

Could you Check the workflow Below :
Filter DT.zip (11.1 KB)

I do think there are still few things that are not cleared. But you could give the workflow a try and let us know if you are getting the expected Output or not.

Also, we might need to change the Date Conversion method on Customer Date if you get an error when using your value from Customer Excel.

9

@supermanPunch Hello and sorry for the late reply

I have some questions to ask you about the customer’s date, maybe we misunderstood the point

For example, if the customer’s date is 11/10/2022 the output table should start from 11/10/2022 to 17/10/2022, but in your example workflow your assign the (customerDate) as 11/10/2022 but the output shows the

image

maybe it cannot use for my workflow

and another question after I try to use your expression the errors show below

image

I just changed the variable from (customerDate) to my variable (StartDate) and the error shows

Regards

10

@poomnattawat , It seems that my Assumption was wrong.

However, It should only be a small correction that needs to be done. Could you check the updated workflow below :
Filter DT.zip (11.2 KB)

Let us know if it gives out any errors after you have tested it with your data.

11

@supermanPunch Hello

Still errors in my workflow, and another problem show

image

The application date should stop on 08/11/2022 but it grabs date that not related to our expected data as shown in the picture

Regards

12

@poomnattawat

What was the customer date supplied ?

13

@supermanPunch Hello

They supplied the date like this one

image

I have tried to change the customerDate to this format but the error appears

Regards

14

@poomnattawat ,

image
image738×456 21.7 KB

As you can see in the image above, we used a customerDate variable to store the input date from customer. It means you don’t need to modify the expression with the date value received but just assign the date value to this variable.

Here, the error is showing up because of Datatable not being declared or not using the right Datatable in the Query. In the workflow that was provided, the Datatable variable name was DT, you need to change the Datatable variable according to the variable name that you have used.

Also, Not sure if the Logic/Condition that you are trying to explain is right or still being misunderstood.

For the Date, 14/09/2022, you have mentioned it should stop on 08/11/2022. But since there are no dates in Septembere, the next closest 7 Days to the customer date would be starting from 10/10/2022 according to the Input data provided, isn’t the Output should be the following based on the Input data provided :
image

15

@supermanPunch Hello, thank you for your deep explanation

I have tried from my workflow and this is data from the website that I get from the Data scraping function

image

I assigned the date on 2022/10/20 and it shows

Assign: Column ‘Date’ does not belong to table DataTable.

For the scraping data, It also includes the header (Date, Password)

This is my workflow

Get the password from website-NZ.xaml (29.3 KB)

Regards

16

@poomnattawat ,

Could you put a Breakpoint in the Assign Activity like shown below and Debug the workflow.

image

Then Check the content of dt_Password variable in the Immediate Panel.

Seems that the Column name may contain spaces or it is a entirely different column name that we would need to use.

17

@supermanPunch Hello this the data from the out_dtPassword

out_dtPassword
[Column-0,Column-1
Date,Password
2022/10/18,7A88F9
2022/10/19,7AC34B
2022/10/20,7AFDB7
2022/10/21,7B383D
2022/10/22,7B72DD
2022/10/23,7BAD97
2022/10/24,7BE86B
2022/10/25,7C2359
2022/10/26,7C5E61
2022/10/27,7C9971
2022/10/28,7CD4AD
2022/10/29,7D1003
2022/10/30,7D4B73
2022/10/31,7D86FD
2022/11/01,7DC2A1
2022/11/02,7DFE5F
2022/11/03,7E3A37
2022/11/04,7E7629
2022/11/05,7EB235
2022/11/06,7EEE52
2022/11/07,7F2A92
2022/11/08,7F66EC
2022/11/09,7FA360
2022/11/10,7FDFEE
2022/11/11,801C96
2022/11/12,805958
2022/11/13,809634
2022/11/14,80D32A
2022/11/15,81103A
2022/11/16,814D5B
]

image

Regards

18

@poomnattawat ,

As we can see, Date Column Name is in the row, This might create problems when comparing the dates as well.

Could you Check why would this be happening in the previous workflow ?

If you are not able to solve this, then you could do the below after the Table is Extracted :

Using an Assign Activity do the below :

out_dtPassword.Columns("Column-0").ColumnName = "Date"

out_dtPassword.Columns("Column-1").ColumnName = "Password"

The above assigns will change the column name to the required column names. Next we would require to Skip the first row and convert to Datatable like below :

out_dtPassword = out_dtPassword.AsEnumerable.Skip(1).CopyToDatatable

This should make the necessary changes in order for the next activities to work.

Do make these changes only if you’re sure that you always get the Column Names as provided by you.

19

@supermanPunch Hello

I already tried your assigned code and It’s working very well for the normal situation

For my testing, I found some errors as shown below,

image
image850×274 6.87 KB

I am not sure how UIpath Brings the date to 02/10/2022 (In website page does not include it)

This is the oldest date on the actual page of the website

image

Regards

20

@poomnattawat ,

We did provide the suggestion/solution based on the initial requirements.

However, it seems that the total requirements is still not being discussed.

Is there a verification that is happening between the website?

If the data that is being used is different for the Errors mentioned, we would ask you to share the data and mention the Output that is Supposed to occur and what is the output currently from the Solution provided.

If this is a different requirement from the original, then we could also discuss this as a separate topic or in private, which would let us Close this Original Topic.