Search in data table

Help Studio
,
1

Hey everyone,

I have an excel file that i read with Read Range Workbook into a data table.
Then i want to keep only just a few columns so after creating another data table (Build Data Table activity) i use For each row in data table and Add Data row activities. All good so far.

The columns are Invoice and Email and the goal is to search in the data table via the invoice and retrieve the email. If the provided email exists then i get alot of results back, if it doesnt exist then i get an empty datarow().

I have used code like:

  • dt_input.AsEnumerable().Where(function(f) f(“Invoice”).ToString.Equals(“123456789”,StringComparison.OrdinalIgnoreCase))
  • dt_Input.Select(“Invoice = ‘123456789’”)

What am i doing wrong ?
Thank you

2

It’s much easier to use the Filter Data Table activity to do this, the second tab allows you to tell it which columns to keep.

To search, use the Lookup Data Table activity.

2 Likes
3

this is a expected behavior. If there are multiple rows with same Invoice number then you will get multiple rows, if there is no matching row (based on Invoice number) then you will get zero rows i.e. empty datarow()
It will be better if can explain the issue in more detail.

1 Like
4

arrMatchedRows = dt_input.AsEnumerable().
Where(Function(f) f(“Invoice”) IsNot Nothing AndAlso
f(“Invoice”).ToString.Trim.Equals(“123456789”, StringComparison.OrdinalIgnoreCase)).
ToArray()
Try these linq query or also you can use filter datatable activity

5

@menlab85

Welcome to the community

first for filter you can use as suggest by @postwick or use dt = dt.DefaultView.ToTable({"Col1","Col2"},False) in assign this retains only those columns you give

now coming to other filters…check contains or use trim may be extra spaces are there

cheers

1 Like
6

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