Help with filtering out data rows with certain values in columns

Help Studio
, ,
1

I built this sequence to remove rows where “Red” is in [Q1] or the 0 (first) column. But it seems to totally ignore the filter.

The flow below is:
–I get files from directory, multiple excel files
–for the rowfiles I do a for each
–a read the spreadsheet tab into variable “dt_extras”
–I filter dt_extras into dtresult
–i then write back dtresults to the workbook

Nothing is changing.

This image depicts a UiPath workflow that reads multiple Excel files, filters the data from the "ComplianceByDepartment" worksheet, and writes the filtered data back to the same worksheet. (Captioned by AI)
This image depicts a UiPath workflow that reads multiple Excel files, filters the data from the "ComplianceByDepartment" worksheet, and writes the filtered data back to the same worksheet. (Captioned by AI)590×829 21.9 KB

2

Hello

Can you share a screenshot of the filter?

If you have two filters entered ensure you have clicked the And/Or button at the start of second row and left it as “Or”

Another option is to split the filter activity up into one filter at a time. So have two activities in your scenario.

Cheers

Steve

3

Here ya go Steve

The image shows the Filter Wizard window of a software application, where rows of the DataTable "dt_extras" are being filtered to remove entries that match the conditions specified for the columns "0" or "Q#" where the value equals "Red" or "1". (Captioned by AI)
The image shows the Filter Wizard window of a software application, where rows of the DataTable "dt_extras" are being filtered to remove entries that match the conditions specified for the columns "0" or "Q#" where the value equals "Red" or "1". (Captioned by AI)811×522 22.8 KB

4

@jwetherin

Can you show one of your excel data

Looks like it might have some extra info or so

Cheers

5

Hi @jwetherin

Can you share the input and expected Output file.

Regards

6

This is just a test spreadsheet. I am trying to delete rows 13 and 16, which eventually Im gonna delete rows 2-9.

The image is a spreadsheet showing some question texts, overall completion percentages with color-coded statuses (green and red) for performance metrics, and instructional text indicating yellow scores between 90 and 100%. (Captioned by AI)
The image is a spreadsheet showing some question texts, overall completion percentages with color-coded statuses (green and red) for performance metrics, and instructional text indicating yellow scores between 90 and 100%. (Captioned by AI)1324×511 9.84 KB

7

As a test, remove all filters and add just this one:
“Q#” contains “Red”
See how you go.

8

Hi @jwetherin

Could you please try to have individual Filter Data Table activity for each of the conditions , i mean 1 filter data table for 0 =“Red” , next filter data table for 0=“1” like that .

9

This is odd. Column is there, but I get this error.

A runtime execution error message in UiPath indicates that the 'Column Name' argument is not set or is invalid in the 'Filter Data Table' activity. (Captioned by AI)

The image is a screenshot of a spreadsheet showing a series of questions related to patient care protocols, including ensuring a charge nurse is notified, assigning a 1:1 sitter, and completing a room or environment safety checklist. (Captioned by AI)

10

Hi @jwetherin

Can you please show us the configure filter

Regards,

11

Sure

The Filter Wizard interface is shown, where a filter is being applied to the dt_extras DataTable to keep rows where the "Q#" column equals "Red." (Captioned by AI)
The Filter Wizard interface is shown, where a filter is being applied to the dt_extras DataTable to keep rows where the "Q#" column equals "Red." (Captioned by AI)583×523 16.1 KB

12

Just confirming, the “Read Range Workbook” activity option “AddHeaders” is enabled?

13

Yep. Thats one I can sometimes miss, but yes.

14

Please upload your data file.

15

Sure no problem. I am new to this so I apologize if it’s something easy. I hope it is though
DeleteExtraRows2.xaml (10.8 KB)

16

You don’t need that rowfiles variable. You can put the Directory.GetFiles expression directly into the “In” on the For Each. But you don’t need that either, just use the For Each File in Folder activity.

Do you have extra spaces in the value ie "Red " instead of “Red”?

17

For Each File in Folder isnt reading my range and am getting another error on the first suggestion just to put in the Get Directory

The image shows a workflow in UiPath where a "For Each" loop iterates through files in a directory and reads the "ComplianceByDepartment" range in each workbook using the "Read Range Workbook" activity. (Captioned by AI)
The image shows a workflow in UiPath where a "For Each" loop iterates through files in a directory and reads the "ComplianceByDepartment" range in each workbook using the "Read Range Workbook" activity. (Captioned by AI)671×620 18.6 KB

The image shows an automation workflow, likely created in UiPath, designed to read compliance data from Excel files in a specified folder. (Captioned by AI)
The image shows an automation workflow, likely created in UiPath, designed to read compliance data from Excel files in a specified folder. (Captioned by AI)702×655 19.5 KB

18

For that Read Range (the green one) you have to use the Excel Scope. You can’t just use that Read Range by itself. Go back to the Read Range Workbook you had originally, inside the For Each File in Folder.

19

Ah thanks, i remember learning that in training. I’ll try after lunch. :slight_smile:

20

I put Read Range there in the Excel Process Scope and error returned that activity can only be used with Use Excel File. I went down that path and I have to pick a specific file.