Splitting Multi line text in Form Entry into separate Columns and Rows

[Nobel8]

Hello,

I am trying to create a flow from Microsoft Teams that will take multi line entry from forms and input each into a column and row in a table in Excel.

For example the form entry comes in as such:

Apple, 25, Banana, 32, Orange, 50, Watermelon

Strawberry, 10, Banana, 32, Orange, 50, Watermelon

Grape, 68, Melon, 44, Carrot, 77, Watermelon

I would like the information to be in split up as below:

Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7
Apple	25	Banana	32	Orange	50	Watermelon
Strawberry	10	Banana	32	Orange	50	Watermelon
Grape	68	Melon	44	Carrot	77	Watermelon

There is the potential for more rows that will need to be created from the form pending on input.  I am also open to change the delimiters as necessary.

Currently I have the Flow pulling the information from the form and adding a new row without any issue, but I am stuck on having the cells split.

Also would it be possible to copy the information from other cells that do not need to be split into the cells of the added rows?

I appreciate any assistance that may be given.

[DamoBird365 · Accepted solution]

Hi @Nobel8 

Do you have a form like so and are they simply inserting a csv into the multiline answer?

If so, this would work.  You would need to add a table to an excel file like so:

You would create a compose with a return line (literally as it sounds, insert cursor and hit return/enter key to create new line)

Insert another compose using the split expression on the response, using the return.  This creates an array of lines.

Insert an apply to each for the output of the above compose

Insert an add a row into table (based ont he above example excel file) and for Item1 -> 7 using an expression where each item() is split by a comma and you retrieve object 0-6 (i.e. 1-7)

i.e.

split(item(),',')?[0]

split(item(),',')?[1]

split(item(),',')?[2]

etc..

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

[Nobel8]

Thank you for the response!

My form is almost like that, with exception I have other fields of entry and then towards the end I have a multiline question that will be populated as csv.

Would this then change the body portion in the second compose to point to a specific cell from the Form spreadsheet that is generated?

[DamoBird365]

Hi @Nobel8 

I don't follow you, can you expand on your thinking?

Damien

[Nobel8]

Hi @DamoBird365 

My form has more than one question on it, and the multiline answer is the last section of the form to be filled out

On the second compose I am unsure what the body/ is referencing to.  Is this your example form id?  or is there a way to point this to a the specific multiline answer?

[DamoBird365]

Hi @Nobel8 

output response details etc? That’s just my dynamic answer that is the csv. If you build up your expression and then go back to the dynamic tab, you can insert your question into the expression.

Damien

[Nobel8]

Hi @DamoBird365  

I see now.

I have the compose inputted and split expression as you detailed above.

When I go to test the flow, it will split only the first term in the multianswer and will not insert a new row for the remaining terms.  Any ideas where it went wrong?

[DamoBird365]

Hi @Nobel8 

How many lines in the CSV?  Can you see that many objects in the history output of the flow for the compose?  Did all cells in the excel file complete OK?  How many times did the apply to each run?

Damien

[Nobel8]

Hi @DamoBird365 

Only 3 lines from the CSV, from my example in my first post.

Apple, 25, Banana, 32, Orange, 50, Watermelon

Strawberry, 10, Banana, 32, Orange, 50, Watermelon

Grape, 68, Melon, 44, Carrot, 77, Watermelon

Looking at the history it looks like the apply to each did not run.

[DamoBird365]

Hi @Nobel8 

The Compose looks correct, 3 objects.  The compose is then your input for the apply to each.  I can only guess you've used something different as I cannot see it from your screen shot.

Damien

[Nobel8]

Hi @DamoBird365 

I used the same compose for output with the split expression.

[DamoBird365]

Hi @Nobel8 Could you be using the return line compose as the input to the apply to each? That would explain why it isn’t running as it would be empty.  I’ve built the same and it added the rows as expected.

Damien

[Nobel8]

Hi @DamoBird365 I triple checked and I am using the compose as the input with the split expression.

Can you share a screen shot of your complete test flow?

[DamoBird365]

[Nobel8]

Thank you @DamoBird365  for sharing.

I am going to try rebuilding a new form and excel file again from scratch to see if that fixes my issue as I have the exact same layout as you

[DamoBird365]

Hi @Nobel8 

If it were me, I would delete the apply to each and rebuild that with the compose as input and then instead of add row, add a compose with split(item(),',')?[0]. It should loop 3 times. Then if that works, add the add row back in.

Damien

[Nobel8]

Hi @DamoBird365 The flow is working as it should now.  Thank you for all your help!