How to use the filter data table?

[Kumar_Sahu_Sameer]

Hi team, I have an Excel file, and I want to retrieve all data based on the information in a text file. However, when I apply the filter, it’s not working as expected. Can someone please take a look into this

6783.txt (162 Bytes)
Merged.xlsx (18.8 KB)

The desired output is like thisDear Gilanki,
Please find below information of IPs, which you requested under Ticket#6783

IP: 112.365.74.2
Owner: Gangireddy Manikanta
Provider: A2 Hosting
ASN:76382
City:Hazaribagh
Postal Code:825301
Country:India
Coordinates:23° 59’ 47.8320’’ N 85° 22’ 8.7960’’ E

IP: 112.829.191.212
Owner: Jommu Pavankalyan
Provider: Bluehost
ASN:98876
City:Kochi
Postal Code:682001
Country:India
Coordinates:9° 56’ 20.7348’’ N 76° 16’ 13.8828’’ E

IP: 113.735.73.733
Owner: Swarna Rakesh
Provider: Hostinger
ASN:07984
City:Vellore
Postal Code:632001
Country:India
Coordinates:12° 56’ 5.8848’’ N 79° 8’ 48.7716’’ E

IP: 115.347.458.533
Owner: Ponnarasapu Surya
Provider: HostGator
ASN:21562
City:Gopalpur
Postal Code:760002
Country:India
Coordinates:23° 29’ 56.2776’’ N 87° 23’ 53.3868’’ E

IP: 117.373.73.1
Owner: Sandhu Srinivas
Provider: HostGator
ASN:36467
City:Nandyal
Postal Code:518501
Country:India
Coordinates:15° 28’ 42.8484’’ N 78° 28’ 59.1348’’ E

[rikulsilva (Henrique Lima da Silva)]

@Kumar_Sahu_Sameer

IP: 117.373.73.1
Owner: Sandhu Srinivas
Provider: HostGator
ASN:36467
City:Nandyal
Postal Code:518501
Country:India
Coordinates:15° 28’ 42.8484’’ N 78° 28’ 59.1348’’ E

Hi @Kumar_Sahu_Sameer

Take a look in following sample

FilterInformation.zip (64.7 KB)

[Yoichi]

Hi,

Can you try as the following?

Read text file using ReadTextFile activity (Let’s say strTxt as output)
Read Excel Sheet sugin ReadRange (Let’s say dt)

Then

arrIP = System.Text.RegularExpressions.Regex.Matches(strTxt,"\b\d+\.\d+\.\d+\.\d+\b").Cast(Of System.Text.RegularExpressions.Match).Select(Function(m) m.Value).ToArray()

arrDr = dt.AsEnumerable.Where(Function(r) arrIP.Contains(r("IP Address").ToString)).ToArray

Check arrDr is not empty

dtResult = arrDr.CopyToDataTable()

Main.zip (1.9 KB)

Regards,

[Kumar_Sahu_Sameer]

Thanks for the quick response, it’s working as expected. could you please guide how to achieve the same result with the help of filter data table activity?

[Kumar_Sahu_Sameer]

Thanks for the quick response, it’s working as expected. could you please guide how to achieve the same result with the help of filter data table activity?

[rikulsilva (Henrique Lima da Silva)]

After get the IP address, just use filter Data Table Activity

[Kumar_Sahu_Sameer]

Thank you for your kind assistance! I have a question regarding regex patterns. I tried using the pattern \d.+ to extract IP addresses, but when I tried to filter the results, it did not work as expected. However, when I tried the pattern that you shared with me, it worked perfectly fine. I was wondering if you could please explain why there was a discrepancy between the two patterns?

[rikulsilva (Henrique Lima da Silva)]

Glad to help @Kumar_Sahu_Sameer

1: \d This represents any digit character, equivalent to any character in the set [0-9]. It matches exactly one digit

2 : .+ will catches any character (except for newline characters. The plus sign + is a quantifier that means “one or more of the preceding element”. So, .+ matches one or more of any character following the initial digit.

It is possible your regex catches others characters too (include possible spaces), like this

[Kumar_Sahu_Sameer]

Thank You @rikulsilva, I learned some new things. Could you please suggest a website or YouTube channel for mastering regex?