How to filter columns by using LINQ Query Syntax in UiPath

Introduction:

When working with DataTables in UiPath, you might encounter scenarios where you only need specific columns from your dataset. Filtering columns in a DataTable can be achieved efficiently using LINQ (Language Integrated Query). LINQ allows developers to write queries directly within .NET languages, making it a powerful tool in UiPath for manipulating data.

In this article, we’ll explore how to use LINQ query syntax in UiPath to filter columns in a DataTable and convert the results back into a new DataTable for further processing.

Input File:

Step 1: : Use read range activity for reading input file .

The input files contain patient reports for both males and females. Here, we need to filter only the male reports from the DataTable

The UiPath workflow provided consists of the following key activities:

1. For Each Row in DataTable: Iterates through each row of the DataTable.
2. Assign: Applies a LINQ query to filter the DataTable.
3. Write Range Workbook: Writes the filtered data to an Excel file.

Let’s dive deeper into each of these steps.

Step 1: For Each Row in DataTable

The first activity is the “For Each Row in DataTable”. This activity is used to loop through every row in the DataTable DT. The current row being processed is referenced by the variable CurrentRow.

• Data Table: The DataTable DT is the source of data for the iteration.
• Item Name: The current row being processed is referred to as CurrentRow.

This activity enables the workflow to iterate over all rows in the DataTable, allowing for further operations on each row.

Step 2: Assign Activity

Within the body of the “For Each Row” loop, there is an “Assign” activity used to filter the DataTable based on specific criteria. This is achieved through a LINQ query.

• Save to: The result of the LINQ query is stored in a DataTable variable called Filterdt.
• Value to Save: The LINQ query expression filters the rows of the original DataTable DT.

Filterdt = (From row In DT

Where row.field(Of String)(“gender”).contains(“Male”)

Select row).copytodatatable

In this example:

• The query filters the rows based on a condition (e.g., a specific value in a column).
• The filtered result is converted back to a DataTable using .CopyToDataTable().

Step 3: Write Range Workbook Activity

After filtering the DataTable, the next step is to write the filtered data into an Excel file. This is done using the “Write Range Workbook” activity.

• Workbook Path: Specifies the location of the Excel file, in this case, “Input.xlsx”.
• Sheet Name: The sheet in which the data will be written, e.g., “sheet1”.
• DataTable: The filtered DataTable Filterdt is used as the input for writing to the Excel file.

This activity will take the Filterdt DataTable and write it to the specified Excel sheet, allowing you to store or further process the filtered data.

Conclusion

This workflow effectively demonstrates how to filter rows from a DataTable using LINQ in UiPath and then write the filtered data to an Excel file. By breaking down each step, you can customize this workflow to filter data based on your specific requirements.