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

7 REPLIES 7
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

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 

Can you please expand one of the split functions you have for the fields in order for me to see the logic underneath?

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (2,832)