Why Filter Table Data activity is not filtering the Excel DataTable?

News Knowledge Base
,
1

Why Filter Table Data activity is not filtering the Excel DataTable?

Issue Description: Filter Table Data activity is not working as expected for Excel DataTable. Its returning all or nothing depending on what I filter on even though filters seem to be configured correctly.

Resolution:

  1. Based on Filter Data Table documentation, "Excel ranges are not supported as input for this activity.".

  1. After using the Read Range Workbook activity, use LINQ for datatable and filter the datatable.

  1. Based on the below example, remove only the records that satisfy all the conditions (AND operator).

To remove the data, use this solution:

  1. Use and Assign activity for variable remainingDataTable (System.Data.DataTable) to 
(From a In dt_Table.AsEnumerable() Select a).Except((From a In dt_Table.AsEnumerable()
Where Convert.ToString(a("Has Affidavit")) = "0" AndAlso 
Not String.IsNullOrEmpty(Convert.ToString(a("Business Purpose"))) AndAlso 
Not String.IsNullOrEmpty(Convert.ToString(a("Budget (CC or WBS) Description"))) AndAlso
Not String.IsNullOrEmpty(Convert.ToString(a("Budget (CC or WBS) Manager"))) AndAlso
Convert.ToString(a("Expense Type")) <> "Undefined" AndAlso
Not String.IsNullOrEmpty(Convert.ToString(a("Hierarchy Area"))) AndAlso
Convert.ToString(a("Receipt Downloaded")) = "Y"
Select a)).CopyToDataTable()
  • dt_Table is the output from the Read Range Workbook activity.

  1. To see the row count, use remainingDataTable.RowCount

To keep the data, use this solution:

  1. Use and Assign activity for variable FilteredExcelData (System.Data.DataTable) to 
(From a In dt_Table.AsEnumerable()
 Where Convert.ToString(a("Has Affidavit")) = "0" AndAlso 
       Not String.IsNullOrEmpty(Convert.ToString(a("Business Purpose"))) AndAlso 
       Not String.IsNullOrEmpty(Convert.ToString(a("Budget (CC or WBS) Description"))) AndAlso
       Not String.IsNullOrEmpty(Convert.ToString(a("Budget (CC or WBS) Manager"))) AndAlso
       Convert.ToString(a("Expense Type")) <> "Undefined" AndAlso
       Not String.IsNullOrEmpty(Convert.ToString(a("Hierarchy Area"))) AndAlso
       Convert.ToString(a("Receipt Downloaded")) = "Y"
 Select a).CopyToDataTable
  • dt_Table is the output from the Read Range Workbook activity.

  1. To see the row count, use FilteredExcelData.RowCount

Note: For the conditional logical OR operator, use Or.