How to export unstructured data in a tabular manner from email to Excel

[Karthik440]

Hello Team, I have a special request. Please see the input below.

PRINTGROUP L-PAGES ENVELOPES CHECKS ADVICES MRDF
-------------- ------- --------- -------- --------- ---------
Domestic 854 854 55 799 129ET405
Foreign 0 0 0 0 129ET406
IOWA_01_1006 0 0 0 0 129ET407
IOWA_02_2055 0 0 0 0 129ET408
IOWA_03_3504 0 0 0 0 129ET409
PR_01_4084 0 0 0 0 129ET410
PR_02_4092 0 0 0 0 129ET411
PR_03_4099 0 0 0 0 129ET412
PR_04_4116 0 0 0 0 129ET413
PR_05_4018 0 0 0 0 129ET414
PR_06_4158 0 0 0 0 129ET415
PR_07_4093 0 0 0 0 129ET428

Is it possible to transfer the data from an email to an Excel spreadsheet in a tabular format utilizing the aforementioned input? Space serves as a divider.

@abm Would you kindly investigate and offer advice?

[abm]

Hi @Karthik440 

Is the above data in your email body and you want to create these in an Excel sheet?

Thanks

[Karthik440]

Yes @abm that is correct.

[abm]

Hi @Karthik440 

I have a video tutorial based on this. Please have a look and comment. 

https://www.youtube.com/watch?v=62BB3rpe37g&t=154s

Thanks

[grantjenkins · Accepted solution]

@Karthik440 Below is how I'd build the flow. This assumes the only content in the email is what you provided, and also that the headers will always be the same - if the headers could differ, then let us know.

Note that I haven't added the actions that add the data to your Excel file as don't know if you have an existing Excel Table you want to add the rows too, need to create a new Excel file, are ok with CSV file, etc.

Below is a sample of the email sent that triggers the flow.

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

When a new email arrives is what you already have. I assume you're filtering on Subject.

Html to text converts the HTML output from the email to plain text.

Filter array uses the following expressions to extract the data into an array of items. This will also remove any empty rows that are returned.

//From
split(outputs('Html_to_text')?['body'], decodeUriComponent('%0A'))

//Condition
trim(item())

Select uses the following expressions to build up your JSON objects.

//From - skip 2 will remove the first two rows
skip(body('Filter_array'), 2)

//Map
split(item(), ' ')[0]
split(item(), ' ')[1]
split(item(), ' ')[2]
split(item(), ' ')[3]
split(item(), ' ')[4]
split(item(), ' ')[5]

After running the flow, the Select would contain the following output.

[
  {
    "PRINTGROUP": "Domestic",
    "L-PAGES": "854",
    "ENVELOPES": "854",
    "CHECKS": "55",
    "ADVICES": "799",
    "MRDF": "129ET405"
  },
  {
    "PRINTGROUP": "Foreign",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET406"
  },
  {
    "PRINTGROUP": "IOWA_01_1006",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET407"
  },
  {
    "PRINTGROUP": "IOWA_02_2055",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET408"
  },
  {
    "PRINTGROUP": "IOWA_03_3504",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET409"
  },
  {
    "PRINTGROUP": "PR_01_4084",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET410"
  },
  {
    "PRINTGROUP": "PR_02_4092",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET411"
  },
  {
    "PRINTGROUP": "PR_03_4099",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET412"
  },
  {
    "PRINTGROUP": "PR_04_4116",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET413"
  },
  {
    "PRINTGROUP": "PR_05_4018",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET414"
  },
  {
    "PRINTGROUP": "PR_06_4158",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET415"
  },
  {
    "PRINTGROUP": "PR_07_4093",
    "L-PAGES": "0",
    "ENVELOPES": "0",
    "CHECKS": "0",
    "ADVICES": "0",
    "MRDF": "129ET428"
  }
]

If you wanted to create a CSV table, you could use Create CSV table and use the output from the Select.

Let us know about how you would want to save this data - Existing Excel File/Table, Need a new Excel File, CSV, etc.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Karthik440]

Hi @grantjenkins, Thank you very much for the succinct response. When I asked a query, I didn't anticipate that the answer would be in power automate. however, you made it possible. thank you a lot

addition to the query posed yesterday.

My yesterday's Input was mocked and criticised. Please refer to the original input below.

EXTERNAL EMAIL

Hi, This is the control report for - ***** ******* ***** - **.
Contol report for ***** ******* *****.

JOB = ******** Date = 05/11/2022 Time = 14:39:41
----------------------------------------------------------------------

PRINT GROUP L-PAGES ENVELOPES CHECKS ADVICES MRDF
-------------- ------- --------- -------- --------- --------------
Domestic 26 26 13 13 131ET605
Foreign 0 0 0 0 131ET606
Special Handling
----------------
IOWA_01_1006 0 0 0 0 131ET607
IOWA_02_2055 0 0 0 0 131ET608
IOWA_03_3504 0 0 0 0 131ET609
PR_01_4084 0 0 0 0 131ET610
PR_02_4092 0 0 0 0 131ET611
PR_03_4099 0 0 0 0 131ET612
PR_04_4116 0 0 0 0 131ET613
PR_05_4018 0 0 0 0 131ET614
PR_06_4158 0 0 0 0 131ET615
PR_07_4093 0 0 0 0 131ET628

UNION_01_2500 0 0 0 0 131ET616
UNION_02_3503 0 0 0 0 131ET617
UNION_03_3601 0 0 0 0 131ET618
UNION_04_4070 0 0 0 0 131ET619
UNION_05_4103 0 0 0 0 131ET620
UNION_06_4130 0 0 0 0 131ET621
UNION_07_5170 0 0 0 0 131ET622
UNION_08_5222 0 0 0 0 131ET623
UNION_09_6313 0 0 0 0 131ET624
UNION_10_5245 0 0 0 0 131ET625
UNION_11_5221 0 0 0 0 131ET626
UNION_12_6018 0 0 0 0 131ET627
LC_4172 0 0 0 0 131ET629
LC_7356 0 0 0 0 131ET632
COOPER DEDUCT 0 0 0 0 131ET633
CALF SPL HD 0 0 0 0 131ET634
UNION_13_5232 0 0 0 0 131ET635
-----------------------------------------------------------------------
Total Accounts = 26 Total Checks = 13 Total Advices = 13

FILENAME ENVELOPES CHECKS ADVICES
--------------- --------- -------- ---------
10701 13 13 0
10702 13 0 13
-----------------------------------------------------------
Totals 26 13 13

Regards,
********* ******** ********

There are two tables in the input. The first table starts with PRINTGROUP and stops before the Total accounts. and the second table starts with FILE NAME and stops before the Totals.

also we have to skip the below from the first table

Special Handling
----------------

The same emails(input) will be sent to me every day. 

Can you maybe provide some guidance on using Excel in place of the CSV format? I want to import the data from the first table into one tab and the data from the second table into another tab of the same Excel. Thank you very much.

[grantjenkins]

Would you be appending the data to an existing Excel file, or would you create a new Excel file each day that would contain just the data from that day? And assuming it would be stored in a library/folder within SharePoint?

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for. I've assumed you want to create a new Excel file for each day.

We can't just create a blank Excel file in Power Automate (or at least not easily). The best way is to have a Template file that contains the two worksheets and tables already pre-created, then use the content from that Template file to create a new Excel file (effectively copying the Template file). In this example, I've got a folder called Daily within my Documents Library and created an Excel file called Template.xlsx.

Below is the email that triggers the flow.

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

And within the Apply to each actions.

When a new email arrives and Html to text is the same as what you had before.

Filter array Table1 uses the following expressions. Note that you will need to click on Edit in advanced mode to paste the Condition expression in.

//From
split(
    slice(
        outputs('Html_to_text')?['body'], 
        indexOf(outputs('Html_to_text')?['body'], 'PRINT GROUP'), 
        indexOf(outputs('Html_to_text')?['body'], 'Total Accounts')
    ), 
    decodeUriComponent('%0A')
)

//Condition (Edit in advanced mode)
@and(
    not(equals(trim(item()), '')),
    not(contains(item(), '----------')),
    not(contains(item(), 'Special Handling'))
)

Select Table1 uses the following expressions.

//From
skip(body('Filter_array_Table1'), 1)

//Map
split(item(), ' ')[0]
split(item(), ' ')[1]
split(item(), ' ')[2]
split(item(), ' ')[3]
split(item(), ' ')[4]
split(item(), ' ')[5]

Filter array Table2 uses the following expressions. Note that you will need to click on Edit in advanced mode to paste the Condition expression in.

//From
split(
    slice(
        outputs('Html_to_text')?['body'], 
        indexOf(outputs('Html_to_text')?['body'], 'FILENAME ENVELOPES'), 
        lastIndexOf(outputs('Html_to_text')?['body'], 'Totals')
    ), 
    decodeUriComponent('%0A')
)

//Condition (Edit in advanced mode)
@and(
    not(equals(trim(item()), '')),
    not(contains(item(), '----------'))
)

Select Table2 uses the following expressions.

//From
skip(body('Filter_array_Table2'), 1)

//Map
split(item(), ' ')[0]
split(item(), ' ')[1]
split(item(), ' ')[2]
split(item(), ' ')[3]

Get file content gets the content from the Template Excel file we created.

Create file uses the content from Get file content using the following expression to build the File name in the format "Daily Report yyyy-MM-dd.xslx"

concat('Daily Report ', formatDateTime(utcNow(), 'yyyy-MM-dd'), '.xlsx')

Apply to each Table1 iterates over the items from Select Table1 and for each item, adds the data into a new row in our Excel Table. The Path comes from Create file and we need to manually type Table1 as our Table name as a custom value. For the Row we can just use the Current item which is the current item we are iterating over.

Apply to each Table2 iterates over the items from Select Table2 and for each item, adds the data into a new row in our Excel Table. The Path comes from Create file and we need to manually type Table2 as our Table name as a custom value. For the Row we can just use the Current item which is the current item we are iterating over.

After running the flow, we should get a new Excel file with data populated in both the Tables.

One thing I did notice in the Table 1 data is that some of your values have spaces in them, so when we split by space it split out those values incorrectly. COOPER and CALF. Would they contain spaces in your actual data? If so, then we would need to work out how to cater for those.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Karthik440]

@grantjenkins Well, I'll be adding the data to an already-existing Excel file, and you're right that it will be kept in a library or folder on OneDrive or Sharepoint.

[Karthik440]

Hi @grantjenkins Thank you so much for your excellent work, and if you could assist me with the final step, that would be greatly appreciated. I want to add the data to an existing Excel file.

[grantjenkins · Accepted solution]

If you had an existing Excel file with the two Tables, then you would just be able to remove the Get file content and Create file actions, and for your Add a row into a table action - just select the existing Excel file and Table, then add the data.

[Karthik440]

@grantjenkins Thank you so much. I eliminated the Get file content and Create file actions as you suggested, and I chose the already-existing Excel file and Table in the Add a row into a table action. and used a previously used trigger to automatically test the flow. 

The flow was successful, but the table had empty rows added. After the second trigger, there are more empty rows added. but data is missing. If I did something incorrectly, kindly let me know.

[grantjenkins]

Are you able to show the output of your Filter array and your Select?

[Karthik440]

Yes @grantjenkins  i was able to the output of Filter array and your Select. 

Filter Array Table 1

Select Table1

[grantjenkins]

Are you able to show what you put in your Add a row into a table? When you selected the Table from the dropdown it should have shown all the fields that you would need to populate.

[Karthik440]

Those fields were blank.

[grantjenkins · Accepted solution]

You'll need to fill each of them in using the following expressions.

item()?['PRINT GROUP']
item()?['L-PAGES']
item()?['ENVELOPES']
item()?['CHECKS']
item()?['ADVICES']
item()?['MRDF']

And similar for the other table, but using the appropriate field names.

[Karthik440]

@grantjenkins Thank you again; data is imported into both tables and everything is working.

As my inquiries are vying for your attention and valuable time, I hope this will be the final one. I apologise deeply.

I have given the similar input. but my flow is getting failed with below error. 

These changes only apply to the beginning of the Table. Why this is going into error is a mystery to me.

below is the error 

[Karthik440]

Hi @grantjenkins , I hope all is okay with you. in order to add the date from the input to Excel, I need your assistance. To do that, I added a new column to the table. Would you kindly assist me with the expression?

Actually, the information I shared with you contains some sensitive material. So I changed it with "***." when I gave correct input to the flow It is making a mistake. Could you kindly assist me in resolving these two open problems?