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:
Transaction | Part | Name | Name | Date | Qty | Place | Notes |
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 |
How can I store every 8 data to a row in excel with various string length and every row starts with 'Receipt'?
Solved! Go to Solution.
Hi @FH337 :
Please replace "Append to array variable" action with "Add a row into a table" action.For example:
1\My data source
Note:
2\The flow
3\The Result
Best Regards,
Bof
Hi @FH337 :
Please try this flow:
1\My entire flow
Step1:Create there variables
Step2:Do until
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
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.
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++;
}
}
Hi @FH337 :
Please replace "Append to array variable" action with "Add a row into a table" action.For example:
1\My data source
Note:
2\The flow
3\The Result
Best Regards,
Bof
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.
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?
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
74 | |
27 | |
18 | |
16 | |
15 |
User | Count |
---|---|
135 | |
45 | |
35 | |
33 | |
29 |