[SOLVED] FFAStrans CommandExecuter with RegEx in Powershell

[ThomasM]

Hi to all,

this may be a little Off-Topic, but...

I have to re-arrange a bunch of JSON-formatted Files. The wrong .JSON-Files begin with

Code: Select all

{
    "Inventarnummer": "INV20911",
    "Timecode": "noTC",
    "Seite": "0",
    "Digitalisierungsergebnis": "AUDIOVISUELLE_DIGITALISIERUNG",
    "Dateien" :
    [
            {
                "Dateiname": "INV20911.7z",
                "Typ": "ARCHIVDIGITALISAT",
                "XXHash": "95def85c"
            },
            {
                "Dateiname": "a4871611-f849-43aa-91c5-84a0270979e6.JSON",
                "Typ": "ARCHIVDIGITALISAT-ANALYSEDATEI",
                "XXHash": "555e4011"
            },

The Corrected .JSON has to look like this (again, only the beginning of the file, notice the correct file-extension in line 6):

Code: Select all

{
    "DigitalisierungsprozessID": "11111111-76cc-4cc2-aafb-7bac6b0d9281",
    "Digitalisierungsergebnis": "AUDIOVISUELLE_DIGITALISIERUNG",
    "Dateien" :
    [
            {
                "Dateiname": "11111111-76cc-4cc2-aafb-7bac6b0d9281.7z",
                "Typ": "ARCHIVDIGITALISAT",
                "XXHash": "95def85c"
            },
            {
                "Dateiname": "a4871611-f849-43aa-91c5-84a0270979e6.JSON",
                "Typ": "ARCHIVDIGITALISAT-ANALYSEDATEI",
                "XXHash": "555e4011"
            },

So the changes are the first few lines and the Filename *.7z.

The Problem: in Command-Executor this code works in a way that it replaces both INV*-Values but kills the file-extension ".7z" in line 6

Code: Select all

%comspec% /C "
POWERSHELL -Command (gc "%s_original_path%\archiv-import-packliste3.json")
-replace 'INV.*', '%s_original_path~1%%s_jsonquoteandcomma%' |
Out-File -encoding ASCII "%s_original_path%\archiv-import-packliste4.json"
"

Outcome is (notice the missing .7z-Extension in line 6):

Code: Select all

{
    "DigitalisierungsprozessID": "11111111-76cc-4cc2-aafb-7bac6b0d9281",
    "Digitalisierungsergebnis": "AUDIOVISUELLE_DIGITALISIERUNG",
    "Dateien" :
    [
            {
                "Dateiname": "11111111-76cc-4cc2-aafb-7bac6b0d9281",
                "Typ": "ARCHIVDIGITALISAT",
                "XXHash": "95def85c"
            },
            {
                "Dateiname": "a4871611-f849-43aa-91c5-84a0270979e6.JSON",
                "Typ": "ARCHIVDIGITALISAT-ANALYSEDATEI",
                "XXHash": "555e4011"
            },

I had quite some trouble getting the Powershell-RegEx running. Even the escape-Characters seems to be problematic. I tried a work-around with PopulateVariables-Node to get the escape-Character working on the Powershell-Expression above. So the

• %s_jsonquoteandcomma%

- Variable is

• \",

I tried to escape the input of the Replace-command but no success. Even with a Variable

• INV.*\.7z

or in brackets and so on.

Code: Select all

%comspec% /C "
POWERSHELL -Command (gc ""%s_original_path%\archiv-import-packliste2.json"")
-replace 'INV.*7z', '"%s_original_path~1%.7z"' | Out-File -encoding ASCII "%s_original_path%\archiv-import-packliste3.json"
"

the workflow is also attached, so maybe someone is able to get me back on track...?

Thanks in advance,
Thomas

[emcodem]

Hi Thomas,

always happy to hear from you!

the cmd /c stuff is not always needed, it also makes quoting sometimes a little more complex.
Also, you have newlines in the command, that makes it a little bit harder to debug because i usually just copy the code of the commandline processor, replace the variables by some real values and paste it to commandline for testing.

This works for me, on commandline and in a cmd processor:

Code: Select all

POWERSHELL -Command "(gc "C:\aaf\orig.json") -replace 'INV.*\.7z', '_REPLACED_.7z' | Out-File -encoding ASCII "C:\aaf\neu.json""

So in theory this exact code shoule work in your cmd proc:

Code: Select all

POWERSHELL -Command "(gc "%s_original_path%\archiv-import-packliste2.json") -replace 'INV.*\.7z', '%s_original_path~1%.7z' | Out-File -encoding ASCII "%s_original_path%\archiv-import-packliste3.json""

[ThomasM]

@emcodem

Harry, again: thanks a lot for your help! Great - it works. Now I can start to ease the workflow a bit.

Interesting thing, that

Code: Select all

"%comspec% /c"

is not needed. I was not aware of this and keep it for the other DOS-(CMD-)commands. All other Powershell-nodes now work great without that!

All the best

thomas

[emcodem]

Hehe honestly i am not sure when exactly the cmd /c stuff is needed, i believe it would make sure that certain envirnoment variables are set.
What i usually do is to try without cmd /c and only if it dont work, i add it.