Answered

GETTING ROWS AND COLUMNS FROM JSON | REGEX | MATCHALL

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I extracted this JSON from excel using PowerAutomate :

{"Actual Start Datetime":"Oct 31,2018 10:35:03","Reported Datetime":"Oct 31,2018 10:33:08","Actual Start Date {c}":"31-10-2018"};{"Actual Start Datetime":"Oct 31,2018 10:35:03","Reported Datetime":"Oct 31,2018 10:33:08","Actual Start Date {c}":"31-10-2018"};

BEAUTIFY:
{
"Actual Start Datetime":"Oct 31,2018 10:35:03",
"Reported Datetime":"Oct 31,2018 10:33:08",
"Actual Start Date {c}":"31-10-2018"
};
{
"Actual Start Datetime":"Oct 31,2018 10:35:03",
"Reported Datetime":"Oct 31,2018 10:33:08",
"Actual Start Date {c}":"31-10-2018"
};

I need to catch data using regex and then put it into collection.

To get only, for example, "Actual Start Datetime" I used :

ClearCollect(
 colContent,
 MatchAll(
 varContent,
 """Actual Start Datetime"":""(?<ActualStartDatetime>[^""]*)""" 
 )
);

And it works, but I have troubles getting the rest at the same time, I tried using :

ClearCollect(
 colContent,
 MatchAll(varContent,
 """Actual Start Datetime"":""(?<ActualStartDatetime>[^""]*)""",
 """Reported Datetime"":""(?<ReportedDatetime>[^""]*)""",
 """Actual Start Date {c}"":""(?<ActualStartDatec>[^""]*)"""
 )
);

And it doesn't work , the problem is probably in the way I use "," at the end of expression to separate columns I am not very familiar with regex, APPRICIATE ENY HELP

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

timl 37,306 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

If your aim is to extract all 3 fields, I'd recommend calling the ParseJSON function because it's designed for this task. You'll need to enable ParseJSON in the settings of your app and this is the formula that you would use.

Set(varContent, "{ ""Actual Start Datetime"":""Oct 31,2018 10:35:03"", ""Reported Datetime"":""Oct 31,2018 10:33:08"", ""Actual Start Date {c}"":""31-10-2018"" }; { ""Actual Start Datetime"":""Oct 31,2018 10:35:03"", ""Reported Datetime"":""Oct 31,2018 10:33:08"", ""Actual Start Date {c}"":""31-10-2018"" }" ); ClearCollect(colContent, ForAll( Table(ParseJSON("[" & Substitute(varContent,";",",") & "]")), { ActualStartDateTime:Text(Value.'Actual Start Datetime'), ReportedDateTime:Text(Value.'Actual Start Datetime'), ActualStartDateTimeC:Text(Value.'Actual Start Date {c}') } ) )
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

YES THANK YOU, so in future cases in what situations do I use ParseJson? Or do I always use it when dealing with JSON