Encoding CSV content to UTF-8

Question

So I have this powershell script Which import a csv file, replacing null into '0' and export this csv.

The issue is that the content and header of this csv is in hebrew

I tried almost everything Used -Encoding for all types of encoding but nothing

Any Suggestions?

$propertyTranslation = @(
    @{ Name = 'Customer__c';   Expression = { $_.'לקוח' } }
    @{ Name = 'Name__c';       Expression = { $_.'שם'  } }
    @{ Name = 'CheckCount__c'; Expression = { $_.'כמות'  } }
    @{ Name = 'Deal';          Expression = { $_.'עסקהוזה'  } }
    @{ Name = 'Amount__c';     Expression = { $_.'סכום'  } }
    @{ Name = 'Discount__c';   Expression = { $_.'ניסיון'  } }
    # And so on
)

$csv = Import-Csv C:\Users\alon\Documents\again.csv -Header "Customer__c","Name__c","Deal","Amount__c","CheckCount__c","Discount__c" 

$csv | ForEach-Object {
    if($_.Customer__c -eq "")   { $_.Customer__c = "0" }
    if($_.Name__c -eq "")       { $_.Name__c = "0" }
    if($_.Deal -eq "")          { $_.Deal = "0" }
    if($_.Amount__c -eq "")     { $_.Amount__c = "0" }
    if($_.Discount__c -eq "")   { $_.Discount__c = "0" }
    if($_.CheckCount__c -eq "") { $_.CheckCount__c = "0" }
} 

Select-Object -Property $propertyTranslation 

$csv | Export-Csv C:\Users\alon\Documents\CheckDealBeforeUpsert.csv -NoTypeInformation -Encoding UTF8

Your `$propertyTranslation` type is `system.array`; consider a [Hash Table](https://technet.microsoft.com/en-us/library/ee692803.aspx) instead, something like `$propertyTranslation = @{ 'Customer__c' = 'לקוח'; 'Name__c' = 'שם' ; 'CheckCount__c' = 'כמות' ; 'Deal' = 'עסקהוזה' ; 'Amount__c' = 'סכום' ; 'Discount__c' = 'ניסיון' }`. However, I'd say that your script could work if you do omit `$propertyTranslation` at all. — JosefZ, Dec 30 '16 at 09:52
You can see om my code (Last row take a bit right) That i tries to use -Encoding. — Alon Segal, Dec 30 '16 at 13:01
Sorry. You are right. Have you tried _reading_ the file with the `-Encoding` as well. The problem starts there. — Matt, Dec 30 '16 at 13:03
Hi matt, Do you mean before importing? (I'm a bit new on powershell scripting) - Thanks in advance! — Alon Segal, Dec 30 '16 at 13:05
The source file is UTF-8 as well correct? If that is the case you need your Import-CSV line to be using `-Encoding` as well. `$csv = Import-Csv C:\Users\alon\Documents\again.csv -Header "..." -Encoding UTF8` — Matt, Dec 30 '16 at 13:06
`$csv | Select-Object $propertyTranslation | Export-Csv C:\Users\alon\Documents\CheckDealBeforeUpsert.csv -NoTypeInformation -Encoding UTF8` You created calculated properties in order to change some values? you are not using them. I don't know if it will do what you expect but if you have columns with hebrew characters you need to be exporting that. I might be lost on how you are manipulating your input. — Matt, Dec 30 '16 at 13:09
Matt thanks, you helped me understand that the issue is the csv encoded as ANSI - when i manually change the encoding to UTF8 and did as you advised - It works. The issue that the files are always will be ANSI (Batch files) — Alon Segal, Dec 30 '16 at 13:10
What is your default encoding on your system? Perhaps your PowerShell is not opening that file with ANSI to begin with. http://stackoverflow.com/questions/5326304/powershell-get-default-system-encoding. I am not great with encoding issues as I do not deal with them enough. — Matt, Dec 30 '16 at 13:12
Ok matt, I will look for a way to change the system default encoding. Thanks you so much for you assist! — Alon Segal, Dec 30 '16 at 13:18
If I'm not mistaken your hashtable is backwards. What you posted in your question would translate hebrew to english headers. If you want it the other way 'round you need to put the hebrew string as the name and the english property as the expression of the hashtables (e.g. `@{n='לקוח';e={$_.Customer__c}}`). Or you could just import the CSV using the hebrew headers. — Ansgar Wiechers, Dec 30 '16 at 17:39
Well, what exactly *is* the issue in the first place? You said it's an encoding issue, but how exactly does the issue manifest itself? Can you provide sample input and output (preferably along with a hexdump of the first couple bytes of input and output)? You said the source file has ANSI encoding, but which codepage exactly? ANSI in PowerShell terms usually means windows-1252 whereas the codepage for hebrew is windows-1255. Not sure if that depends on the Windows language version. — Ansgar Wiechers, Dec 30 '16 at 18:51
The issue is that I'm using a script to add '0' to empty cells because a groovy scrip collapse once it hits empty cells. The issue is that this script above return "???????" Where the content is Hebrew — Alon Segal, Dec 30 '16 at 19:06
Did you try `Import-Csv -Encoding OEM`? If that doesn't work you may need to convert the input file to UTF-8 first, see [here](http://stackoverflow.com/a/32121665/1630171) (try with `[Text.Encoding]::GetEncoding(1255)` for the reader and `[Text.Encoding]::UTF8` for the writer). — Ansgar Wiechers, Dec 30 '16 at 19:17
Hi Ansgar, thanks for your assistance! not sure I understood the code (a bit newbie on powershell) but I will try — Alon Segal, Dec 30 '16 at 20:30

Ansgar Wiechers · Answer 1 · 2017-01-02T21:35:58.597

The term "ANSI" as it is used in Windows is basically an umbrella term for a number of encodings (or code pages). Usually it refers to the windows-1252 encoding. Your input file, however, appears to be encoded using the windows-1255 code page.

I'm not sure if in PowerShell -Encoding ASCII always means windows-1252 encoding, or if that is adjusted for localized Windows versions. If it's not adjusted you probably need to convert your input file to an encoding Import-Csv can handle before you can import and modify the data:

$inFile  = 'C:\path\to\input.csv'
$outFile = 'C:\path\to\input_utf8.csv'

$reader = New-Object IO.StreamReader ($inFile, [Text.Encoding]::GetEncoding(1255))
$writer = New-Object IO.StreamWriter ($outFile, $false, [Text.Encoding]::UTF8)

while ($reader.Peek() -ge 0) {
  $writer.WriteLine($reader.ReadLine())
}

$reader.Close(); $reader.Dispose()
$writer.Close(); $writer.Dispose()

Encoding CSV content to UTF-8

1 Answers1