topic RE: Excel Automation/filter data in Product Forum

Excel Automation/filter data

UditKhanna — Thu, 17 Feb 2022 06:51:00 GMT

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
------------------------------

RE: Excel Automation/filter data

PabloSarabia — Thu, 17 Feb 2022 09:22:00 GMT

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
------------------------------

RE: Excel Automation/filter data

Rambo27 — Thu, 17 Feb 2022 09:31:00 GMT

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
------------------------------

RE: Excel Automation/filter data

UditKhanna — Thu, 17 Feb 2022 10:00:00 GMT

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

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

RE: Excel Automation/filter data

PabloSarabia — Thu, 17 Feb 2022 10:15:00 GMT

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
------------------------------

RE: Excel Automation/filter data

UditKhanna — Wed, 09 Mar 2022 04:50:00 GMT

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
------------------------------

RE: Excel Automation/filter data

PabloSarabia — Wed, 09 Mar 2022 09:27:00 GMT

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
------------------------------

RE: Excel Automation/filter data

RonanConsidine — Wed, 09 Mar 2022 14:24:00 GMT

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
------------------------------

RE: Excel Automation/filter data

UditKhanna — Fri, 11 Mar 2022 04:58:00 GMT

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
------------------------------

RE: Excel Automation/filter data

RaviKumar3 — Fri, 11 Mar 2022 12:59:00 GMT

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
------------------------------

RE: Excel Automation/filter data

RonanConsidine — Fri, 25 Mar 2022 16:31:00 GMT

Hi @UditKhanna,

Apologies for being late to reply to this.

Can I ask why the date is in this format in the first place? It would be easier if the system that creates this can present the data in a different way.

Alternatively with some excel rules you can split the data into columns to remove the name of the day and the timestamp so it would go from:

Wed Jan 19 00:45:23 PST 2022

Jan 19 2022

This could be used with either an excel rule or a digital worker to convert into a date format much easier with minimal effort.

Best Regards,

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