Text Parsing Question from an email

[CKonash]

Hi.   I have setup a google API to bring in emails into new FM records.   From that FM record I create new records in another table.  I'm having trouble parsing the text in the body of the email.  

Wondering if anyone has some guidance on how to accomplish extracting the "Address Number" and "Address Street from the body field in the message table.   The location changes depending on the call type (the one below is a Fire - Alarm).  but the address number is ALWAYS on line 3 and after the word "at ".   Similarly, the Address Street is always after the Address Number and the street name ALWAYS ends just before the ","   

 

Here is how the emails come in and what the Messages::Body field looks.  I'm trying to parse out the text in RED into two fields, AddressNumber and AddressStreet.

Thanks in advance for anyone that offers advice on the parse..  

Chris

 

 

[CSI Active911 - CFS Notification]

2023-20673 (P#:2023-20673)
FIRE - ALARM at 700 DARLINGTON AVENUE, MAHWAH TWP, NJ 07430 (MAHWAH SWIM CLUB)(41.066700,-74.178678)
Time reported: 09/03/23 7:16:39 PM
Unit(s) responded: 130, 140, Engine 425, Ladder 4, 1176

[comment]

I would prefer to see the actual text in the field, instead of rendered in a browser. It seems like you have some special characters in there, like non-breaking spaces?

Anyway, assuming you are doing this in a script, try something like this: 

Set an $address variable to:  

Let ( [
line3 = GetValue ( Messages::Body ; 3 ) ;
start = Position ( line3 ; "at" ; 1 ; 1 ) + 3 ;
end = Position ( line3 ; "," ; start ; 1 ) 
] ; 
Middle ( line3 ; start ; end - start )
)

Now you should have "700 DARLINGTON AVENUE" in the variable, and the question becomes how to separate the house number from the street name. Ostensibly you could set the AddressNumber field to:

LeftWords ( $address ; 1 )

and the AddressStreet to:

RightWords ( $address ; WordCount ( $address ) -1 )

However, this will fail if the house number is more than one word, for example "15/A Maple Street" - and I am not sure how to reliably test for such thing. Perhaps we should assume that the first space in the address separates between the two? But again, we would have to be sure exactly what kind of space it is.

(On the same note: I wanted to use " at " as the searchString for the start variable but it failed because of the spaces; I don't know what other call types you might have, and as it is now it will fail if any of them contains the string "at".)

 

Edited September 6, 2023 by comment