Perform a 'contains' lookup in a spreadsheet?

[magnussandstrom]

I'm working with a spreadsheet that has two columns, and I need to create a flow to check if "nameProper" contains any of the values from column A. If a match is found, I want to output the corresponding value from column B (as private data).

I'm aware of the Row2Variables app, but it only works with exact matches in the lookup field. In this case, I’m looking for a solution where the lookup field contains the value, not an exact match.

Does anyone have suggestions on how to set this up?

Example:

nameProper= TESTFILE_ABCD_127845

Column A Column B
_ABCD_ Folder_A
_BCDE_ Folder_B
_FGHI_ Folder_A
_JKLM_ Folder_C

In this case the output value I need is Folder_A.

[rhd_ole]

We struggled with the same thing and ended up using Monday.com.

Via their API we are able to look at a given board, search a column for Name.Proper (or anythning) and give any matching column's data and return it as PrivateDate to be used. Works great. We also push data to it.

They have an app also but it's super expensive to use but via scripting you can get by using it. I can't remember if you can use the API with the 100% free version. We do have multiple users so we are paying something like $24/mo. Might be worth checking out.

[tdeschampsBluewest]

I'm aware of the Row2Variables app, but it only works with exact matches in the lookup field. In this case, I’m looking for a solution where the lookup field contains the value, not an exact match.

Hi Magnus, the CSV pickup app can do this :

● Filters

List of columns to filter with value(s).
If no filter is required, keep this value empty. Filters accept standard wildcards.
Format :
{column header}={value}
or :
{column header}={value1}|{value2}|{value3}
eg :
country=uk
email=john@doe.com|*smith.com
On this example, the pickup will return each line where country is equal to uk AND
email is john@doe.com, or end with *smith.com

This way you could get a dataset (json or XML) at the output of the app, and then test if you got a value or not.

In your case, the filter should be :

Column A = *_ABDC_*

[magnussandstrom]

Hi Thomas,

I'm not sure if this is what I'm looking for.

I cannot use the filer Column A = *_ABCD_* because _ABCD_ is "unknown".

I need to compare the filename (example: Myfile_ABCD_1234.pdf) and see if it contains any of the values in column A and then get a value from column B if I get a "match".

[tdeschampsBluewest]

You could use variable in a such expression, so i think it could work unless i misunderstood your point.

If it's always with a such syntax : Myfile_ABCD_1234.pdf, I suppose that "MyFile" could vary a lot, but _ABCD_1234.pdf refer to something added as a suffix (either by you, or the customer)

Code: Select all

[Job.NameProper:Search="[a-z]+(?=_\d+$)"]

wich led to this expression in csvPickup

Code: Select all

Column A = *_[Job.NameProper:Search="[a-z]+(?=_\d+$)"]_*

If it does not work/suit your purpose, do not hesitate to send me more information via mail (appstore@bluewest.fr) in order to see if we could make a new version that could suit your needs

[magnussandstrom]

The filename structure was only an example it can also be: Myfilename_that-look-like_this_ABCD_and-some-other_AB_stuff_12346_HELLOWORLD

I want to check if any of the values in column A could be found anywhere in the filename and return the value in column B from the same row.

[tdeschampsBluewest]

Oh, OK, that's more complicated. The only way i could think of right now, is to use string splitter with "_" as separator, and loop trough CSV pickup with a different part as filter for each loop.

If it's not a heavy load it may be ok, but with a lot of file going trough it could too much time consuming...

[JimmyHartington]

Easy Data Transform (EDT) has different ways to lookup data.

Maybe you could hack together a flow, where you write a csv with what you want to find.
Send it to EDT as input.
EDT transform file has hardcoded the lookup file and output the results of the lookup as a text-file.
You could then read this output into private data.

[magnussandstrom]

I took an unconventional approach to solve this. In my effort to deepen my Azure knowledge, I developed a small Python app (with help from chatGPT) to streamline lookups in an Excel file stored in Azure. I packaged the app in a Docker container and deployed it to an Azure Container App for serverless execution.

Now, I can use Switch’s HTTP request app to query the Excel file via the Azure Container API.