cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FH337
Regular Visitor

Save extracted Email Data to Excel

I have extracted a specific values from a outlook email and want to put this values in a excel table.

I have the steps until the "clean data" was extracted.

Every row consist of 8 data for example below ( the length of the data can be various )  :

 

Receipt

Harddisk, Samsung, mSATA SSD, MZ-76P256, 256 GB

nameA

nameB

16/11/2020

10

locationA

Notes

Receipt

Optical Drive, Asus, DVD-ROM, SDRW-08D2S-U, -

nameA

nameD

16/11/2020

10

placeB

PR

 

This should produce 2 rows in excel table:

TransactionPartNameNameDateQtyPlaceNotes
ReceiptHarddisk, Samsung, mSATA SSD, MZ-76P256, 256 GBnameAnameB16/11/202010locationAPR
ReceiptOptical Drive, Asus, DVD-ROM, SDRW-08D2S-U, -nameAnameD16/11/202010placeBPR

Capture.JPG

 

How can I store every 8 data to a row in excel with various string length and every row starts with 'Receipt'?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @FH337 :

Please replace "Append to array variable" action with "Add a row into a table" action.For example:

1\My data source

1.JPG

Note:

2\The flow

3.JPG

3\The Result

2.JPG

Best Regards,

Bof

View solution in original post

6 REPLIES 6
v-bofeng-msft
Community Support
Community Support

Hi @FH337 :

Please try this flow:

1\My entire flow

v-bofeng-msft_0-1606806522973.jpeg

 

Step1:Create there variables

v-bofeng-msft_1-1606806522976.png

 

Step2:Do until

v-bofeng-msft_2-1606806522979.png

 

Reference Expression:

1\div(length(Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')), 😎

2\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),8)]

3\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),7)]

4\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),6)]

5\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),5)]

6\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),4)]

7\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),3)]

8\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),2)]

9\Split(uriComponentToString(replace(uriComponent(variables('TheString')), '%0A', '/n')),'/n')[sub(mul(variables('count1'),8),1)]

Step3:Show the result

v-bofeng-msft_3-1606806522980.png

 

7.gif

 

Best Regards,

Bof

It's good to get the value, but i'm confused how to store them into excel worksheet, the input length can be various. My goal is to store the example data, into excel which hopefully produce 2 rows as mentioned above. Thankyou.

sumurthy
Microsoft
Microsoft

Here is a simple way to do this using Office Scripts. It will require the addition of an Office Script to Excel online and calling Run Script action within Excel online flow. 

 

The script that you'll need is this. It expects the table to be there... if you want to create a new table each time, that can be done too with small tweaking. If this sounds interesting, please let me know and I can help along. 

 

What you'll need: 

1. Create a new script. The script you need is below. 

2. run it in Power Automate. You'll need to supply the text data from email as input to the run script action. In the UI, it'll show up as rawData. 

 

For detailed instruction of how to setup the flow, you can checkout this video -- though it is for a different scenario. But the idea is the same. 

https://www.youtube.com/watch?v=152GJyqc-Kw&feature=youtu.be&ab_channel=SudhiMurthy

 

function main(workbook: ExcelScript.Workbook, rawData: string) {
  let table = workbook.getTable('Table2');

/* Comment this section before running in Power Automate */
/*
  rawData = `Receipt
Harddisk, Samsung, mSATA SSD, MZ-76P256, 256 GB
nameA
nameB
16/11/2020
10
locationA
PR
Receipt
Optical Drive, Asus, DVD-ROM, SDRW-08D2S-U, -
nameA
nameD
16/11/2020
10
placeB
PR`;
*/
/* End comment */

  const rows: string[] = rawData.split('\n');

  let row: string[] = [];
  let colCount = 1;
  for (let i=0; i<rows.length; i++) {
    row.push(rows[i]);
    if (colCount % 8 === 0) {
      table.addRow(-1, row)
      row = [];
    }
    colCount++;
  }
}

 

sumurthy_0-1606860314590.png

 

 

Hi @FH337 :

Please replace "Append to array variable" action with "Add a row into a table" action.For example:

1\My data source

1.JPG

Note:

2\The flow

3.JPG

3\The Result

2.JPG

Best Regards,

Bof

View solution in original post

FH337
Regular Visitor

So the rawData variable value is taken automatically from Flow process, then processed further inside script, right?

FYI the raw data can change based on email sent by user. So i hope this script receive dynamic value( can produce dynamic rows). But the col count is always 8.

sumurthy
Microsoft
Microsoft

Yes, you'll see that I commented out the lines that I was using for testing. What you'll pass into the Run Script action is the text input from the email read action. You can feed that data dynamically and script will split that into many rows. Please let me know if you need complete sample screenshot with the flow 

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (36,332)