Hi all,
Trying to match all instances of a string in a .txt file with a string in a second array without nested apply to each (because it times out)
I have a form where users can upload a text document and an excel list with a table of values, 1st column is the original string that appears in the text document, second column has the corresponding replacement string.
i'm using get file content using path to grab the text from the text file and then converting it from base64 to string in a compose:
base64ToString(outputs('Get_file_content_using_path')?['body']?['$content'])
output sample:
02290 C¶
02300 IF("XXX0300FZ" .EQ. ON) THEN GOTO 2312¶
02305 C¶
02310 ON("XXX030000")¶
02311 GOTO 2340¶
02312 OFF("XXX030000")¶
02315 C¶
02320 C *** PREHEAT TEMP CONTROL ***¶
02330 C¶
02340 IF("XXX030000" .EQ. PRFON) THEN GOTO 2380¶
02350 SET(2.0,"XXX031400")¶
02360 LOOP(0,"XXX030300","XXX031500",75.0,2500,1,0,1,1.5,1.5,15.0,0)¶
02370 GOTO 2510¶
02380 IF("XXXOA0200" .GE. 65) THEN GOTO 2460¶
02390 IF($LOC12 .EQ. ON) THEN GOTO 2430¶
02400 SET(2.0,"XXX031500")¶
02410 LOOP(0,"XXX030300","XXX031400","XXX0303DA",300,1,300,1,7.0,2.0,14.0,0)¶
02420 GOTO 2510¶
02430 LOOP(0,"XXX030300","XXX031500","XXX0303DA",25,1,500,1,2.0,1.5,15.0,0)¶
02440 SET(2.0,"XXX031400")¶
02450 GOTO 2510¶
02460 SET(15.0,"XXX031400","XXX031500")¶
02470 C¶
02480 C¶
for the replacement string(s) i'm using list rows present in table, then doing a parse JSON and selecting the two values i want.
this gives me an array like this - called point names:
[
{
"Long": "XXX HVAC3.CLG VLV",
"Short": "XXX031300"
},
{
"Long": "XXX HVAC3.DISCH AIR CTRL",
"Short": "XXX0303DA"
},
{
"Long": "XXX HVAC3.DISCH HIGH LIMIT",
"Short": "XXX0303DH"
},
{
"Long": "XXX HVAC3.DISCH HUM",
"Short": "XXX03D900"
},
{
"Long": "XXX HVAC3.DISCH LOW LIMIT",
"Short": "XXX0303DL"
},
{
"Long": "XXX HVAC3.SUPPLY AIR FLOW",
"Short": "XXX034200"
}
]
with i am then appending to an array variable with an apply to each action.
ultimately, the goal is to look like this:
Now i'm stuck on how to actually do the replacement throughout the text document.
Ultimately the goal is to replace the "Short" string with the "Long" string:
02290 C¶
02300 IF("XXX HVAC3.LOW TEMP DETECTION" .EQ. ON) THEN GOTO 2312¶
02305 C¶
02310 ON("XXX HVAC3.SFAN A S/S")¶
02311 GOTO 2340¶
02312 OFF("XXX HVAC3.SFAN A S/S")¶
02315 C¶
02320 C *** PREHEAT TEMP CONTROL ***¶
02330 C¶
02340 IF("XXX HVAC3.SFAN A S/S" .EQ. PRFON) THEN GOTO 2380¶
02350 SET(2.0,"XXX HVAC3.FB DAMPER")¶
02360 LOOP(0,"XXX HVAC3.DISCH TEMP","XXX HVAC3.HTG VLV",75.0,2500,1,0,1,1.5,1.5,15.0,0)¶
02370 GOTO 2510¶
02380 IF("XXX OUTSIDE AIR" .GE. 65) THEN GOTO 2460¶
02390 IF($LOC12 .EQ. ON) THEN GOTO 2430¶
02400 SET(2.0,"XXX HVAC3.HTG VLV")¶
02410 LOOP(0,"XXX HVAC3.DISCH TEMP","XXX HVAC3.FB DAMPER","XXX HVAC3.DISCH AIR CTRL",300,1,300,1,7.0,2.0,14.0,0)¶
02420 GOTO 2510¶
02430 LOOP(0,"XXX HVAC3.DISCH TEMP","XXX HVAC3.HTG VLV","XXX HVAC3.DISCH AIR CTRL",25,1,500,1,2.0,1.5,15.0,0)¶
02440 SET(2.0,"XXX HVAC3.FB DAMPER")¶
02450 GOTO 2510¶
02460 SET(15.0,"XXX HVAC3.FB DAMPER","XXX HVAC3.HTG VLV")¶
02470 C¶
02480 C¶
I originally had a macro to do this, but, the organization that i work for has locked down macros significantly. I wanted to try and execute the script in word using office scripts in power automate, but, it appears that is only a feature in excel.
So far, i've tried an apply to each for the array "point names" and then do a replace on the text of the document, which works fine, but, each iteration only replaces the one value pair and i don't know how to "save" the changes so that as it iterates ALL points in the point array will be replaced into a single document.
I tried putting the text into a sharepoint list and using an update item action, but, that times out with only 140 lines of text and 16 string pairs in the points array., and usually there will be thousands of lines of text and hundreds of points in the points array.
I've also tried putting each line of the text document into an array and updating the item when there is a match using a conditional, which also works, but times out if there is over a few dozen lines.
I also thought of using filter array with apply to each - but, if i use the text array as the input for the apply to each then i don't have a way to iterate through the selected point name, i just end up comparing the entire array output from the select.
Am i missing some kind of obvious fix here (besides just going back to the macro, our IT has been adamant that isn't going to be an option)
is there a better way?
