PowerShell temp script removes special charcters

[Sascha Peter]

Hello,

I'm trying to run PowerShell language inside a JOB, here I have to handle with special characters like 'äöüßåø'.

I know, that there are simlar threads, but these didn't help me to fix my issues.

If I run only these 2 lines inside the JOB, then I'm already getting the 6 question marks.

$sixChars= 'äöüßåø'
Write-Output $sixChars

When I look at the temp file(JAAHRWXR.TXT.ps1), then it is encoded with:

IsSingleByte : True
Preamble :
BodyName : us-ascii
EncodingName : US-ASCII
HeaderName : us-ascii
WebName : us-ascii
WindowsCodePage : 1252
IsBrowserDisplay : False
IsBrowserSave : False
IsMailNewsDisplay : True
IsMailNewsSave : True
EncoderFallback : System.Text.EncoderReplacementFallback
DecoderFallback : System.Text.DecoderReplacementFallback
IsReadOnly : True
CodePage : 20127

and PowerShell session is running with:

Preamble          : 
BodyName          : utf-8
EncodingName      : Unicode (UTF-8)
HeaderName        : utf-8
WebName           : utf-8
WindowsCodePage   : 1200
IsBrowserDisplay  : True
IsBrowserSave     : True
IsMailNewsDisplay : True
IsMailNewsSave    : True
IsSingleByte      : False
EncoderFallback   : System.Text.EncoderReplacementFallback
DecoderFallback   : System.Text.DecoderReplacementFallback
IsReadOnly        : True
CodePage          : 65001

I'm using PowerShell 7 

ECPEXE=C:\Program Files\PowerShell\7\pwsh.exe -NonInteractive -ExecutionPolicy bypass -NoLogo -file
ECPEXT=ps1
FT_TEMP_FILE=yes

Would be nice, if someone can give me a hint to keep the right encoding.

Regards,
Sascha

[kay.koll]

Hi Sascha,

I do not have a solution for you. UTF-8 support is on the roadmap.
At least the 'if' condition can compare the special character

$sixChars= 'äöüßåø'
Write-Output $sixChars
$singleChar = 'ä'
if($singleChar -eq 'ä') {
Write-Output 'singleChar includes ä'
}
else
{
Write-Output 'could not find ä'
}

------------------------------
Sr. Solution Architect
Broadcom
------------------------------

[Sascha Peter]

Hi Kay,

thanks for the reply, this doesn't work also.

$sixChars = 'äöüßåø'
Write-Output $sixChars
$singleChar = 'ä'
if ($singleChar -eq 'ß') {
    Write-Output 'singleChar includes ä'
}
else {
    Write-Output 'could not find ä'
}

then the output is still:

??????
singleChar includes ?

[kay.koll]

Hi Sascha,
there is one solution. You have to use external files or define PowerShell libraries with the function who are dealing with the German Umlauts.
The PowerShell code in the Automic Job just calls the external file but you have to specify in the Attribute Tab the ASCII_850 code table.

Following example code is working on my machine.

CODE OF THE EXTERNAL FILE ps1Lib.ps1
$charVal = [int][char]'ä'
$gerChars = 'äöü'
Write-Output $gerChars

function libFunction {
$Script:GlobalVariable = "Changed by function with German umlauts äüö"
} ​
and this is the code for in the Automic JOBS object
. C:\temp\ps1Lib.ps1

libFunction

Write-Output $charVal
Write-Output $gerChars
Write-Output $GlobalVariable

and this is the output

äöü
228
äöü
Changed by function with German umlauts äüö

------------------------------
Sr. Solution Architect
Broadcom
------------------------------

[Sascha Peter]

Hi Kay,

Thanks again.

Maybe I need to describe more detailed what I want to achieve.

The data is comming from the request manager and needs to be transfered into a JSON to be used at a REST API interface (Remedy).

I have to use PowerShell 7 or at least > 5 due to some certificate handling limitations in version 5 incase of invoke-WebRequest and invoke-RestMethod.

So I have to use a dynamic approach and not a static file. It seems I need to find a way to store that data and grab it during exectuon of the PowerShell script, means after the temp file is created.

So if you have an idea how to access the workflow variables during execution of the (temp) script, that would be really helpful.

/Sascha

[Joel Wiesmann]

Hi Sascha

It's a bit difficult to see what part of your use-case is covered in Powershell and what is covered on the AE side. So here's a workaround for utf-8 support in the script tab with the allmighty Powershell.

UTF-8 covers the Unicode Characterset. Unicode has an alternative representation of all characters, so "ä" would be U+00E4. You can lookup that table here: https://unicode-table.com/de/

Now if you want to have an utf-8 "ä" on a UTF-8 Powershell in a script/file that is not UTF-8 encoded, you can do like this:
$ae = [char][int]("\U+00E4" -replace "\\u", "0x")

... or short:
$ae = [char]0x00E4

This way you might also do comparisions. Let's say, the file "ue.txt" is utf-8 encoded and contains the character "ü", then this comparision would equal to "true":
(Get-Content ue.txt) -in ([char]0x00E4, [char]0x00FC, [char]0x00F6)

Does that help in any way?


------------------------------
Swisscom Automation Engineer & PE Membership Creator

Entfessle mehr Automic Wissen mehr Automic Tools
https://membership.philippelmer.com/
------------------------------

[Sascha Peter]

Hi Joel,

your approach goes in the right direction, but as I understand it can't be covered in the PowerShell part.

I have multiple variables in the workflow, which needs to be parsed in a JSON.

If I could do it in the PowerShell part, then it would be easy to use an .Net assembly to do what you did suggest.

So, if there isn't a way to (re-)read the variable during execution of the 'temp' script, then it needs to be done in the pre-process and that seems to be a longer part.

I have to do this for a several of european languages (danish,swedish,polish,german) in a list of workflow variables.

/Sascha

[Joel Wiesmann]

Hi Sascha

So you have a variable containing a json on workflow level that contains the special characters and are passed to the child-job which then uses Powershell. While passing the special characters from the job-script down to the temporary file you loose them. Did I get that right?

In JSON it would be a valid method to replace special characters with the unicode-representation \U.... This would cover all danish, swedish, polish, german and even emoji-icons. Whatever feeds your workflow might do this replacement already?

------------------------------
Swisscom Automation Engineer & PE Membership Creator

Entfessle mehr Automic Wissen mehr Automic Tools
https://membership.philippelmer.com/
------------------------------

[Sascha Peter]

Hi Joel,

maybe I didn't explain it clear enough. The JSON needs to consume several workflow variables.

like this 

$json= @{values = [ordered]@{
valueA = "&VALUE_A#"
valueB = "&VALUE_B#"
valueC = "&VALUE_C#"
}
} | ConvertTo-Json

and &VALUE_A#, &VALUE_B# and &VALUE_C# can contain special characters at any place.

Viele Grüße

Sascha

[Joel Wiesmann]

Okay I see. In the end you end up with the same issue.

I see these options:
- If possible, directly get the input from the source system using Powershell 
- Whereever you get &VALUE_*# from - have the source system encode the special characters in unicode notation (\U+....). There is a chance, that you can send them 1:1 to the target system (and if not, Powershell can reencode it into UTF-8).
- STR_SUBSTITUTE 🤣, but I'm not sure how successful/relieable this would be..

I don't see more alternatives. Let us know if you found a solution 🤙

------------------------------
Swisscom Automation Engineer & PE Membership Creator

Entfessle mehr Automic Wissen mehr Automic Tools
https://membership.philippelmer.com/
------------------------------

[Sascha Peter]

The source system(s) are the request manager and ASO, but if I remember me right, then the request manager database was a bit more intuitive than the ASO database.

And if we keep in mind, that the data is there, but you can't use it....

[Joel Wiesmann]

Jeah it's a shame. I added some emojis to my signature to remind our Automic engineers about the importance of the UTF-8 support. #utf8now​

------------------------------
☎️ Swisscom Automation Engineer & 🧙 PE Membership Creator

🌟🌟🌟🌟🌟🌟
Entfessle mehr Automic Wissen mehr Automic Tools
https://membership.philippelmer.com/
🌟🌟🌟🌟🌟🌟
------------------------------

[Sascha Peter]

​Hello,

Finally I have a solution, which covers the most cases.

Kay was a great help here in the background. Thanks a lot.

Together we were looking in a solution by creating a temporary file in the preprocess, it was working fine, but it had a drawback.
I couldn't handle multi-line values(strings) in an easy way any longer.

So I was looking in a different solution, by grabbing the data directly from the EV table.

So it looks now:

Pre-Process
:SET &RUNNR# = SYS_ACT_ME_NR()
:RSET &RUNNR# = FORMAT(&RUNNR#)

Process
[Console]::OutputEncoding = [Text.Encoding]::GetEncoding(1252)
:INCLUDE JOBI.POWERSHELL_FUNCTIONS_PROD
Import-Module SQLServer

$ServerInstance = "localhost"
$timeout = 0
$database = "UC4_ASO"
$Query = "SELECT distinct EV_VName,EV_Value
FROM EV
where EV_AH_Idnr = '&RUNNR#'"

try {
    $queryResult = Invoke-Sqlcmd -ServerInstance "$serverInstance" -DataBase $database -Query $Query -QueryTimeout $timeout -ErrorAction Stop -Verbose   
}
catch {
    write-Verbose "Caught an exception:"
    write-Verbose "Exception type: $($_.Exception.GetType().FullName)"
    write-Verbose "Exception message: $($_.Exception.Message)"
    write-Verbose "Error: " $_.Exception           
}

[hashtable]$varList = [ordered]@{}

foreach ($row in $queryResult) {
    $newVar = $($row.EV_VName)
    $newVal = $($row.EV_Value)
    # trimming & and #
    $newVar = $newVar.Trim([char]0x0023, [char]0x0020).Trim([char]0x0026, [char]0x0020).ToLower()
    # replace line breaks with '\n' for JSON
    $newVal = $newVal.replace("`n", "\n").replace("`r", "\n").replace("\n\n", "\n")
    New-Variable -Name $newVar -Value $newVal -Force
    # next line is for testing purpose only, to see what we are getting
    $varList.Add($newVar, $(Get-Variable -Name $newVar -ValueOnly))
}

# let's take a look
$varListStr = $($varList) | Out-String
Write-Output $varListStr


That is still not a complete solution, but it seems I have to live with that.
We can't handle polish characters, because these letters are already gone, if I'm looking at the database.
Sure I could use a second database and start over again, but ;-)

[Sascha Peter]

Hi Kay,
you mentioned that UTF-8 is on the roadmap and V21 is knocking on the door, but it seems it is coming again without UTF-8 support.

Is that right?

Regards,
Sascha