cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

text to spreadsheet flow

Hi All,

 

Looking for any guidance please,

 

I have a flow that converts email (html to text) then imports selected fields into a spreadsheet using strings. so for example:

 
Ticket:INC00000000000; ServiceTag:123456; ComputerName:name; Status:Transferred; Model:Latitude 7480; Location:shop  ; User: joe bloggs; Comment: new user;
 
However after this has been imported i have some emails where there will be more that one submission in the email but the flow will only import the first submission.
 
My question is, is there a way to import multiple into the spreadsheet?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: text to spreadsheet flow

Hi @Price83,

 

You could use the split() function to convert the text into an object by following steps:

 

Separate the original text into key-value pair:

split(outputs('Compose'),'; ')

Annotation 2020-05-21 111420.jpg

Initialize a String variable to hold the converted object, the default value should have a "{".

 

Then use Apply to each to loop through these key-value pairs, and separate key and value by using twice split() function:

split(item(),':')[0]
split(item(),':')[1]
Then structure them into "split(item(),':')[0]": "split(item(),':')[1]",

Annotation 2020-05-21 111555.jpg

Don't forget add a "}" at the end of the variable.

 

Use replace() function to remove the additional "," at the last key-value pair, then use the json() function to convert the variable into object:

Annotation 2020-05-21 111649.jpg

 

Result:

Annotation 2020-05-21 111719.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: text to spreadsheet flow

Hi @Price83,

 

You could use the split() function to convert the text into an object by following steps:

 

Separate the original text into key-value pair:

split(outputs('Compose'),'; ')

Annotation 2020-05-21 111420.jpg

Initialize a String variable to hold the converted object, the default value should have a "{".

 

Then use Apply to each to loop through these key-value pairs, and separate key and value by using twice split() function:

split(item(),':')[0]
split(item(),':')[1]
Then structure them into "split(item(),':')[0]": "split(item(),':')[1]",

Annotation 2020-05-21 111555.jpg

Don't forget add a "}" at the end of the variable.

 

Use replace() function to remove the additional "," at the last key-value pair, then use the json() function to convert the variable into object:

Annotation 2020-05-21 111649.jpg

 

Result:

Annotation 2020-05-21 111719.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: text to spreadsheet flow

Hi Thanks for the detailed answers i have applied this and the flow runs with no errors but if i have another submission underneath for example:

 

Ticket:INC00000000000; ServiceTag:123456; ComputerName:name; Status:Transferred; Model:Latitude 7480; Location:shop  ; User: joe bloggs; Comment: new user;

 

Ticket:INC00000000000; ServiceTag:123456; ComputerName:name; Status:Transferred; Model:Latitude 7480; Location:shop  ; User: joe bloggs; Comment: new user;

 

Ticket:INC00000000000; ServiceTag:123456; ComputerName:name; Status:Transferred; Model:Latitude 7480; Location:shop  ; User: joe bloggs; Comment: new user;

 

it will only pull in the first submission?

Highlighted
Community Support
Community Support

Re: text to spreadsheet flow

Hi @Price83,

 

If there are multiple records need to process, you should use split() function to separate them individually, firstly, you should initialize a string variable named Breakline or else you like, then select the gap of the two records, in my screenshots red part, then copy the red part, and paste them to the Value field of the Breakline:

Annotation 2020-05-22 164522.jpg

 

Then use the split() function:

Annotation 2020-05-22 165124.jpg

 

 

The result will be three single records in an array:

Ticket:INC00000000000; ServiceTag:123456; ComputerName:name; Status:Transferred; Model:Latitude 7480; Location:shop  ; User: joe bloggs; Comment: new user;

 

Then put the output of Compose 2 into the Apply to each action to loop through each item to do steps just as my first reply, please have a try:

Annotation 2020-05-22 165319.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: text to spreadsheet flow

Hi 

 

what is in compose 3?

Highlighted
Community Support
Community Support

Re: text to spreadsheet flow

Hi @Price83,

 

Compose 3 is the beginning of my first reply, which is the compose2 in the first reply. And the expression should be:

split(item(),'; ')

Annotation 2020-05-27 175831.jpg

 

Then the following steps are same with my first reply.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (10,512)