Select & Copy number after consistant word - Apple Mail

Questions & Suggestions
1

I’ve tried several things, but I could use some help. I’m trying to copy a number that appears in a weekly email and paste it into excel. I have hundreds of these emails, and they are all formatted the same. The number I want to copy from each ( Apple Mail ) email message is in the message body and is always proceeded by a consistent text string.

What I need help with is accurately selecting the text to copy. As a human, I know that the number to copy is always to the right of the words: “Market Value: $”, and ends at the next space. But I’m not sure how to explain that so a machine can do it. Once I get that number selected & copied, I think I can handle the rest of the macro. (pasting that number into a spreadsheet, advancing a cell, advancing to the next Apple Mail message, etc.)

If I were working in FileMaker pro, I would try to read the position of the phrase, read the position of the next space after the phrase, then do some text math to select the characters between those positions. But these email messages are all in Apple Mail as html mail. If there is no better way, I guess I could export all the messages as text so they could be processed by another app with better search capability, like FileMaker Pro, or maybe TextWrangler?

Is there a way for Keyboard Maestro to select a text string in an Apple Mail message, by knowing what is before & what is after?

2

I'm sure that a RegEx pattern can extract the number for you.
Unfortunately, I'm such a RegEx novice that I can't figure it out for you.

You will probably need AppleScript to get the email, and extract the body text into a variable. Then perform the RegEx on that. Could do that in either AS or KM. If using AS, the Satimage AS extension (it's free) has an excellent RegEx tool (Find). If you get the RegEx pattern, I can help you with using the RegEx tool, if you need it.

If you don't get a workable response here, you might try posting your question on the StackOverflow.com board. I a got a very quick response to another RegEx question recently.

1 Like
3

Hopefully someone with knowledge of RegEx will wade in – but my 2 cents is a call-out to Regexr.com. I too know next-to-nothing about RegEx, but used this site to practice on the construction of the formula, which helped me get close enough to what I needed for it to work in Scrivener and Text Wrangler.

1 Like
4

Thanks for mention of Regex & the pointer to Regexr.com those were super helpful, and I pounded away as if I were one of a thousand monkeys with typewriters who knew that eventually one of us was going to write the entire works of Sir Conan Doyle. :wink:

Anyway, What I have so far is a Regex statement that will select the flag text AND the target text.
Market Value:.$+[0-9,.]*
Next, I just have to figure out out how to get rid of the phrase; "Market Value: $"
Either via more Regex, or pasting it somewhere & using Keyboard Maestro to delete the bits I don’t want.
One step at a time.

Dave

5

Hello Dave,

That's simple enough.

Here's the regex:

(?m)Market[[:blank:]]*Value:[[:blank:]]*\$([0-9.]+)(?=[[:blank:]]|$)

The macro looks more complicated than it is, because I'm doing a little error-checking and cleanup.

-Chris

Macro made in KM7.

Extract Market Value from Selected Email v1.01.kmmacros (5.8 KB)

1 Like
6

Hey Dave,

By the way...

It's always better to provide a real data-sample than to give a written explanation — and both are better still.

Since I couldn't see the real data I had to guess about whether you described the text parameters correctly.

In this case I've allowed for the possibility of blank-space after the value OR the end-of-line after the value.

It would have been easier and faster if I'd been able to see and test with a real data-sample in context.

If the macro doesn't work as is please provide more information.

-Chris

7

Wow! :grin:

So much of usefulness here! Thanks for the examples – my mind is already running gears at where this could be useful. Looks like I’ll be spending more time learning RegEx!

Hope you come right Dave!

Please share if you do!

8

I made a small edit to my macro in post 5.

  • Changed the regular expression syntax slightly.

-ccs

9

WOW! Awesome help! Thanks - I'm learning a ton just from seeing what you did there. I have to run, and I will pick this up again shortly - but I want to give a huge well- deserved shout out of "THANKS!" for the help.

Attached is a screenshot of one of those emails as example data. (This one is not a particularly impressive number, but it's all part of the fun).

I'm also interested in the date from each email that I get a market value from. I tried to modify your Regex to capture what is after the first occurrence of "As of " and any 4 characters, but got tangled up. (It is possible even if unlikely, that the phrase "As of " might reoccur later in the message as related news. Can you also suggest a regex to grab the date? I don't care about the time, but the month, day of month & year would be great.

My plan includes sending the captured data to a text file (then eventually to a spreadsheet to produce a chart), and I have discovered the KM command to "Append Text to File" which is useful to build an output file one line at a time, adding a return at the end of each line, & a tab between fields. Awesome.

Copied & pasted exactly, the text we are talking about looks like this:

Portfolio Digest

As of Fri Jun 12 21:04:30 2015
Portfolio: XXXX--08 Watch List Edit Preferences | Unsubscribe

Market Value: $39.70 (-$0.53/-1.41%)

Again, many THANKS for your awesome help! I help a lot of other people daily, but I don't expect help myself and am grateful.

Dave

10

Hey Dave,

I'm just now getting back to this.

(?i)As of[[:blank:]]+([a-z]{3})[[:blank:]]+([a-z]{3})[[:blank:]]+(\d{1,2})[[:blank:]][\d:]{5,}[[:blank:]]+(\d{4})

I've split this apart, so you can do whatever you want with the pieces.

I haven't tested against all possible cases, so if it breaks anywhere let me know — and I'll fix it.

-Chris

1 Like