Iterating through Cell Range and using each value in Regex

[Steven_McKeering]

Hello

I am trying to build a process in Studio which will:

1. Read a PDF of a Customer’s email order (activity: Read PDF Text)
2. Find keyword/s from an excel reference list (so it can be updated and expanded for additional/new Topics). (Activity: unsure)
3. Determine Topic from keywords found in reference list. If possible - tally up the keywords and select the Order Topic from the highest tally number.
   (Example: keywords found - 3 food, 2 shapes and 1 Stationery ->Selects Order topic as ‘Food’.
   Example: keywords found - 2 shapes, 1 stationery and 0 food → Selects order topic as ‘Shape’)
   Reference table example below:
   
   
   
   Open to other options…
4. Then enters into a word document the result.

My challenges/knowledge gaps with this process:

• I can Regex out the Name, phone and email address and email body text (additional-comments), BUT I am unsure about how to regex keywords from a excel reference list /excel column (see above).
• Then select the adjacent cell and copy the text (example: keyword found in A6, grab text in B6 (Topic).

Attachments
I have attached 3 customer email examples and an excel reference list.
Forum example 1.zip

Any assistance would be appreciated.

Thank you.

[loginerror (Maciej Kuźmicz)]

Hi @Steven_McKeering

You should look at two base activities for that:

• Read Range → this one will load your entire Excel reference into a new variable of type DataTable.
• For Each Row → this one allows you to loop each row of the newly loaded DataTable variable

With these two activities, you can filter your table however you want. If you don’t want to use code, there are several Data Table activities that could help you out here:

The one to point out would be either Filter Data Table or Lookup Data Table.

Naturally, if you do want to code a bit more, you can check our Forum for many examples on how to use the .Select method on the DataTable variable.

Now, to do that, and assuming you have already loaded your Excel file range to a DataTable, you could loop through the rows of your inputDT by using the For Each Row activity.
Each row in that loop can be referenced by item by default, and if you want to reference specific column (based on your sample file column headers), you do it like so:

item("Item").ToString

or

item("Order Topic").ToString

I suppose you could use this as an input for your Regex right there.

I hope it helps a little