Topic: search for a given input from the email body and assign the result to string variables

Ok:) I added the msg.Body and print it out. As you will see in the attachment, it returns a blank after the project name instead of returning the organization. Thanks!
emailBody_missing org.xlsx (9.3 KB)

I think I identified the problem as being that there is a space next to "Organization: "

Try adjusting your pattern to this:
“(?<=”+projectKey+“\s\s*)(.+)”
“(?<=”+orgKey+“\s\s*)(.+)”

I just added a \s in there.

Regards.

Wow! That did solve it!! Thank you so much! @ClaytonM, you are amazing!!!

1 Like

Hi @ClaytonM,
I have this fellow up question of how to write the string variables (project name, org, and Fname) to a data table with 3 columns which should allow me later to iterate through rows to populate the online for each user info (project, org, and Fname). I thought it was simple, but I am actually stuck here. I tried to use for each row activity or add data row, but none of that worked as I expected. I am attaching here the workflow along with a screenshot of the write to csv file output. The current output is written in one column (A).
Thanks again for your help on this!
HousseinePasswordReset.xaml (16.2 KB)

AH, yeah, so Add Data Row is for an entire row. You are only putting in one item so it only puts it in the first column. The ArrayRow should include each column you want to add.
For example,
{ Project, Org, Fname }

so, just need 1 Add Data Row

Regards.

1 Like

Thanks @ClaytonM !! This solution worked!

Hi Clayton!

I was reading this and trying to understand but unable to do so.
Basically I wanted to use data I got from email, and extract below highlighted data under “NAME” column. May I know what data should I modify from your Regex pattern below.

image

projectKey = “Project Name:”
orgKey = “Organization:”
subjectKey = “ISM PW Reset”
projectPattern = “(?<=”+projectKey+“(.)“+System.Environment.NewLine+”)(.)”
orgPattern = “(?<=”+orgKey+“(.)“+System.Environment.NewLine+”)(.)”

messagesFiltered = messages.Where(Function(m) m.Body.ToString.Contains( subjectKey ) ).ToArray

For each msg In messagesFiltered
project = System.Text.RegularExpressions.Regex.Match(msg.Body.ToString,projectPattern).Value.Trim
org = System.Text.RegularExpressions.Regex.Match(msg.Body.ToString,orgPattern).Value.Trim

If you output what msg.Body is with like a Write Text file or something where you can get what the text is, that will be helpful.

The data is in rows, so you will need to manipulate the data into some list, so you can loop through it and process the items.

If I have extra time I can help further.

Thanks.

Hi Clayton,

Thanks for your idea… I have been trying to use Write Text File Activities, but it was perfectly copied from Outlook mail body. But when I try to use Matches command which is “(?<=” + labelBefore + “)(.*?)(?=” + labelAfter + “)” to find required data, it wont read across table array. Sorry if I sound stupid. :frowning: I guess, I have to manipulate data to csv or excel, using your suggestion to original poster. But need your help. Thank you!

Condition : (matchValue is Nothing) Or (matchvalue.Count = 0)

Hi.

I can’t tell what pattern will work without seeing what the text shows, specifically how the table is transposed to text. But, I can comment on a few issues with your pattern…

“(?<=” + labelBefore + “)(.*?)(?=” + labelAfter + “)”
I believe you will want the text variables surrounded by quotes. For example, it should be “(?<=(sometext))(.*?)(?=(sometext))”
“(?<=(” + labelBefore + “))(.*?)(?=(” + labelAfter + “))”

Also, an alternative way to get the matches is with vb dot net:
System.Text.RegularExpressions.Regex.Matches(text, pattern)
I don’t know if it will return a different result, though.

Regards.

Hi Clayton,

Some good news, but not sure if this a correct way.
I use " mail.Body.Split(" “.ToCharArray)(12).Split(” ".ToCharArray)(0) " to get the specific data and it worked. I am not familiar with Split function, but it seems does help a lot on this.

Thanks!

Cool. Yeah, you can use .Split() and is useful, just as long as your data is consistent.
Your line says to Split by the space character and take the 12th indexed item, then split that string by the space again and take the first indexed item. I’m not sure that should work though because you are already splitting by the space so when you try to split by the space again, there are no more spaces.

Let’s say for example there is a string like this: "A B C D E F"
If you split it by the space, you get an array like this: {"A","B","C","D","E","F"}
…so there are no more spaces in any item to split by. But, maybe I’m missing something.

You might also think about using the RemoveEmptyEntries option, as a safe guard against an instance where there could be 2 spaces next together.
For example .Split({" "},System.StringSplitOptions.RemoveEmptyEntries)

Good luck!