Searching in Excel Table Using FME for Entries Containing Values from Another Table
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!
Page 1 / 1
You can use a FeatureMerger or FeatureJoiner to do that, the matches are the ones you need.
Excellent. I agree with @redgeographics
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.
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
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)
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.
