Using a read range to read excel file and filter data table to only keep certain rows

Help Studio
, ,
1

Hello, I am using a read range to read a certain excel file which contains many rows and columns with headers. I have saved it to a dtMasterfile variable. I am trying to use a filter data table to keep all rows where column “Mandatory Selection” equals Agree. It only writes out the sheet name. Not sure what the issue is.

2

@mojo001

"Mandatory Selection" equals "Agree"

If this not works then

"Mandatory Selection" Contains "Agree"

or if the above not works it’s better to use Linq query in assign activity

filteredDataTable = dtInput.AsEnumerable().Where(Function(row) row.Field(Of String)("Mandatory Selection") = "Agree").CopyToDataTable()
3

Hi @mojo001

Might be it was not filtered properly,you are using the filter datatable right, instead of that use the below linq expression in assign activity,

→ Use read range workbook activity to read the excel and store in a datatable called dt_Input.
→ The use the below linq expression in assign activity.

- Assign -> Output_dt = Input_dt.asenumerable.where(Function(X) X("Mandatory Selection").toString.tolower.equals("agree")).Copytodatatable()

→ Then use the write range workbook activity to write the Output_dt to the other sheet in excel.

Hope it helps!!

4

Hi @mojo001

dtFiltered = dtMasterfile.AsEnumerable().Where(Function(row) row("Mandatory Selection").ToString().Equals("Agree")).CopyToDataTable()

Regards,

5

Hi @mojo001 ,

Example Workflow

Step 1: Read Range

  • Drag and drop the Read Range activity.
  • Set the SheetName, Range (leave blank to read the whole sheet), and the output DataTable variable (e.g., dtMasterfile).

Step 2: Filter Data Table

  • Drag and drop the Filter Data Table activity.
  • Set the Input DataTable to dtMasterfile.
  • Set the Output DataTable to a new DataTable variable (e.g., dtFiltered).
  • In the Filter Rows tab, configure the condition to filter the rows where “Mandatory Selection” equals “Agree”:
    • Column: "Mandatory Selection"
    • Operation: =
    • Value: "Agree"

Step 3: Write Range

  • Drag and drop the Write Range activity.
  • Set the SheetName, DataTable to dtFiltered, and the StartingCell (e.g., “A1”).
  • Make sure to enable the AddHeaders option if you want to write the headers.
    Regards
    Sandy
6

Thank you, this works!

1 Like
7

It’s my pleasure… @mojo001

Happy Automation!!

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.