extract first N of array variable and write to word template

[Derek4]

Hi all,

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

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:

[Pstork1]

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.

[Derek4]

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!

[Pstork1]

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.

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.

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.

[Derek4]

Hi @Pstork1 ,

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

this is how I write the expression:

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

this is how my flow looks like:

[Pstork1]

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.

[Derek4]

@Pstork1 ,

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

[Pstork1]

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?

[Derek4]

@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]

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.