Create Array with Key Field Pairing from Txt File

[sboeser]

Hello,

I am trying to create a flow that will disable a user in our ERP once they are terminated within ADP. The disabling part and linking it to the user is fine, its the beginning steps that I am having an issue of.

I will receive a txt file every night at 9PM that contains a list of people who left the organization. The list can contain more than one user as well, the most I have seen so far is up to 5 different employees on the list. The format is the following.

Distinguished Name  :  CN=John Doe, OU=Disabled ON Hold

Enabled  :  True

Given Name    :    John

Name    :    John Don Doe

Object Class    :    User

UPN    :    Jdoe@contoso.com

DistinguishedName   :   CN=Jane Jay Smith, OU=Disabled On Hold

Enabled   :   True

GivenName   :   Jane

Name   :   Jane Jay Smith

ObjectClass   :   user

UPN   :   jsmith@contoso.com

Is there a way that I can map the Name and UPN of multiple users listed within a text file to an Array so that I can separate each user and get the correct dynamic content to plug into the ERP connector to disable potentially multiple user accounts in 1 flow run?

[v-jefferni · Accepted solution]

Hi @sboeser ,

I made a flow to achieve your need, please refer to below screenshots. Note that Compose is the input I used for testing, so you need to replace it with text file content and use in all other expressions.

input:

output:

Detail steps:

split(outputs('Compose'),outputs('Compose_2'))

split(trim(replace(replace(replace(replace(string(item()),'    ',' '),'   ',' '),'  ',' '),' : ','":"')),decodeUriComponent('%0A'))

json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))[0]

schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Distinguished Name": {
                "type": "string"
            },
            "Enabled": {
                "type": "string"
            },
            "Given Name": {
                "type": "string"
            },
            "Name": {
                "type": "string"
            },
            "Object Class": {
                "type": "string"
            },
            "UPN": {
                "type": "string"
            },
            "DistinguishedName": {
                "type": "string"
            },
            "GivenName": {
                "type": "string"
            },
            "ObjectClass": {
                "type": "string"
            }
        },
        "required": [
            "Enabled",
            "Name",
            "UPN"
        ]
    }
}

Best regards,

[royg]

Awesome elaborated response, I was about to suggest breaking to rows by linebreak delimiter and filtering the result array by items that start with "UPN:", these would be the emails of the disabled users that probably can be used for the cancelation process.   

[sboeser]

This is exactly what I needed, I appreciate the incredibly well thought out solution. Thank you!

[sboeser]

Hello again,

I realized I missed a few fields that are included in the text file. Beyond those missing fields, I am inputting the data exactly as listed and I get an error at Select 2. The error I get is "The execution of template action 'Select_2' failed: The evaluation of 'query' action 'where' expression '@json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))[0] ' failed. 'Invalid character after parsing property name. Expected ':' but got: ,. Path '[0].UserPrincipalName', line 1, position 411.'."

I have tried editing the map field to several different iterations but have unable to successfully get the Select 2 action to produce a correct JSON that can be fed into the Parse JSON action. I did notice that its also adding a "r" to the Enabled field, Given Name field, the last name in the Name field, at the end of the Object Class field, and at the end of the UPN field except for the last user.
It looks something along like this.
'[{"DistinguishedName":"CN=John Doe,OU=Disabled On Hold Employees,DC=contoso,DC=comr","Enabled ":"Truer","GivenName":"Johnr","Name ":"John Doer","ObjectClass":"userr","ObjectGUID":"1f111eea-1a1a-1111-111d-11b1111f1a1r","SamAccountName":"jdoerr","SID":"S-1-1-11-111111111-1111111111-1111111111-1111r","Surname ":"John Doerr","UserPrincipalName":"jdoe@contso.comr","r","r","r","r","r",

I believe a combination of the missing fields that I missed including in the original post as well as the extra "r" characters are what is preventing me from feeding this into the Parse JSON action.

Any idea what I could do to fix this? I am out of ideas for iterations for the expression below to try.
json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))[0]

[v-jefferni]

Hi @sboeser ,

Seems on you end it's \r\n instead of \n as the new line. So, in Select, try the expression:

split(trim(replace(replace(replace(replace(string(item()),'    ',' '),'   ',' '),'  ',' '),' : ','":"')),decodeUriComponent('%0D%0A'))

Best regards,

[sboeser]

Thank you again @v-jefferni ! This did solve the issue of removing the extra "r;" characters but I still do get a similar error as previously, on the same line but in a different position.

The error reads "The execution of template action 'Select_2' failed: The evaluation of 'query' action 'where expression '@json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))' failed: 'The template language function 'json' parameter is not valid. The provided value  cannot be parsed: 'Invalid character after parsing property name. Expected ':' but got: ,. Path '[0].UserPrincipalName', line 1, position 468.' "

I edited the select 2 action expression to json(string(outputs('Select')['body'])) and that did work however it failed at the Parse JSON action since that doesn't match with the schema and is expecting an object but instead receives an array. That leads me to believe that the expression I got to work in Select 2 will not be applicable.

What I have currently is set up is expressions the Select action is 

split(trim(replace(replace(replace(replace(string(item()),' ',' '),' ',' '),' ',' '),' : ','":"')),decodeUriComponent('%0D%0A'))

and in Select 2 is 

json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))[0]

[sboeser]

Thank you again @v-jefferni ! This did solve the issue of removing the extra "r;" characters but I still do get a similar error as previously, on the same line but in a different position.

The error reads "The execution of template action 'Select_2' failed: The evaluation of 'query' action 'where expression '@json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))' failed: 'The template language function 'json' parameter is not valid. The provided value  cannot be parsed: 'Invalid character after parsing property name. Expected ':' but got: ,. Path '[0].UserPrincipalName', line 1, position 468.' "

I edited the select 2 action expression to JSON(string(outputs('Select')['body'])) and that did work however it failed at the Parse JSON action since that doesn't match with the schema and is expecting an object but instead receives an array. That leads me to believe that the expression I got to work in Select 2 will not be applicable.

What I have currently is set up is expressions the Select action is 

split(trim(replace(replace(replace(replace(string(item()),' ',' '),' ',' '),' ',' '),' : ','":"')),decodeUriComponent('%0D%0A'))

and in Select 2 is 

json(replace(replace(replace(string(item()),'[','[{'),']','}]'),'\',''))[0]