Web Data scraping and select the cloumn with earliest date

Hello!

I am new to UiPath. I need a small help to select the earliest date row on the website.
I am scraping data table from the web. I have to match the all the rows (Date column) and select the row on the website.

Ex:
Name Date company amount
ABC 01/12/2020 Wipro 8000
ABC 02/01/2021 Wipro 3000
ABC 03/20/2021 TCS 20000

I need to select 1 st row where the date is oldest among the 3 rows.

How can I achieve this? I was running this in for each row in datatable activity.
Please help.
Thank you

HI @Ping_Pong

Try this expression

(From row In Dt Order By DateTime.ParseExact(row.Item("Date").ToString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) Ascending Select row).CopyToDataTable

Regards
Gokul

Give a try on

Assign Actvitiy
LHS: drFirstOldestRow | DataType: DataRow
RHS:
yourDataTableVar.asEnumerable.OrderBy(Function (x) CDate(x(Date).toString.Trim).Date).First()

Assign this in a datatable variable and pass that variable to For each row in datatable

Then loop through it

Regards
Gokul

Welcome to UiPath and the forum! If you are using the Data Scraping activity, you will extract an “ExtractedDataTable”. You would in this case iterate through each row using a For Each Row in DataTable activity. Use this LINQ statement:

DateTime minDate = Convert.ToDateTime(((from DataRow dr in YourDtname.Rows orderby Convert.ToDateTime(dr[“DateColumn”]) descending select dr).FirstOrDefault()[“DateColumn”] )

Hi @ppr @Gokul001
Thank you for the reply

I have tried with this logic but loop is needed to match the company column "Wipro "(matching from Excel spreadsheet). If I get two or more same companies then I have to go for the Oldest date. Some cases have other companies with the oldest date.

Please suggest.
Thanks.

looks like you want to get for each company the oldest row.

This we can do with grouping the data

DTCompanyOldestRows | DataTable =


(From d in YourDataTableVar.AsEnumerable
Group d by k=d("Name").toString.Trim into grp=Group
Let dro = grp.OrderBy(Function (x) CDate(x("Date").toString.Trim).Date).First()
Select r = dro).CopyToDataTable

You will get back a datatable with the oldest row for each company

1 Like

Hi @ppr

Thanks for the reply
but I am confused with the logic.
Could you please elaborate on how can I pass this activity?

Thanks.

it is about LINQ

Just us an assign activity
grafik

LeftSide: DTCompanyOldestRows - a variable of DataType: DataTable
RightSide:

(From d In YourDataTableVar.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let dro = grp.OrderBy(Function (x) CDate(x("Date").toString.Trim)).First()
Select r = dro).CopyToDataTable

YourDataTableVar = use the VariableName of your extracted Datatable

Hi @ppr
I am facing compilation error in the activity, please share any steps to achieve this

Thank you.

did check and updated it. surrounding " from

CDate(x("Date").toString

was corrected

Hi @Palaniyappan @Yoichi @kirankumar.mahanthi1

Please help me to achieve this.
Thank you

@Ping_Pong

was it working?

No Sir, @ppr

image

I am getting this error.
Please help
Thanks

there is an empty value in your data for a date column value
can you check if we can filter out such rows when doing the processing

@ppr Sir,

How can I get only oldest date from the data table. So that I can match by creating another loop.

Please help
Thank you.

please let us progessing by not repeating the case, but answering the open questions

we were asking to check the data on rows with empty values on the date column (could also be the latest one only) and if it will harm, when we do filter out these records

A LINQ doing this filtering e.g. could look like this:

(From d In YourDataTableVar.AsEnumerable
Where not (isNothing(d("Date")) OrElse String.IsNullOrEmpty(d("Date").toString.Trim))
Group d By k=d("Name").toString.Trim Into grp=Group
Let dro = grp.OrderBy(Function (x) CDate(x("Date").toString.Trim)).First()
Select r = dro).CopyToDataTable

also ensure that the date formats are consistent and parseable with CDate. Otherwise we have to handle this also as well

Thank you sir I got the row with the Date

Hi @ppr sir,

Its throwing an error if the data table is null.

How can I move on if even the no data?

Please suggest

following pattern can do it:

when we would expect an empty filter result we use the toList.
Depending on the List count we execute the CopyToDataTable or clone the origin datatable

just adopt it to your case and feel free to rename the variable drDuplicateOnly e.g. to drResult
drResult is of Datatype: List(Of DataRow)