How to get the filtered excel output in a datatable?

datatable
excel
activities

#1

We are trying to get the filtered results from excel sheet in to datatable. Doing a ‘read range’ after filtering doesn’t seem to work as it takes the entire table.


[Duplicate]How to read filtered Excel table?
Generating some excels filtered by the main excel
[Duplicate]How to read filtered Excel table?
What is the significance of "Use Filter" option in excel read range activity
#2

Hey @Zehara

You can First do “Read Range” and then filter the data from datatable(generated by read range activity).

This link will help you on this:

Let me know, if you have any doubts on this.

Regards…!!
Aksh


#3

Hi @aksh1yadav,

We need to do this filter on a loop and keep sending the filtered result to mail output. We have done a read range before ‘Filter Activity’.
Does the ‘Filter Activity’ not store its output in any variable? Or is there a way to dynamically store the filter output in a new datatable?

Thanks,
Zehara


#4

@Zehara

you are using “Filter Table Activity”?

Visit this thread:

Regards…!!
Aksh


Extract Data from excel after Apply Filter
Filter Table in excel data tables
#5

@aksh1yadav

Yes, we are doing read range - getting distinct values of the column to be filtered in a list - Passing each distinct item as an input to ‘Filter Table’ activity.
We should now be able to get this output and send email in a loop. We are just not able to access the filtered output since there is not ‘output’ parameter in the activity.

Thanks,
Zehara


#6

You can take filter output in datable. See that edited above post link.

Regards…!!


#7

Thanks so much @aksh1yadav

We dint know about the ‘use filter’ property in ‘Read Range’ and hence were getting the entire table.

Regards,
Zehara


#8

Hi Aksh,

I click on “Download Sample” at the end of “How to Filter a Data Table”

I get an error:

403 Forbidden
Code: AccessDenied
Message: Access Denied
RequestId: D1648034F3922FE6
HostId: 9ef7KZvCzIssXhJs+OAY2u+bRdCUgLg+r9eLJY50MoEFeED4W0iTkKtvgM+7q+/0msL+z+V9iJE=

Can you let me know how to download this example?

Thanks,

Fritz


#9

Don’t know why you facing this because this questions exists in a available category for every user. Try to go that link mentioned and from their try to download otherwise if you will still face the same then just let me know :slight_smile:

Will share with you :slight_smile:

Regards,!!
Aksh


#10

Hello @aksh1yadav

I am trying to filter scrubbed data and write the same in excel, tried the solution shared by you.
Table could bot found as my range is not written in table form, please check if you can help.

no 2.xaml (12.3 KB)


#11

Filter Table activity will work on a table exists inside an Excel.

so better to use Datatable.Select() with your condition which will return an array of DataRow.
Then add that DataRow array in a datatable and then write that Datatable in an Excel.

Regards…!!
Aksh


#12

Thanks @aksh1yadav your solution worked out,

you keep helping people so below is one more way suggested by @ClaytonM


#13

I can’t download the file either, 403 Forbidden?

403 Forbidden
Code: AccessDenied
Message: Access Denied
RequestId: 854F911D331D7DD8
HostId: yYY64sA1uGfUq7iyA8JkCPHaRpXp+TzsHKpywqpiksAsfHxsafZYzBzc+ut+1SYdrcuuqA5kIxI=


#14

refresh it …might be your browser session has been over :slight_smile:


#15

Tried it over 10 times, restart the browser and even tried another browser but still having the same result :open_mouth: Is the link working there?


#16

it’s working. I can download it. It’s probably got to do with your own internet


#17

Thank you. Will try it from another connection :slight_smile:


#18

Hi @aksh1yadav,

I tried your solution, but the read range doesn’t work in my case.
This is, I think, because the range is variable. The data in this sheet is different everytime, and based on the filter, the first row might not be A2, but A6. I thought I could solve this part by simply including the headers, so it could read range from A1, but then I still have the end of the range, which is also variable (for instance, column A goes until A20, but last row with filter is A18).

Do you have any idea on how to solve this?
Thanks in advance!


#19

Hey @AnniekJ

I m not getting much info about what you are trying to say but based on what i have understood you can leave the read range to " ".

if i m wrong then share sample or screenshot of your problem
Thanks

Regards…!!
Aksh


#20

Thanks for thinking with me!

I think the problem is my table doesn’t start at the top of the excel file, as shown below, the first row is used for some other data. This needs to be there, unfortunately. So, normally, the data starts from cell A3, but when the filter is on, it can be that it starts at A6, like shown below.

temp

Also, I didn’t to read all columns, some could be left out, but because I don’t know how many rows to copy either (same problem as the unkown start of the range) this isn’t possible.

I now handle this by indeed setting the range to " ", and then in the datatable removing some rows and columns wherever needed. Not the neatest solution I guess, but it works.