Solved

Searching in Excel Table Using FME for Entries Containing Values from Another Table

  • 26 February 2024
  • 6 replies
  • 69 views
mgee
Rank
Badge +4

Hello everyone,

I'm facing a challenge in processing data in Excel and I'm seeking assistance. I need a method to search in one Excel table for entries that contain values from another Excel table and then create a new table with the found entries.

Specifically, I want to search an Excel table (Table A; 8 collumns) for certain keywords. These keywords are located in a separate Excel table (Table B; 1collumn). When any of the keywords are found in Table A, I'd like to create a new table with these entries.

I would greatly appreciate any advice, tips, or examples that could help me solve this issue.

Thank you very much in advance for your help!

 

icon

Best answer by redgeographics 27 February 2024, 13:14

View original

6 replies

redgeographics
Rank
Userlevel 5
Badge +32

You can use a FeatureMerger or FeatureJoiner to do that, the matches are the ones you need.

danilo_fme
Rank
Userlevel 5
Badge +32

Excellent. I agree with @redgeographics 

mgee
Rank
Badge +4

You can use a FeatureMerger or FeatureJoiner to do that, the matches are the ones you need.

Thank you for your answer! 

 

Maybe it's worth to mention that the values in the table A may not be unique and the words from the table B can appear at any position within a string in the table A. 

Is one of these transformers still capable of doing this Job?

 

If my questions seem too basic, I apologize, as I'm still learning about FME.

hkingsbury
Rank
Userlevel 6
Badge +36

 

 

Maybe it's worth to mention that the values in the table A may not be unique and the words from the table B can appear at any position within a string in the table A. 

Is one of these transformers still capable of doing this Job?

 

FeatureMerger and FeatureJoiner are more aimed at finding direct matches. InlineQuerier is another option, this stores the incoming data in an SQLite database and allows you to write more complex SQL queries to join different datasources

redgeographics
Rank
Userlevel 5
Badge +32

You can use a FeatureMerger or FeatureJoiner to do that, the matches are the ones you need.

Thank you for your answer! 

 

Maybe it's worth to mention that the values in the table A may not be unique and the words from the table B can appear at any position within a string in the table A. 

Is one of these transformers still capable of doing this Job?

Okay, that does complicate matters a bit. Assuming table B is dynamic (or has too many values to make brute-forcing an option) you may want to try this:

 

Concatenate all values from B in a single attribute, separated by | (ListBuilder, ListConcatenator)

Merge that to table A (FeatureMerger and then set both Requestor and Supplier to the same value, e.g. 1)

StringSearcher, using your concatenated attribute as a regular expression (so yeah, a dynamic regex)

 

1 Attachment
mgee
Rank
Badge +4

You can use a FeatureMerger or FeatureJoiner to do that, the matches are the ones you need.

Thank you for your answer! 

 

Maybe it's worth to mention that the values in the table A may not be unique and the words from the table B can appear at any position within a string in the table A. 

Is one of these transformers still capable of doing this Job?

Okay, that does complicate matters a bit. Assuming table B is dynamic (or has too many values to make brute-forcing an option) you may want to try this:

 

Concatenate all values from B in a single attribute, separated by | (ListBuilder, ListConcatenator)

Merge that to table A (FeatureMerger and then set both Requestor and Supplier to the same value, e.g. 1)

StringSearcher, using your concatenated attribute as a regular expression (so yeah, a dynamic regex)

 

 

I want to thank everyone for their support. Special thanks to @hkingsbury —your suggestion worked perfectly!

Also, I deeply appreciate @redgeographics  for generously providing the FME Workbench. Both options were fantastic, but I've decided to utilize the FME Workbench due to its flexibility with various table formats.

Thank you all for your help!

Reply


Community Stats

29,355
Posts
111,609
Replies
37,018
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.