Getting a certain number / numbers out of an email.

[SergeW]

Hope you all can help me.

I get emails with content like the following

      ORD NO          DUE DATE    RECEIVED    MODE   ORIG     TYPE STATUS        STATUS STATUS STATUS STATUS STATUS  CLASS   STAT 

      ------          --------    --------    ----   ----     ---- ------        ------ ------ ------ ------ ------  -----   ---- 

     T5FWQPT1         05-08-24   05-01/15:31  AUTO   SOP      PRE  ASEC RTD      ASGD   ASGD   NO RQD FUND   FUND    RES     NRQ

     N5B4B6M6         05-16-24   05-09/16:28  AUTO   SOP      PRE  ASEC RTD      ASGD   ASGD   NO RQD FANK   FANK    RES     NRQ

     C101596P4B     SD05-17-24   05-15/12:36  AUTO   SOP      COR  ASEC RTD      ASGD   ASGD   NO RQD FANC   FANC    RES     NRQ

     C103812P8A       05-17-24   05-13/18:25  AUTO   SOP      PRE  ASEC RTD      ASGD   ASGD   NO RQD FUAU   FUAU    RES     NRQ

     C105229P1A       05-17-24   05-14/19:33  AUTO   SOP      PRE  ASEC RTD      ASGD   ASGD   NO RQD FANK   FANK    RES     NRQ

     C105265P0A       05-17-24   05-14/20:30  AUTO   SOP      PRE  ASEC RTD      ASGD   ASGD   NO RQD FANK   FANK    RES     NRQ

Basically I only want the items in red. The red items will be random and sometimes I will only get one and some days I'll get six like the above. I tried to extract it to excel, but I wasn't able to get everything lined up in a cell individually. My first idea was to go back x amount of characters from "AUTO" since that would be my first constant, but as you can see line 2 would through a monkey wrench into my idea. Might still work if i pad it a little.

My flow basically starts out with the HTML to text function.

[abm · Accepted solution]

Hi @SergeW 

Believe you are after the Order number only. Follow the below steps:

Above expression is as follows:

split(outputs('Compose'),outputs('Compose_2'))

Next all steps are inside the Apply To Each. See below:

Above expressions are:

skip(outputs('Compose_3'),4) (This will skip the first few lines such as header, dotted lines and blank lines)

Compose 4 expression is : item()

Then the IF condition to check whether its a blank line or not?

Then finally to get the Order No expression is

split(outputs('Compose_4'),'  ')?[2]

Here is my unit test result:

Thanks

[SergeW]

abm

Thank you for the response and I was able to replicate your solution, but I guess I should have included my "html to text" output. I've been playing with trying to make it work but haven't figured out how to line it up with your solution.

Here is the html to text

Fri May 17 09:30:39 2024 Page 1 of 1 WC 318253 ** FACS PENDING ORDER REPORT **
FULL REPORT EMP PL683R SORTED BY DUE DATE, ORDER NUMBER WC 318253 DLE INV N PASS
ORDER LOOP COF COFUPD SOAC SOP RREQ ORD NO DUE DATE RECEIVED MODE ORIG TYPE
STATUS STATUS STATUS STATUS STATUS STATUS CLASS STAT ------ -------- --------
---- ---- ---- ------ ------ ------ ------ ------ ------ ----- ---- T5FWQPT1
05-08-24 05-01/15:31 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FUND FUND RES NRQ
N5B4B6M6 05-16-24 05-09/16:28 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FANK FANK
RES NRQ C101596P4B SD05-17-24 05-15/12:36 AUTO SOP COR ASEC RTD ASGD ASGD NO RQD
FANC FANC RES NRQ C103812P8A 05-17-24 05-13/18:25 AUTO SOP PRE ASEC RTD ASGD
ASGD NO RQD FUAU FUAU RES NRQ C105229P1A 05-17-24 05-14/19:33 AUTO SOP PRE ASEC
RTD ASGD ASGD NO RQD FANK FANK RES NRQ C105265P0A 05-17-24 05-14/20:30 AUTO SOP
PRE ASEC RTD ASGD ASGD NO RQD FANK FANK RES NRQ * MULTI-WIRE CENTER ORDER &
SPECIAL FOREIGN EXCHANGE ORDER c REUSE COMBINED ORDER q QUEUED TRANSACTION s
SPECIAL SERVICE ORDER i REUSE INDIVIDUAL ORDER SENT 09:30:39 05/17/2024*
TFPL683R / MSG# 3270 LPR 00 SESSION 01307 BYTES 0002018

[abm]

Hi @SergeW 

Yes you should have given the right data earlier. Based on that community can help you. Always put a detailed description of what you want to achieve and where exactly you need the help. Also a sample data always greatly help.

My solution only works for the data you first posted. For the second set of data you shown earlier is not going to work in that logic. We need some consistencies in the data where we can split and divide.

So for the above given data I can see there are no carriage returns in each lines but I can see for each items NRQ is the word ending with. Is that for all of your data or it could different value as well.

Let me know.

Thanks

[SergeW]

@abm I appoligize about that.

I'm fairly new at this and trying to learn as I go.

The NRQ would be the same all the time and I did notice that, but I think that would work except for the first order number.

Now let me ask this, would just the raw input from the email work? so instead of using the "html to text" I use the dynamic content email "body" which looks like the below.

actual text in the input

Fri May 17 09:30:39 2024 Page 1 of 1
WC 318253
** FACS PENDING ORDER REPORT ** FULL REPORT
EMP PL683R
SORTED BY DUE DATE, ORDER NUMBER
WC 318253 DLE INV N
PASS ORDER LOOP COF COFUPD SOAC SOP RREQ
ORD NO DUE DATE RECEIVED MODE ORIG TYPE STATUS STATUS STATUS STATUS STATUS STATUS CLASS STAT
------ -------- -------- ---- ---- ---- ------ ------ ------ ------ ------ ------ ----- ----
T5FWQPT1 05-08-24 05-01/15:31 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FUND FUND RES NRQ
N5B4B6M6 05-16-24 05-09/16:28 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FANK FANK RES NRQ
C101596P4B SD05-17-24 05-15/12:36 AUTO SOP COR ASEC RTD ASGD ASGD NO RQD FANC FANC RES NRQ
C103812P8A 05-17-24 05-13/18:25 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FUAU FUAU RES NRQ
C105229P1A 05-17-24 05-14/19:33 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FANK FANK RES NRQ
C105265P0A 05-17-24 05-14/20:30 AUTO SOP PRE ASEC RTD ASGD ASGD NO RQD FANK FANK RES NRQ

* MULTI-WIRE CENTER ORDER & SPECIAL FOREIGN EXCHANGE ORDER c REUSE COMBINED ORDER
q QUEUED TRANSACTION s SPECIAL SERVICE ORDER i REUSE INDIVIDUAL ORDER

SENT 09:30:39 05/17/2024* TFPL683R / MSG# 3270
LPR 00 SESSION 01307 BYTES 0002018

[abm]

Hi @SergeW 

The above text got carriage returns (EnterKey) in each text. Also is the below is the standard text for all the data when you recieve

Fri May 17 09:30:39 2024 Page 1 of 1
WC 318253
** FACS PENDING ORDER REPORT ** FULL REPORT
EMP PL683R
SORTED BY DUE DATE, ORDER NUMBER
WC 318253 DLE INV N
PASS ORDER LOOP COF COFUPD SOAC SOP RREQ
ORD NO DUE DATE RECEIVED MODE ORIG TYPE STATUS STATUS STATUS STATUS STATUS STATUS CLASS STAT
------ -------- -------- ---- ---- ---- ------ ------ ------ ------ ------ ------ ----- ----

9 rows of data before the Order No and other items

Thanks

[SergeW]

@abm thank you again.

I was able to modify your original solution to use the "body" of the email and add an additional filter to get just the order number.

[abm]

Hi @SergeW 

Fantastic & thanks for letting me know.

If your problem has solved, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thanks