columns string variable from excel to populate word document

(0) Share

Report

Posted on by lexwykes

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

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?

All responses (5)

Answers (1)

lexwykes 14 on at

Like (1)

Report

Re: columns string variable from excel to populate word document

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
lexwykes 14 on at

Like (0)

Report

Re: columns string variable from excel to populate word document

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.
Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Re: columns string variable from excel to populate word document

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.

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 😉
lexwykes 14 on at

Like (0)

Report

Re: columns string variable from excel to populate word document

Currently i have a working solution but i have only added 6 rows and there are 80 rows
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
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Re: columns string variable from excel to populate word document

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 😉