Answered

Extract Certain Fields from Email to Sharepoint

(0) Share

Report

Posted on by johnnykalsi

143

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",
 ]

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Rhiassuring 8,690 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (2)

Report
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']

Was this reply helpful? Yes No
Pstork1 68,757 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
johnnykalsi 143 on at

Like (1)

Report

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

Was this reply helpful? Yes No
johnnykalsi 143 on at

Like (1)

Report

I tried this tonight and it worked great

Screenshot 2023-1...

Was this reply helpful? Yes No