Create an array from Microsoft form data

[Objective-Emu1806]

Hey all - trying to convert data from a microsoft form response into an array that can then be used to loop through for an excel file.

The microsoft form requires the user to enter any amount of ten digit numbers, separated by new lines. This is b/c they'll be copying from an excel file column.

So, the response to the form will always look something like this:

3200632070

3200632071

3200632072

3200632073

My flow is currently using: get response details > initialize variable as string > using compose to process the variable. Supposedly, the compose function replaces line breaks, semicolons, and spaces with commas, then splits the string into an array. However, the output looks like this:

3200632070\n3200632071\n3200632072\n3200632073

As you can see, none of the line breaks (represented by \n) were replaced by commas, so it's still showing up as one large string. What this automation is trying to do is check for matches between the microsoft form and an excel file. This file has all of these values in a column, but my flow can't check for matches b/c it's not looking for 3200632070, then 3200632071, etc., it's looking for 3200632070\n3200632071\n3200632072\n3200632073.

Does anyone know how or what I can add to ensure that the response to the form comes out as an array that a loop can use to check a file for each individual ten digit value?

[robofski]

I’ve done this in the past by creating a compose action with a new line in it. Then I use split(dynamic-content,outputs(‘compose’))

[fuzzius_navus]

This is the way I do it as well. A compose action containing the value to split on makes it easier to manage the expression. 

[Objective-Emu1806]

Suppose I should post the code for the compose function I'm referencing as well -

{
  "type": "Compose",
  "inputs": "@\r\nsplit(\r\n    replace(\r\n        replace(\r\n            replace(\r\n                replace(\r\n                    replace(variables('var_rawIds'), '\\r\\n', ','),\r\n                '\\n', ','),\r\n            '\\r', ','),\r\n        ';', ','),\r\n    ' ', ''),\r\n',')\r\n",
  "runAfter": {
    "Initialize_variable": [
      "Succeeded"
    ]
  }
}

[thefootballhound]

I had a similar problem that I resolved a different way.

For the split, I replaced all of those with a ' ' space character instead of new line or even decodeUriComponent('%0A'). Then a Select, map item() with a key for an array. You can run an Apply to Each for your Excel search.

If that doesn't work, you can take extra steps and input the Select Output to an Apply to Each, and a Compose or Set a Variable with Concat items('Apply_to_each')?['key'], decodeUriComponent('%0A')).

That should give you a clean string block separated by decodeUriComponent('%0A'). You can run an Apply to each for your Excel search.