How to Exact Match values of Excel Column using Filter Datatable

Help Activities
, , , ,
#1

I am trying to filter exactly only “tcp-22” from the service column. I used filter datatable to get the required data, but problem is that I only need the values “tcp-22”, but I also get other values such as ‘tcp-2242’, “tcp-2222” in the results.

Any idea how to get only the exact data that is required?

Attached are my sample excel sheet and Filter DataTable screenshot.
Appreciate any help.

Excel Data
Excel Data1046×352 158 KB

filter DataTable

#2

hey @dheer159
use “=” instead of contains method.

#3

Thanks Nilay.
If we use “=” then the data from ‘Rule 2’ and ‘Rule 4’ is not able to see in the output. It only matches then the cells which only contains ‘tcp-22’

#4

hey @dheer159
Try “service” endswith “p-22”

#5

Thanks Nilay,
I Tried “service” endswith “p-22”, however, the issue with this method is all the cells which has “tcp-22” in the beginning, then those rows are not captured.

For example, using “ends with ‘p-22’”, will not capture “Rule No. 8” from below screenshot

Excel Data1
Excel Data11044×416 188 KB

#6

Use end with -22

#7

@dheer159
give a try to extract with Regex tcp-22 and only when it is sourrounded by the \b
meaning of \b: assert position at a word boundary: (^\w|\w$|\W\w|\w\W)

grafik

But better to do this with a LINQ:

(From d In dtData.AsEnumerable
Where Regex.IsMatch(d(0).toString, "\btcp-22\b")
Select d).CopyToDataTable

ensure:
grafik

Handling empty result:

grafik
grafik707×346 46.6 KB

#8

I am try but its showing the source xontain no data row could you please share any workflow example i appreciate

#9

Rule pc-22 excel.xlsx (11.7 KB)
please find the excel file

#10

Thanks Peter, Could you please share your xaml file where you have tested this sample excel.
Appreciate your help.

#11

@dheer159
find xaml here:
FilterExactMatch_WithRegexIsMatch.xaml (9.0 KB)

did test it with the Excel from @Aleem_Khan
Rule pc-22 excel.xlsx (11.7 KB)

result on excel input:
grafik

#12

@Aleem_Khan
just check the column index or name from the statement. Maybe from there the issue is caused

#13

Thank You very much Peter.
Your code worked… :slight_smile:

1 Like
#14

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