Excel Automation/filter data

[UditKhanna]

Hi Community,

There is an excel sheet that i want to automate. There are about 16000 records in a sheet, i want to filter out only those records whose time from current date is more than 30 days. Below is the attached format of login time. Initially, i thought to import all records in a collection and process every record in code stage but that would take a hell lot of time, so there must be some way to achieve this.
Thanks

------------------------------
Udit Khanna
------------------------------

[PabloSarabia · Accepted solution]

Hi Udit,

In this case, if you have all the Excel in a collection, you can filter it easy with the "Filter Collection" action from the "Utility - Collection Manipulation" object.

The filter syntax looks like this:

"[Column Name] < '" & AddDays(Today();-30) &"'"



Hope this helps you!


Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[Rambo27]

Hi Udit, 

OLEDB against Excel as it contain good amount of data and filter with using queries inside it 

Check this example - OLEDB against Excel

Hope it helps you.

------------------------------
Shikhar Mishra
RPA Lead
Infosys Pvt Ltd
Pune,India
------------------------------

[UditKhanna]

Hi @PabloSarabia, Thanks for the reply. I get the following error on executing the command you mentioned:

 ​​

------------------------------
Udit Khanna
------------------------------

[PabloSarabia]

Hi!

In this syntax, is not necessary to put the collection name. 

In your case, put only "[Date]".


Hope this helps you

Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[UditKhanna]

Hi @PabloSarabia, still getting the same error. I think it's some kind of error related to bracket.



Note: Date is in this format : Wed Jan 19 00:45:23 PST 2022

Thanks

​

------------------------------
Udit Khanna
------------------------------

[PabloSarabia]

Hi @UditKhanna

In this case, the problem is with the expression, but i watch the entry formula and looks correct. I also try on my own BP and works 😞

All the syntax looks correct but the error is over one of the brackets that you have in the formula. I don't know if the problem could be that when something is copied from the forum it is not pasted properly. (I also try to copy and paste the expression that I send you and works). Can you try to rewrite the entry formula?

When I execute the "AddDays" function (like this: AddDays(Today();-30) ), now its returning me this: 

Are you getting something similar to this? And... what BP return you when you execute only the "Today()" function?



See you in the community, bye 🙂
​

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

[RonanConsidine]

Hi Udit,

If you are looking for all of the records that are WITHIN 30 days of Today use the action Filter Collection with the following inputs

Collection In: [Collection name in square brackets]
Filter: "[Date] >'"&AddDays(Today(),-30)&"'"

The popup issue you are seeing is because you used a semicolon ';' when you should be using a comma ','

If you are looking to exclude those over 30 days away (both in the future and past) you will need to do this twice with the first collection removing the dates more than 30 days ago:
Filter: "[Date] >'"&AddDays(Today(),-30)&"'"
And the second one removing all cases that are more than 30 days in the future
Filter: "[Date] <'"&AddDays(Today(),30)&"'"


Hope this helps!

------------------------------
Ronan Considine
Business Automation Lead
Blue Prism
------------------------------

[UditKhanna]

Hi @RonanConsidine and @PabloSarabia, Thanks for the reply.

I do not get the exception now, i got the logic but still we cannot get the desired output, and the only reason that makes sense is the date format.

This is the date format : Wed Sep 8 22:46:54 PDT 2021 and i don't think comparison would happen with this date format, is there any other way?
Because even if i change the date format i would have to loop through 25000 records, and we want to avoid the loop on the first place.

Thanks.​​

------------------------------
Udit Khanna
------------------------------

[RaviKumar3]

Hi Udit,

Did you try with OLEDB? If there 25000 records then I will recommend to use OLEDB.

------------------------------
Ravi Kumar
Sr Automation Designer
Ericsson
Asia/Kolkata
------------------------------