I have a script that merges Data from an Oracle Database (with SQLPlus) and creates CSV Files from that. When I run this script from PowerShell ISE everything seems fine, but when I run it from a normal powershell-Session or via right-click -> Execute with Powershell, characters like ä,ö,ü are not dispalyed correctly in the CSV-File instead characters like "÷" and "³" are being displayed.
I already set the encoding for the CSV-Export to UTF8 with
export-csv -Encoding UTF8
But the Problem doesn't seem to be the export but how I get the Data from the SQLPlus session.
I already checked which encoding the Session runs in by logging [Console]::OutputEncoding
In the ISE the Encoding is iso-8859-1 and the Powershell-Encoding is ibm850.
So I guess my solution would be to force the Powershell-Session to run with the same Encoding as the ISE-Session does. I just don't know how to set this Parameter. I already tried executing the following commands before I run the Script:
set NLS_LANG=AMERICAN_AMERICA.UTF8
set NLS_LANG=UTF8
$OutputEncoding = [System.Text.UTF8Encoding]::new()
None of this worked and the OutputEncoding remained ibm850 I also tried to alter the Oracle Session with
ALTER SESSION SET NLS_LANGUAGE='UTF8'
But that returns an error.
Minimal Coding Example would be as following:
$sqlQuery_Example = @"
set pagesize 0
set feedback off
set heading off
set linesize 15000;
set colsep ;;
set headsep ;;
Select * FROM table
"@
$ExampleSQL = $sqlQuery_Example | sqlplus -silent USER/PW@DB
$ExampleCSV = ConvertFrom-Csv $ExampleSQL
$ExampleCSV | export-csv C:\TEMP\example.csv -Encoding UTF8 -NoTypeInformation -Delimiter ';'
I use the PowerShell Version 5.1.17763.2268. Can someone help me change the Encoding of the Powershell-Session to be the same as in the ISE-Session?
CodePudding user response:
Solution to my script was adding the following line before the SQLPlus session was started:
$OutputEncoding = [console]::InputEncoding = [console]::OutputEncoding = [Text.Encoding]::GetEncoding((Get-Culture).TextInfo.ANSICodePage)
Now Ö, Ü, Ä and so on are being displyed correclty
CodePudding user response:
When running ps1 files the PowerShell instance will default to whatever encoding the file is detected as. Usually this can be somewhat unreliable and usually will default to the system encoding.
Saving ps1 files with "UTF8 with BOM" encoding (sometimes refered to UTF8 Signature) will make PowerShell run in UTF8 mode when you are running a script.