Macro for find and replace in main file

dcaron24 · 23:37 - Apr 25#12023-04-25T23:37

I am trying to create a macro that translates and formats some consolidated data (next window) into my main file in the previous window. I want to replace a handful of data only (particularly, the people-hg-lat, people-hg-sens, etc. detailed below. Right now, I am pulling all the data I need from an Excel file, and it can be edited to be in a different format for tagging purposes, but my current macro does not seem to have issues with that. It can successfully select the entire line's worth of data (using the tags for replacement) up to the end of the copied/pasted line. After that, it seems to have issues selecting multiple lines and finding the right ones to replace. I could use some help.

My main file looks like this before running my macro:

Code: Select all

"My Room 1" = SPACE           
   INF-METHOD       = AIR-CHANGE
   INF-FLOW/AREA    = 0.0147816
   PEOPLE-HG-LAT    = 230.877
   PEOPLE-HG-SENS   = 251.034
   EQUIP-LATENT     = ( 0 )
   EQUIP-SENSIBLE   = ( 1 )
   LIGHTING-W/AREA  = ( 1.71887 )
   EQUIPMENT-W/AREA = ( 0.41 )
   AREA/PERSON      = 194.063
   POLYGON          = "EL1 Space Polygon 1"
   ..

"My Room 2" = SPACE           
   INF-METHOD       = AIR-CHANGE
   INF-FLOW/AREA    = 0.001
   PEOPLE-HG-LAT    = 223.297
   PEOPLE-HG-SENS   = 251.443
   EQUIP-LATENT     = ( 0 )
   EQUIP-SENSIBLE   = ( 1 )
   LIGHTING-W/AREA  = ( 1.41073 )
   EQUIPMENT-W/AREA = ( 0.336 )
   AREA/PERSON      = 308.618
   POLYGON          = "EL1 Space Polygon 2"
   ..

My next window data that I am trying to replace:

Code: Select all

"My Room 1"AREA/PERSON = 100PEOPLE-HG-SENS =275PEOPLE-HG-LAT =275EQUIPMENT-W/AREA =1LIGHTING-W/AREA ={#SI(#PA(/“Base Ltg Flag/”)*0.558 + #PA(“Proposed Ltg Flag”)*0.558)}
"My Room 2"AREA/PERSON = 200PEOPLE-HG-SENS =275PEOPLE-HG-LAT =475EQUIPMENT-W/AREA =1LIGHTING-W/AREA ={#SI(#PA(/“Base Ltg Flag/”)*0.56 + #PA(“Proposed Ltg Flag”)*0.56)}

This is what I would like the file to look like after my macro:

Code: Select all

"My Room 1" = SPACE           
   INF-METHOD       = AIR-CHANGE
   INF-FLOW/AREA    = 0.0147816
   PEOPLE-HG-LAT    = 275
   PEOPLE-HG-SENS   = 275
   EQUIP-LATENT     = ( 0 )
   EQUIP-SENSIBLE   = ( 1 )
   LIGHTING-W/AREA ={#SI(#PA(/“Base Ltg Flag/”)*0.558 + #PA(“Proposed Ltg Flag”)*0.558)
   EQUIPMENT-W/AREA = 1
   AREA/PERSON      = 100
   POLYGON          = "EL1 Space Polygon 1"
   ..

"My Room 2" = SPACE           
   INF-METHOD       = AIR-CHANGE
   INF-FLOW/AREA    = 0.001
   PEOPLE-HG-LAT    = 275
   PEOPLE-HG-SENS   = 275
   EQUIP-LATENT     = ( 0 )
   EQUIP-SENSIBLE   = ( 1 )
   LIGHTING-W/AREA  = LIGHTING-W/AREA ={#SI(#PA(/“Base Ltg Flag/”)*0.56 + #PA(“Proposed Ltg Flag”)*0.56)
   EQUIPMENT-W/AREA = 1
   AREA/PERSON      = 200
   POLYGON          = "EL1 Space Polygon 2"
   ..

What I have so far:

Code: Select all

InsertMode
ColumnModeOff
HexOff
UltraEditReOn
Loop 0
Find RegExp "\"*\" = SPACE"
Find Select "\"*\""
Copy
NextWindow
Find "^c"
SelectLine
Copy
PreviousWindow
Key RIGHT ARROW
"
"
Paste
Key UP ARROW
Find RegExp "\"*\"^(AREA/PERSON =*^)^(PEOPLE-HG-SENS =*^)^(PEOPLE-HG-LAT =*^)^(EQUIPMENT-W/AREA =*^)^(LIGHTING-W/AREA =*$^)^([^p]++^) PEOPLE-HG-LAT = *$^nPEOPLE-HG-SENS = *$^([^n^r^t*$]++^)LIGHTING-W/AREA = *$^([^p]++^) EQUIPMENT-W/AREA = *$^([^n^r^t*$]++^)AREA/PERSON = *$"
Replace SelectText “^6 ^3 ^2 ^7 ^5 ^8 ^4 ^9 ^1”
EndLoop

It works until end of \"*\"^(AREA/PERSON =*^)^(PEOPLE-HG-SENS =*^)^(PEOPLE-HG-LAT =*^)^(EQUIPMENT-W/AREA =*^)^(LIGHTING-W/AREA =*$^), then it does not continue selecting anything. UltraEdit also hates my replace command, these are just my notes.

Mofi · 6:33 - Apr 26#22023-04-26T06:33

It is many years ago that I wrote an UltraEdit macro which must process data from two files and would need variables not supported by UltraEdit macros. I write usually an UltraEdit script for such a task. However, it is possible to use an UltraEdit macro using the UltraEdit regular expression engine for this task although being very inefficient on execution as lots of modifications and window updates are done on two opened files.

Code: Select all

InsertMode
ColumnModeOff
HexOff
UltraEditReOn
Clipboard 9
NextWindow
Bottom
IfColNumGt 1
InsertLine
EndIf
Top
Find MatchCase RegExp "[^t |]++AREA/PERSON[^t ]++=[^t ]++"
Replace All "|AREA/PERSON="
Find MatchCase RegExp "[^t |]++PEOPLE-HG-SENS[^t ]++=[^t ]++"
Replace All "|PEOPLE-HG-SENS="
Find MatchCase RegExp "[^t |]++PEOPLE-HG-LAT[^t ]++=[^t ]++"
Replace All "|PEOPLE-HG-LAT="
Find MatchCase RegExp "[^t |]++EQUIPMENT-W/AREA[^t ]++=[^t ]++"
Replace All "|EQUIPMENT-W/AREA="
Find MatchCase RegExp "[^t |]++LIGHTING-W/AREA[^t ]++=[^t ]++"
Replace All "|LIGHTING-W/AREA="
Find MatchCase RegExp "|++$"
Replace All "|"
PreviousWindow
Top
Loop 0
Find MatchCase RegExp "\"*\" = SPACE"
IfNotFound
ExitLoop
EndIf
GotoLine 0 1
Find MatchCase RegExp "\"*\""
Copy
NextWindow
Find "^c"
IfFound
SelectLine
Copy
PreviousWindow
GotoLine 0 1
Paste
Key UP ARROW
Find MatchCase RegExp "\"*\""
Replace ""
ToggleBookmark
Find MatchCase "|AREA/PERSON"
IfFound
Key RIGHT ARROW
Find MatchCase RegExp "[~|]+"
Copy
Find MatchCase RegExp "^(AREA/PERSON[^t ]++=[^t ]++^)*$"
Replace "^1^c"
EndIf
GotoBookMark -1
Find MatchCase "|PEOPLE-HG-SENS"
IfFound
Key RIGHT ARROW
Find MatchCase RegExp "[~|]+"
Copy
Find MatchCase RegExp "^(PEOPLE-HG-SENS[^t ]++=[^t ]++^)*$"
Replace "^1^c"
EndIf
GotoBookMark -1
Find MatchCase "|PEOPLE-HG-LAT"
IfFound
Key RIGHT ARROW
Find MatchCase RegExp "[~|]+"
Copy
Find MatchCase RegExp "^(PEOPLE-HG-LAT[^t ]++=[^t ]++^)*$"
Replace "^1^c"
EndIf
GotoBookMark -1
Find MatchCase "|EQUIPMENT-W/AREA"
IfFound
Key RIGHT ARROW
Find MatchCase RegExp "[~|]+"
Copy
Find MatchCase RegExp "^(EQUIPMENT-W/AREA[^t ]++=[^t ]++^)*$"
Replace "^1^c"
EndIf
GotoBookMark -1
Find MatchCase "|LIGHTING-W/AREA"
IfFound
Key RIGHT ARROW
Find MatchCase RegExp "[~|]+"
Copy
Find MatchCase RegExp "^(LIGHTING-W/AREA[^t ]++=[^t ]++^)*$"
Replace "^1^c"
EndIf
GotoBookMark -1
ToggleBookmark
DeleteLine
Key DOWN ARROW
Else
PreviousWindow
Key END
EndIf
EndLoop
ClearClipboard
Clipboard 0
Top

UltraEdit does not support Microsoft Excel files which are binary files with file extension .xls or .xlsx. I suppose the files are a kind of CSV files (comma-separated value files) which are just opened on your PC by default in Microsoft Excel because of the file extension .csv is associated with Excel.exe. That does not make a CSV file an Excel file.

The data file in second document window misses the separators according to the two posted lines. The macro above first changes the file to a CSV file using a vertical bar as separator before running the replaces in main file which of course must be the active file on starting macro execution. There is appended at each line in the data file a vertical bar at end for easier processing the values. A real CSV file with using a vertical bar as separator would not have a vertical bar at end of each line.

I added some extra conditions to make sure that the macro works even on making some changes in the future on the two files to process.

User clipboard 9 instead of the Windows clipboard is used as variable value storage.

Please note that the current string in user clipboard 9 is interpreted as UltraEdit regular expression string on using ^c in search or replace expression. The macro works for the provided data but could produce a wrong result on a room name containing characters interpreted as UltraEdit regular expression characters in a search expression. It would produce also a wrong result if a value from separate data file would contain a character which is interpreted as UltraEdit regular expression character in a replace expression.

The result produced by the macro for the provided input data does not match with the provided expected output. That is not the fault of the macro. The provided expected output does not match with provided data in the separate data file. The produced output is:

Code: Select all

"My Room 1" = SPACE           
   INF-METHOD       = AIR-CHANGE
   INF-FLOW/AREA    = 0.0147816
   PEOPLE-HG-LAT    = 275
   PEOPLE-HG-SENS   = 275
   EQUIP-LATENT     = ( 0 )
   EQUIP-SENSIBLE   = ( 1 )
   LIGHTING-W/AREA  = {#SI(#PA(/“Base Ltg Flag/”)*0.558 + #PA(“Proposed Ltg Flag”)*0.558)}
   EQUIPMENT-W/AREA = 1
   AREA/PERSON      = 100
   POLYGON          = "EL1 Space Polygon 1"
   ..

"My Room 2" = SPACE           
   INF-METHOD       = AIR-CHANGE
   INF-FLOW/AREA    = 0.001
   PEOPLE-HG-LAT    = 475
   PEOPLE-HG-SENS   = 275
   EQUIP-LATENT     = ( 0 )
   EQUIP-SENSIBLE   = ( 1 )
   LIGHTING-W/AREA  = {#SI(#PA(/“Base Ltg Flag/”)*0.56 + #PA(“Proposed Ltg Flag”)*0.56)}
   EQUIPMENT-W/AREA = 1
   AREA/PERSON      = 200
   POLYGON          = "EL1 Space Polygon 2"
   ..