cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Derek4
Helper III
Helper III

extract first N of array variable and write to word template

Hi all,

 

I need to extract excel content and write to word template, I use an array variable to store all the excel content:

微信截图_20210412205736.png

 

Let's say there are total 50 set of data, How can I get the first 10 set to a word template, then the second 10 set to another word template, and so on...

 

I can't even find the array variable in the dynamic content:

 

微信截图_20210412210210.png

9 REPLIES 9
Pstork1
Dual Super User III
Dual Super User III

If you create an integer variable as a counter you can use a Do Until loop to access each value by index and increment the counter.  exit the loop when you reach the number you want to process.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

I did as you suggested, but I don't know how to "access each value by index", can you please share a screenshot or a formula that I can refer to?

 

Thank you!

 

微信截图_20210413085332.png

 

 

 

 

Pstork1
Dual Super User III
Dual Super User III

Here's an example.  I created an array with 3 nested arrays.  Then created a variable to hold the loop count and an array variable to build the target array in.

image.png

Then its just a DoUntil loop that appends the values as objects into the target array using JSON notation and indexing.  I'll show you that formula in a minute.  Then increment the counter variable.  The Do Until loop will loop until the count is equaled or exceeded.

image.png

The JSON for the values is typed into the Expressions tab.  It looks like this.

outputs('Car_Array')[variables('LoopCount')][0]

Outputs('Car_Array') is the name of the array.

the first index in square brackets is variables{'LoopCount') Thats the index of the row being processed.

The second index in square brackets is [0].  Which is the first field in the row of the first record, etc.

Its essentially the same notation as Array[0,0] in regular programming.  Its just in JSON.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

I tried your way, but it gives me an error:

微信截图_20210413115538.png

 

this is how I write the expression:

微信截图_20210413120005.png

 

outputs('varLabel')[variables('varCount')][6]

 

this is how my flow looks like:

微信截图_20210413115157.png

Pstork1
Dual Super User III
Dual Super User III

You only use the second square bracket if the record is an embedded array.  Since you are reading from Excel I assume you are reading rows of records. In that case the second square bracket would be the name of the field, not a number.  You also can't use the same varCount for two different loops because the value will already be set by the time you get to the second loop.  Can you provide a copy of one of your records that you are trying to read?  Without seeing your data I can't be more specific about what the JSON would look like.  For example, in your case the array is coming from a List Rows action in my case the array is coming from a Compose statement.  The pattern will be the same but the details will be different.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@Pstork1 ,

 

Thanks for your reply, this is how my Excel looks like:

微信截图_20210413203215.png

Pstork1
Dual Super User III
Dual Super User III

I wanted to see the JSON representation, not the actual file.  Can you just screenshot the output of the List Rows action output from a run?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@Pstork1 ,

 

it's like this:

{"statusCode":200,"headers":{"Pragma":"no-cache","Vary":"Accept-Encoding","x-ms-request-id":"7b31cc6c-e032-4b46-9812-0bf7f9c7c7c1;d6cccfc3-bc95-45c7-91ca-17254dc269e4;b1c439b5-ae1d-4d0d-a972-876d74c1c073;959064b9-3ec6-4927-ae5f-019590970d52","OData-Version":"4.0","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","Cache-Control":"no-store, no-cache","Date":"Mon, 12 Apr 2021 05:29:31 GMT","Content-Type":"application/json; odata.metadata=minimal; odata.streaming=true","Expires":"-1","Content-Length":"8295"},"body":{"@odata.context":"https://excelonline-eus2.azconn-eus2.p.azurewebsites.net/$metadata#drives('b%21Uqv4scLWhUCxTIXFq0dDNZTolsKe0WVPrF9cUwGbf7_l5s4dKmvcSI4CrdisPFxQ')/Files('01DMAUJE44RBUGB4JAARFKJNMZD3CVHQQA')/Tables('%7BD676BF65-08EC-4B04-8421-F077E9C3CDE1%7D')/items","value":[{"@odata.etag":"","ItemInternalId":"ad5ef697-7c37-422b-a534-5c229b307a2f","EAM NO":"90120010082","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123456","WORK ORDER_x0023_":"123456","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"09e0648d-360c-47bf-9229-d729d82c09e6","EAM NO":"90120010083","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"ABC","PURCHASE ORDER_x0023_":"900123457","WORK ORDER_x0023_":"123457","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"3d5200ba-6683-47e7-b49c-9c62a532720a","EAM NO":"90120010084","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"DEF","PURCHASE ORDER_x0023_":"900123458","WORK ORDER_x0023_":"123458","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"3aad09ae-dd6b-4033-bfc4-a841813975fe","EAM NO":"90120010085","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"GHI","PURCHASE ORDER_x0023_":"900123459","WORK ORDER_x0023_":"123459","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"e1b23fb4-f676-449b-94c6-c38faf675344","EAM NO":"90120010086","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123460","WORK ORDER_x0023_":"123460","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"9401dc14-2b4e-48c5-8f06-a224f00e536e","EAM NO":"90120010087","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"ABC","PURCHASE ORDER_x0023_":"900123461","WORK ORDER_x0023_":"123461","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"702a0327-4ff3-4a2b-9f71-4ecc8b43c2ac","EAM NO":"90120010088","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"DEF","PURCHASE ORDER_x0023_":"900123462","WORK ORDER_x0023_":"123462","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"9d88c328-2e94-4d6b-b0a4-793f3e864ca0","EAM NO":"90120010089","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"GHI","PURCHASE ORDER_x0023_":"900123463","WORK ORDER_x0023_":"123463","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"d54d6259-893e-4c73-825f-1b46d89b3f9a","EAM NO":"90120010090","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123464","WORK ORDER_x0023_":"123464","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"b09682da-074a-4d0e-929b-5751fbf49ccc","EAM NO":"90120010091","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"ABC","PURCHASE ORDER_x0023_":"900123465","WORK ORDER_x0023_":"123465","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"e48e6e23-8366-4095-9e73-8a3b8b5828bc","EAM NO":"90120010092","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"DEF","PURCHASE ORDER_x0023_":"900123466","WORK ORDER_x0023_":"123466","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"218aa60c-d74c-4c7b-a52b-1c6d3f7e7662","EAM NO":"90120010093","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"GHI","PURCHASE ORDER_x0023_":"900123467","WORK ORDER_x0023_":"123467","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"39d7d00b-9d91-447f-967b-a6e92c1cfc04","EAM NO":"90120010094","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123468","WORK ORDER_x0023_":"123468","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"294298e4-a92b-4bd1-b53e-58082aac5e61","EAM NO":"90120010095","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"ABC","PURCHASE ORDER_x0023_":"900123469","WORK ORDER_x0023_":"123469","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"52d2be84-fc23-4c21-9d0a-3da72da89b03","EAM NO":"90120010096","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"DEF","PURCHASE ORDER_x0023_":"900123470","WORK ORDER_x0023_":"123470","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"d811410c-a360-44fb-a255-20dc115f32d0","EAM NO":"90120010097","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"GHI","PURCHASE ORDER_x0023_":"900123471","WORK ORDER_x0023_":"123471","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"d6e6b053-f060-406e-bba8-f6f3a7cc37e9","EAM NO":"90120010098","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123472","WORK ORDER_x0023_":"123472","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"8928ab4e-e291-434f-bb3d-98701cb92905","EAM NO":"90120010099","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"ABC","PURCHASE ORDER_x0023_":"900123473","WORK ORDER_x0023_":"123473","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"437d9502-9712-46c7-b514-fac436cc6be6","EAM NO":"90120010100","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"DEF","PURCHASE ORDER_x0023_":"900123474","WORK ORDER_x0023_":"123474","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"0251b6dd-ac06-4d28-9827-d0a182297d18","EAM NO":"90120010101","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"GHI","PURCHASE ORDER_x0023_":"900123475","WORK ORDER_x0023_":"123475","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"59aba1b5-198b-4b9b-9c9f-4760bafa73e2","EAM NO":"90120010102","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123476","WORK ORDER_x0023_":"123476","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"4f7389bc-1ac1-48ac-8d8d-7cc92718fffd","EAM NO":"90120010103","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"ABC","PURCHASE ORDER_x0023_":"900123477","WORK ORDER_x0023_":"123477","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"5c12b9dc-7ded-4ff8-86df-e0d15cd8329f","EAM NO":"90120010104","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"DEF","PURCHASE ORDER_x0023_":"900123478","WORK ORDER_x0023_":"123478","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"50856b28-006c-49c1-bb7c-74940dc140ba","EAM NO":"90120010105","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"GHI","PURCHASE ORDER_x0023_":"900123479","WORK ORDER_x0023_":"123479","LOCATION":"SACKROOM"},{"@odata.etag":"","ItemInternalId":"c6cf9657-961c-4f96-9c90-b0c4141ccb08","EAM NO":"90120010106","DESCRIPTION":"Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m","MFR P/N_x0023_ ":"EVO5LH-1500-1.5mtr","MFR NAME":"UNK","PURCHASE ORDER_x0023_":"900123480","WORK ORDER_x0023_":"123480","LOCATION":"SACKROOM"}]}}
Pstork1
Dual Super User III
Dual Super User III

So an individual row looks like this.

 {
                "@odata.etag": "",
                "ItemInternalId": "ad5ef697-7c37-422b-a534-5c229b307a2f",
                "EAM NO": "90120010082",
                "DESCRIPTION": "Lever Type Hoist, PROLH Model, SWL 1500 KG, 1.5m",
                "MFR P/N_x0023_ ": "EVO5LH-1500-1.5mtr",
                "MFR NAME": "UNK",
                "PURCHASE ORDER_x0023_": "900123456",
                "WORK ORDER_x0023_": "123456",
                "LOCATION": "SACKROOM"
            },

 

That means the code would look like this for Description.

outputs('Car_Array')[variables('LoopCount')]?['Description']

Of course your array isn't Outputs('Car_Array').  But that is the pattern.  Other fields would just be different fieldnames.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

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

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (55,039)