Power Automate convert a string into "key":"Value" array for sending as HTML email

Question

I am attempting to convert a string that I've set up with "Key":"Value" pairs into an array of objects.

What I have currently:

Date:2024-10-02 time,Time:13:13:45,Desc:SSL VPN login fail,IPAddress:95.164.32.58,User:swf group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in|Date:Date:2024-10-02 time,Time:13:13:31,Desc:SSL VPN login fail,IPAddress:79.110.62.9,User:Brenton group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in|Date:Date:2024-10-02 time,Time:13:11:45,Desc:SSL VPN login fail,IPAddress:45.125.66.55,User:Raquel group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in

That is the string, it has 7 "Key":"Value" pairs that repeat many times (Date, Time, Desc, IPAddress, User, Group, Msg). I split this into an array on "|" and get an array like:

  "Date:2024-10-02 time,Time:13:13:45,Desc:SSL VPN login fail,IPAddress:95.164.32.58,User:swf group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in",
  ",Date:2024-10-02 time,Time:13:13:31,Desc:SSL VPN login fail,IPAddress:79.110.62.9,User:Brenton group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in",
  ",Date:2024-10-02 time,Time:13:11:45,Desc:SSL VPN login fail,IPAddress:45.125.66.55,User:Raquel group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in",
  ",Date:2024-10-02 time,Time:13:11:21,Desc:SSL VPN login fail,IPAddress:79.110.62.8,User:Oliver group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in",

But if I try to put this into another loop, it just keeps running without stopping and never gives me an output.

I've tried replacing all of the "," with "|" and then splitting, but that just makes it a longer array with each "Key":"Value" pair it's own element.

What I am trying to do is to take a bunch of emails from Outlook and pull data out of them, pair that data down to just the 7 parts that I need and then put just that into a table to email back to me so it's possible to evaluate the information. The emails that I'm pulling the data from have anywhere from 10 to 100 lines of data. I only need the 7 parts from each of the lines, so the array would be 7 "Key":"Value" pairs times the 10 to 100 lines in the email. I have everything else working to the point where I've got the above string lined up, I just can't figure out how to get it split into the proper array of objects.

Edit for clarity

This is the expected results that I am trying to get (this is 3 rows worth of data):

[
[
    "Date" : "2024-10-02",
    "Time" : "13:13:45",
    "Desc" : "SSL VPN login fail",
    "IPAddress" : "95.164.32.58",
    "User" : "swf",
    "Group" : "VPN-USERS-Epicor",
    "Msg" : "SSL user failed to logged in"
],
[
    "Date" : "2024-10-02",
    "Time" : "13:13:31",
    "Desc" : "SSL VPN login fail",
    "IPAddress" : "79.110.62.9",
    "User" : "Brenton",
    "Group" : "VPN-USERS-Epicor",
    "Msg" : "SSL user failed to logged in"
],
[   
    "Date" : "2024-10-02",
    "Time" : "13:11:45",
    "Desc" : "SSL VPN login fail",
    "IPAddress" : "45.125.66.55",
    "User" : "Raquel",
    "Group" : "VPN-USERS-Epicor",
    "Msg" : "SSL user failed to logged in"
]
]

This way I can use the Create HTML table to make the results into a table for the email.

Skin · Accepted Answer · 2024-10-02 23:48:44Z

1

Firstly, I'm not able to provide a complete solution given the ambiguity in your question regarding exact output and how you've come to experience your issue has not been detailed.

I'll focus on show you how to split up all of this data so you can isolate fields and values.

Caveat: This solution makes the assumption that all pipes (|) and all commans (,) in all strings represent delimiters for both lines and columns. If that is not true then this solution requires a different approach.

Compose Data

That's your string as you provided ...

Date:2024-10-02 time,Time:13:13:45,Desc:SSL VPN login fail,IPAddress:95.164.32.58,User:swf group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in|Date:Date:2024-10-02 time,Time:13:13:31,Desc:SSL VPN login fail,IPAddress:79.110.62.9,User:Brenton group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in|Date:Date:2024-10-02 time,Time:13:11:45,Desc:SSL VPN login fail,IPAddress:45.125.66.55,User:Raquel group,Group:VPN-USERS-Epicor,Msg:SSL user failed to logged in

Compose Data To Split Into Rows

Expression = split(outputs('Compose_Data'), '|')

For Each Split Item

Expression = outputs('Compose_Data_Split_Into_Rows')

Compose Sub Split Into Column Name And Value

Expression = split(item(), ',')

For Each Sub Split Item

Expression = outputs('Compose_Sub_Split_Into_Column_Name_And_Value')

Compose Field Name And Value

This step split the name of the property for the given column and the value on each row. From here, you need to work out what you want to do with this data factoring in that you need to group all of this data into an object that represents a row.

{
  "Name": @{first(split(item(), ':'))},
  "Value": @{join(skip(split(item(), ':'), 1), '')}
}

answered Oct 2, 2024 at 23:48

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Over a year ago

I am able to get all but that last step working. I cannot figure out how to get those two things into the one compose. What does the expression look like?

Mike Over a year ago

I got it figured out, thanks!

Collectives™ on Stack Overflow

Power Automate convert a string into "key":"Value" array for sending as HTML email

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related