cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maZen
New Member

Extract text from body of an email and create multiple new rows in excel spreadsheet

Hello,


as mentioned in the title, I'd like to extract information from an e-mail and add them to a row, similar to this problem:


https://powerusers.microsoft.com/t5/Building-Flows/Extract-text-from-body-of-an-email-and-create-new...


For my case, I would like to extent this to the following:

When an e-mail is send with these example information (the format does not need to be comma separated):

Date:20180410, 21880410

Unit Number:200

Run Number:180

Pick Up:100

Drop Off:80

 

Addition: There can more than one date or multiple (2,3,4....)

 

I would like to generate two new rows, where the two dates share the same information as below:

20180410 200 180 100 80

21880410 200 180 100 80

xxxxxxxxx 200 180 100 80


Is this possible (I thought of creating an array with something like a for / while loop).


Secondly, is it possible to create even more rows if other entries are multiple entries as well? Something like this:

 

Date:20180410, 21880410

Unit Number:200

Run Number:180

Pick Up:100, 200

Drop Off:80

 

Resulting in somthing like this:

20180410 200 180 100 80

21880410 200 180 100 80

20180410 200 180 200 80

21880410 200 180 200 80

 

Thank you in advance,


maZen

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yiwenxie-msft
Microsoft
Microsoft

Hi @maZen 

 

If there is only ‘Date’ has multiple entries, it’s possible to add multiple rows into an excel table.

But if there is more than one property have multiple entries, I’m afraid it’s not possible.

 

The following flow is for your reference:

3.4-10.PNG

 

The detailed steps for these ‘compose’ and ‘initialize variable’:

3.4-11.PNG

The ‘Value’ put in ‘initialize variable’ is an ‘Enter’ on the keyboard.

 

The function used:

split(outputs('Compose'),variables('enter'))

 

the ‘inputs’ in ‘Compose 4’:

["0","2","4","6","8"]

 

For more info about the functions split(), outputs(), variables():

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

The detailed steps for ‘Apply to each’:

3.4-12.png

‘Compose 3’:

trim(outputs('Compose_2')[int(items('Apply_to_each'))])

 

‘Compose 5’:

split(outputs('Compose_3'),':')[1]

 

‘Compose 6’:

split(string(outputs('Compose_5')),',')

 

For more info about trim(), int(), string():

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

[0] is used to get the first item in an array, [1] is used to get the second item in an array, and so on.

 

The detailed step for ‘If no’:

3.4-13.PNG

 

The detailed steps for ‘Apply to each 2’:

3.4-14.PNG

The expressions used in succession:

first(outputs('Compose_6'))

items('Apply_to_each_2')

variables('one entry')[0]

variables('one entry')[1]

variables('one entry')[2]

variables('one entry')[3]

 

[0] is used to get the first item in an array, [1] is used to get the second item in an array, and so on.

 

For more info about the functions first(), items():

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

If you still have the problems, please feel free to let me know.

 

Best Regards,
Community Support Team _ Kira Xie
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

1 REPLY 1
v-yiwenxie-msft
Microsoft
Microsoft

Hi @maZen 

 

If there is only ‘Date’ has multiple entries, it’s possible to add multiple rows into an excel table.

But if there is more than one property have multiple entries, I’m afraid it’s not possible.

 

The following flow is for your reference:

3.4-10.PNG

 

The detailed steps for these ‘compose’ and ‘initialize variable’:

3.4-11.PNG

The ‘Value’ put in ‘initialize variable’ is an ‘Enter’ on the keyboard.

 

The function used:

split(outputs('Compose'),variables('enter'))

 

the ‘inputs’ in ‘Compose 4’:

["0","2","4","6","8"]

 

For more info about the functions split(), outputs(), variables():

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

The detailed steps for ‘Apply to each’:

3.4-12.png

‘Compose 3’:

trim(outputs('Compose_2')[int(items('Apply_to_each'))])

 

‘Compose 5’:

split(outputs('Compose_3'),':')[1]

 

‘Compose 6’:

split(string(outputs('Compose_5')),',')

 

For more info about trim(), int(), string():

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

[0] is used to get the first item in an array, [1] is used to get the second item in an array, and so on.

 

The detailed step for ‘If no’:

3.4-13.PNG

 

The detailed steps for ‘Apply to each 2’:

3.4-14.PNG

The expressions used in succession:

first(outputs('Compose_6'))

items('Apply_to_each_2')

variables('one entry')[0]

variables('one entry')[1]

variables('one entry')[2]

variables('one entry')[3]

 

[0] is used to get the first item in an array, [1] is used to get the second item in an array, and so on.

 

For more info about the functions first(), items():

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

If you still have the problems, please feel free to let me know.

 

Best Regards,
Community Support Team _ Kira Xie
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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,025)