Extract Certain Fields from Email to Sharepoint

[johnnykalsi]

How to i extract certain fields from this to Sharepoint? Issue is I cant use line #'s because sometimes fields are missing so line number might change.

[
" ",
"NOL #:",
"560286",
"Offense Date:",
"Aug 10 2023 5:09PM",
"Intersection:",
"ETEXTTEXT St @ TEXTTEXT",
"Vehicle Information",
"ETEXTTEXT ID:",
"26771",
"License Plate:",
"TEXTTEXT",
"Make:",
"JEEP",
"Model:",
"PATRIOT",
"Agency Code:",
"TEXTTEXT",
"Account No:",
"Vehicle Owner",
"Name:",
"TEXTTEXT",
 ]

[Rhiassuring]

Here is the easiest way I can come up with at the moment, I was thinking there'd be a way to search an array for a value and return the associated array index, but I can't figure that out (don't even know if it exists.. @Expiscornovus , @wskinnermctc , @Pstork1  - you guys know of anything?). So, I found another way.

I've pasted your text into a string variable called strData.

Above, I'm doing the following:

Initialize Variable - arrKeys: Creating an array of all of the named types of things I want to retrieve. I've only put 3 in as an example, just Make, Agency Code, Intersection.  

Compose - Remove Quotations: I'm just replacing all " with blank, so it's cleaner.

Apply to Each: I'm aiming it at our array of keys. On each cycle, it will select a different one to evaluate. 

Compose - Split on First Key: Here, I'm splitting the cleansed data by the first value from the array ("Make:")

Find text position - Ending Value Comma: Here, I'm selecting the last() item from our split text, and then I'm searching for the first comma (as that tells us the end of the value associated with our key.)

Substring - Extracted Value: Then, again on the last() part of that split array, we're getting a substring - starting from 0 - the amount of characters that the "find text position" told us.

In this scenario, that last Substring returns "JEEP". 

Now you have a few options. You could build another array, (ie, use "Append to an Array", and then do "Current Item":"Substring - Extracted Value" -- it would give you a full, clean array to input to SharePoint.)  

OR, you could create a variable for each type - ie, txtMake, txtAgency, txtIntersection, etc, and select the variable by using a matrix to identify the right one, or something like that. Depends what you need to do with it.

[grantjenkins · Accepted solution]

Below is how I'd likely build the flow using some XPath expressions.

See full flow below. I'll go into each of the actions.

JSON is a Compose that contains your data.

XML is a Compose that converts your data to XML so we can use XPath. The expression is below.

xml(json(concat('{"root": { value:', outputs('JSON'), '}}')))

Initialize variable data creates a variable called data of type Object and sets it to an empty object using opening and closing curly braces {}.

Apply to each iterates over each of the items that contain a period : These are your property names. The expression used is below.

xpath(xml(outputs('XML')), '//root/value/text()[substring(., string-length(.)) = ":"]')

Item is a Compose that appends the property name and value using the following expression.

addProperty(
    variables('data'), 
    replace(item(), ':', ''), 
    xpath(xml(outputs('XML')), concat('string(//value[contains(./text(), "', item(), '")]/following-sibling::*[1]/text())'))
)

Set variable data sets the new value of the variable data to the output from Item. This will eventually contain all the properties and their values in an object for you to use.

The variable data would now contain the following:

{
  "NOL #": "560286",
  "Offense Date": "Aug 10 2023 5:09PM",
  "Intersection": "ETEXTTEXT St @ TEXTTEXT",
  "ETEXTTEXT ID": "26771",
  "License Plate": "TEXTTEXT",
  "Make": "JEEP",
  "Model": "PATRIOT",
  "Agency Code": "TEXTTEXT",
  "Account No": "Vehicle Owner",
  "Name": "TEXTTEXT"
}

To access the individual items you can use the following expressions.

variables('data')?['NOL #']
variables('data')?['Offense Date']

variables('data')?['PROPERTY NAME']

[Pstork1]

I made an assumption and took a slightly different approach. I assumed that the key for the value was always the value immediately prior to it in the list that ended with ':'. If that is valid to assume then this is the process I used.

1) Clean the input from email by replacing all the newline characters using decodeUriComponent('%0A').

2) Split the result on the commas to get an array of the values in the list

3) Use that array in an apply to each. Check the current item to see if it is contains a ':'. If it does then store that value in a variable called currentKey for the next loop. If it doesn't then check to see if the currentKey is blank. If its not then append the currentKey and the current Item value to an Array variable. YOu'll also have to clean up additional characters like quotes and colons from the result.

4) The output of the loop will give you an array of values where the keys were supplied as previous values. You can then run that through a Parse JSON to have access to the individual keys.

This will use only one apply to each rather than one apply to each for each key.

[johnnykalsi]

Thanks folks...I'm going to try each solution this weekend and get back 

[johnnykalsi]

I tried this tonight and it worked great