Excel Filter Table Activity

Hi,

Is there a way to automate filtering in excel?
For example:

  • Filter Column G to only show “2302”

  • Filter Column A to “Blanks” and delete all rows then set Filter back to “All” on Column A

Regards,
Shawn

@shawnmurray

You can use Filter dataTable Activity or Select query to filter dataTable. Try like this:

YourDT.Select(“[ColumnName] = ‘2302’”).CopyToDataTable

3 Likes

@shawnmurray say for example the outpue fo your datatable is outdt
now coming to your filter you can use like this outdt.select("G column name=‘2302’ AND A column name=‘ALL’‘’).COPYTODATATABLE
it will give you the output with columns containing values 2302 and ALL.
hope this helps you.plz let me know if anything goes wrong.

2 Likes

@venkatmalla6 Please view image!
image
I am unsure where you mean to put outdt.select("G column name=‘2302’ AND A column name=‘ALL’’’).COPYTODATATABLE?

Regards,
Shawn

@shawnmurray for this you no need to use filter wizard just give assign activity and assign it to a datatable variable.

1 Like

@shawnmurray

If you are using this Select query then no need of Filter dataTable Activity.

If you want to go with that Select query then use Assign Activity and write that expression.

1 Like

Hi Guys,

Is this what you mean - but i am getting an error


I will show properties also:
image

Thanks,
Shawn

1 Like

Hi @shawnmurray

Don’t give it in double quotes

Outdt.select(“column name=‘2302’ and [“column name]=‘ALL’””). copy todatatable ()

Thanks
Ashwin.S

@shawnmurray outdt.Select(“G column name=‘2302’ and A column name=‘ALL’”).CopyToDataTable this is how it should be and for your understanding i mention G column name actually there you need to mention the column name similarly for A column also.

Thats great guys thank you !

1 Like

Hi guys, I am new to Ui Path…wondering if there is a way to filter an excel file that is not converted to a table yet? (My case is, I want to automate downloading an excel file, applying the required filters for 2 columns, and copy the range. But the downloaded Excel file has not been converted to a table, so when i try to use the Filter Table function, i see the ‘cannot find Table’ message.

Hi @shawnmurray,

Check this.

Regards
Balamurugan.S

@balupad14,

Thanks a lot. I tried out Create Table function and it is working.

However, it requires me to specify a range in the Create Table Range. I am not able to leave it as “”. But the excel sheet I am working with is an active one, new records will be added daily. What is another way to go around this? Using a specified range will not work…

1 Like

Hi @Renee_Lua,

I understand your situation. I have attached the sample to you that it can find the used range.

sample : Renee_Lua.zip (87.5 KB)

Regards
Balamurugan.S

@balupad14, Could you explain on the Write line activity with the text “A1:” & strColName & intRowIndex.ToString()?

I tried to add these variables, and copied the same command, but it returned as could not find range. But i also noticed you used a write line function before going into Excel Application Scope. What does this do? And is this a generic function I can use when I don’t have a specified range in Excel?

1 Like

Hi. Can you please install the package and reopen it @Renee_Lua

Thank you
Balamurugan.S

Hi @balupad14Thanks for the help! I downloaded the package, and used your example workflow as guidance. Not sure why I am seeing this error message when I run it.Workflow%20Error

1 Like

Hi,
Can you Please attach xmal with xlsx file please. Or send me the sample…

Regards
Balamurugan.S

Hi @balupad14, could you share your email for me to send the files and xaml? I am a new user and not able to upload here…