How to filter records in a data table

Hi Guys,

I have the following question:
I need to read data from an excel file where column “status”= Open and process those records and write back data to those rows with status=Open.
Main.xaml (9.7 KB)
I did the above code, but everytime i write back data to excel file, the original data in the excel is getting changed.
Please help.Mappe1.xlsx (8.1 KB)

Hi @pooja.pakanati

This task can also be performed by using less number of activities like this

The code used in invoke code is

dt.AsEnumerable.Where(Function (r) r("Status").ToString.Trim.ToUpper.Equals("OPEN")).ToList().ForEach(
Sub (row) 
	row("Status") = status.ToString
End Sub
)

For your reference

Filter Records in a Data Table.xaml (6.1 KB)

If you do not want to use the Invoke Code then the same task can be done using the Assign activity via LINQ

I Suggest you to read this article

Without Using Invoke Code

(From row In dt
Let s = If(row("Status").ToString.Trim.ToUpper.Equals("OPEN"), status.ToString, row("Status").ToString)
Select dt.Clone.LoadDataRow({row(0).ToString, s}, False)).CopyToDataTable

Xaml File
Filter Records in a Data Table without Invoke Code.xaml (5.8 KB)

Hope it solves your problem.

Use the Filter DataTable Activity!

1 Like

It constantly amazes me how people overcomplicate these things.

If you see the code I attached I did use Filter DataTable Activity! I am stuck writing back the filtered data to Excel file :slight_smile:

Hi Pooja,

please excuse my rushed answer. I just saw the title: “How to filter records in a data table” and the first response (do it with invoke code) and wanted to provide a simpler answer.

By now I had a look at your workflow and you are making one crucial mistake. You filter your datatable then take the indexes of the filtered datatable to edit values of rows with those indexes in your old datatable. The indexes don’t match though and that’s why your data is all over the place.

What you actually want to do is change all rows with status “Open” to status “hochgeladen” (entscheid dich doch mal! Deutsch oder Englisch? :wink: ). Correct? Easy way to do this is just an if in a For Each statement.

Keep things as simple as possible and as detailed as necessary :+1:

image

@postwick - right? :laughing:

1 Like

That’s a good way to do it, yes. Simple and easy to read for the next person that has to work on the code :slight_smile: