Import-Excel Powershell character encoding issues

Question

I have the following code

$filename = "C:\test\ReboundItem.xlsx"
$InFile = Get-Item $filename
$ITEMOutFile= $InFile.FullName.replace($InFile.Extension,".csv")
$example = Import-Excel $Infile.FullName
$example | Export-Csv $ItemOutFile -encoding utf8

When Import-Excel reads in German or French characters it changes them and doesnt seem to encode them properly. How would I en code them on the import as utf-8?

for example turns

'Art déco Haarspange' to 'Art déco Haarspange'

Any help appreciated.

Answer 1

Try adding "-Encoding UTF8" to the Import-CSV.

I'm guessing that if you examine the input file (use a hex editor) you'll find that it has no BOM at the beginning.

Answer 2

I'd have to say that the program reading your CSV file isn't handling the file correctly (i.e. ignoring the BOM at the beginning of the file).

get-content c:\junk\art.csv | Format-Hex

           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   41 72 74 20 64 3F 3F 63 6F 20 48 61 61 72 73 70  Art d??co Haarsp
00000010   61 6E 67 65                                      ange

get-content c:\junk\art.csv                            

Column1
Art déco Haarspange


get-content c:\junk\art.csv | Format-Hex -encoding utf8

           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   41 72 74 20 64 C3 83 C2 A9 63 6F 20 48 61 61 72  Art déco Haar
00000010   73 70 61 6E 67 65                                spange

get-content c:\junk\art.csv -encoding utf8

Column1
Art déco Haarspange

When I look at the file in a hex editor I see the correct BOM at the beginning of the file:

Offset(o) 00 01 02 03 04 05 06 07 10 11 12 13 14 15 16 17

00000000  EF BB BF 22 64 C3 A9 63 6F 22 0D 0A 22 41 72 74  "déco".."Art
00000020  20 64 C3 A9 63 6F 20 48 61 61 72 73 70 61 6E 67   déco Haarspang
00000040  65 22 0D 0A                                      e"..