Turning Tab Delimited JSON into Array for Table Pasting

[ssbi]

Hello, all. Long time reader, first time poster.

Could I please have some help with the finishing touches on what I have built so far? I can’t get the final steps right.

The big picture: I want to be able to paste content from an excel file into a form, feed it through Powerautomate, and paste it into another excel file. The first file will be raw data from things like sales data, and the latter file will have scripts, formulas, etc, which makes the whole process worthwhile. I am using forms because I want the front end to be as simple as possible for my users.

Said another way, I want to copy excel data which looks like this:

Into Microsoft Forms, and from there Powerautomate takes the Tab Delimited (with line breaks) information and puts it into a format that allows me to paste into a Onedrive file.

What I have so far:

My test form submission looks like this (the step “Compose Faux Array Input”):

{

  "responder": "anonymous",

  "submitDate": "7/27/2023 2:38:51 AM",

  "rb348ff": "Column A\tColumn B\tColumn C\n0.57263906\t0.721963989\t0.233819196\n0.658907399\t0.511149912\t0.139190558\n0.708845808\t0.988536183\t0.74282814\n0.270527158\t0.707212518\t0.709652369\n"

}

Then I parse JSON and set it up as a string variable:

That gets me this far:

Then I split the line breaks and tab breaks:

For the top  function I am doing this: “Compose - Split by Line Break”:

split(variables('Array_JSON_Output'),decodeUriComponent('%0A'))

And then from “Apply to Each”, I am taking the output from “Compose - Split by Line Break”, and using the nested Compose:

split(items('Apply_to_each'),'  ')

Note: that gap is a “tab”.

The results look like:

And so on….

So how do I get this into a format that I can start pasting into a linked excel file? I can’t use “Select” + “Add a Row to a Table” if my Columns are not variables (?).

Other notes:

• The input size will not be regular. Approx 5 to 8 columns, up to 100s or 1000s of rows.
• Column names will change.
• This type of coding is not in my comfort zone, and I do not know enough Javascript. I am more comfortable in Excel and PowerQuery.

Various thoughts:

• Is this approach a dead end?
• Can I do a shortcut using Split + Appending an Array variable + “Create CSV Table”?
• “Join”? String vs Array variables? “Union”? Transpose so I can get into a tidier JSON array? Concatenate/append into a single string at the end? So many things to consider!

Thank you very much in advance. I can answer follow up questions.

[Expiscornovus]

Hi @ssbi,

As far as I am aware \t is a horizontal tab, which should be %09.

Have you tried splitting it with:

decodeUriComponent('%09')

[Paulie78 · Accepted solution]

The approach is not a dead end, but I would use a slightly different approach, I made a quick video with my explanation, see if it helps you:

How to transform data copied from Excel into a JSON array with Power Automate. 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[ssbi]

Thank you, @Paulie78, that is amazing. Thank you for your time and effort, it is an elegant solution, too! I am also excited about your alternative approach to populating long excel tables (https://www.tachytelic.net/2021/08/power-automate-export-to-excel/).

Thank you for the input as well, @Expiscornovus. That makes my process much more thorough.

[moutinhoabreu]

Thanks for tip.

Saved me hours @Expiscornovus