What is the best way to filter Excel or DataTable

Help
1

Hi friends,

What is the best practice of filtering data. Thru Excel or DataTable ?

cheers :smiley:

Happy learning :smiley:

5 Likes
2

Hi @pattyricarte

I would suggest the data table would be better… But this could depend on the scenario we are in too…

If we have the capability of omitting some records we could configure the read range as well to start from the row we need.

6 Likes
3

Hi @pattyricarte
From my current projects, the best approach would be saving the filtering condition in seperate Excel file, then form the query based on that

Ex :grinning:

This is my pseudo-code :

In excel file
Name brse;se;pm;coe

In Studio, you split the {brse;se;pm;coe} string to [brse, se, pm, coe] based on delimiter “;”

Assume that queryArr = [brse, se, pm, coe]
Initialize queryStr = “”
Then form the query :

For each element in queryArr :
If element.equals(queryArr.last()) :
queryStr = queryStr + "job = " + element
Else
queryStr = queryStr + "job = " + element + " OR "
End If
End For

The query now would be like :grinning:

   job = "brse" OR job = "se" OR job = "pm" OR job = "coe"

When you want to change the condition of the query, just add or remove elements in the Excel File, you don’t need to change in the code. This would save you ton of time if the project is huge.

It’s better to save those conditions somewhere else, not in the code. It’s more flexible.

3 Likes
4

If you filter using data table, you have to read it first and then after filtering, you are supposed to write it.

If you are using excel to filter the data, then there would be game of send hotkeys or clicks to remove the columns and rows.

Another point to keep in mind is that from which location you are accessing the file. If it is on shared location, it is good to work on data table in place of excel sheet, to avoid network latency.

Using which approach out of two also depends on the size of data you are working on.

3 Likes
5

Hi @Lahiru.Fernando

that make sense. Thank you for sharing your idea.

cheers :smiley:

Happy learning :smiley:

4 Likes
6

Hi @sonminator

thank you for your effort creating a lot of good stuff .

cheers :smiley:

Happy learning :smiley:

3 Likes
7

Hi @Purvi

you are saying that if i have a big data i must to use datatable right ?

cheers :smiley:

Happy learning :smiley:

4 Likes
8

Do we have any activities to filter the excel @pattyricarte :smiley: We have one where we the data in excel needs to be in tabulated form and then you have to pass that table name to the activity. If the data is not in the tabular form, then you have to write the code to filter excel.

I would say we have only one option that is FIlter Data Table even it is huge data or small data.

Correct me if I’m wrong :smiley:

2 Likes
9

I would agree with hareeshMR. Creating custom code sets to perform actions that can already be performed with the base functionality of UiPath is not preferable. Best practice for filtering data from Excel would be to use the Filter Data Table action, as we already have the action built in to UiPath.

There may be a case where filtering in Excel is the better option, but that would be on a case by case basis, and I have not encountered it yet.

Cheers!

1 Like
10

@pattyricarte @HareeshMR @Lahiru.Fernando @martinw3
Hi guys as I dont want to repeat your valuable statements. Just let me add:

  • I had a lot tasks best solveable with LINQ, so a close bind to DataTable
  • rewriting back DT to Excel can sometimes be tricky
    ** in some special cases we used DT as side by side with following:
    *** named the DT Columns similar to EXCEL Columns Name (A,B, AC…)
    *** For Updating computed info from DT into EXCEL this was easy done by calculating the range with DT functionality: eg.: AZ1 = DTColumnname + “:” + DTRowIndex + 1
1 Like
11

Hi Friends,

Thanks all for sharing all your thoughts about the topic.

cheers :smiley:

Happy learning :smiley:

4 Likes