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

columns string variable from excel to populate word document

HI

i have an excel spreadsheet with two columns i want to work with (out of about 11)

lexwykes_0-1632211032141.png  and column 11 which has some string information related to column 3

I have created a word doc with content controls which i want to populate with the respective string value from column 11 related to column 3.  So for the document i want to have a number of areas that i add in for 1.1, 1.2 and when i create the new file from that word template i want the corresponding string from the same row pumped into the document.

i have tried initialising an array, a json and just the variables....and then looping through but i can't seem to get anything to go into the document unless i read each line individually to create the variable, there are 86 rows....so it will be messy.

any thoughts?

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @lexwykes 

 

I've tried to explain a better method above, using list rows - rather than get row.

 

If you use list rows, you can repurpose the data as an array, flatten it (using a compose action) and then query each column3 by using an expression.

 

DamoBird365_0-1632223311402.png

 

The flatten array expression is json(replace(join(body('Select'), ','), '},{', ','))

 

Note that the expression in the last compose could be used directly in each of your inputs for the Word Template.

first(outputs('Compose_-_flatten_array'))?['1.1']

first(outputs('Compose_-_flatten_array'))?['1.2']

first(outputs('Compose_-_flatten_array'))?['1.3'] etc....

 

Here's a video showing how to rebuild/repurpose an array https://youtu.be/cCnngTanFOM or for a beginners guide, try https://youtu.be/6nJSUNh579w

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

View solution in original post

5 REPLIES 5
DamoBird365
Super User
Super User

Hi @lexwykes 

 

Are you using list rows present in a table?  After this, I would insert a select action and set the key to be column3 and the value to be column11.  Then you will have a new array with "column3":"column11" which you can then call from within your populate word doc action 

body('Select')?['1.1']
body('Select')?['1.2']
etc....
 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Currently i have a working solution but i have only added 6 rows and there are 80 rows

lexwykes_0-1632221465170.png

As i said it works as it uses column 3 as its control and then i can use the get row 6 say to grab the appropriate column 11 variable......however i don't want to have to do this for 80 rows if at all possible.

Is there a way around this, can i use an expression in the create a word template for instance, if (column3=2.1 then use column 11 variable) kind of thing, or can that not be hard coded.  I am truly stuck and may end up having to add 80 get rows

 

Hi @lexwykes 

 

I've tried to explain a better method above, using list rows - rather than get row.

 

If you use list rows, you can repurpose the data as an array, flatten it (using a compose action) and then query each column3 by using an expression.

 

DamoBird365_0-1632223311402.png

 

The flatten array expression is json(replace(join(body('Select'), ','), '},{', ','))

 

Note that the expression in the last compose could be used directly in each of your inputs for the Word Template.

first(outputs('Compose_-_flatten_array'))?['1.1']

first(outputs('Compose_-_flatten_array'))?['1.2']

first(outputs('Compose_-_flatten_array'))?['1.3'] etc....

 

Here's a video showing how to rebuild/repurpose an array https://youtu.be/cCnngTanFOM or for a beginners guide, try https://youtu.be/6nJSUNh579w

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

View solution in original post

Ah great, i did manage to do list and select with the mapping before but let me check through the rest....i seemed to get so far with the json side of things and then couldn't grab the data properly.....let me give it a shot.  thanks for your help.

genius thanks very much.

Had an error but on going through your video i realised that the [ ] brackets are required around the json, so not only go the answer but learned even more.....

you have saved me an awful lot of work.

Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,628)