Support for international characters?

[andrewbeno]

I tried searching discussions for this type of topic, but haven't come across any so I figured I'd post here.

I'm importing a CSV file that has some characters with accents, i.e., "Brésil", and then using Export-Excel to generate a XLSX file, but the resulting XLSX file doesn't "preserve" them. Is there a way to workaround that?

Here's a portion of the script:

$data = Import-Csv -Path $destinationPath -Encoding UTF8
$ExcelFilename = (Get-Item $destinationPath).Basename + ".xlsx"
$data | Export-Excel .\$ExcelFilename -AutoNameRange -Show

Here's what one line of the CSV file looks like:

Here's what that same line in the exported XLSX file looks like:

Thanks for any input/advice!

Andy

[dfinke]

@scriptingstudio thoughts?

[scriptingstudio]

My op environment

Windows 11 23H2 En with Russian support
MSO 2021
VS Code
Windows Terminal
Powershell 5.1 / 7.4.2

get-culture

LCID             Name             DisplayName
----             ----             -----------
1049             ru-RU            Russian (Russia)

Steps to reproduce

Script:

& {
	$data = Import-Csv test1.csv -Encoding utf8 -Delimiter ';'
	$ExcelFilename = 'test1.xlsx'
	if (Test-Path $ExcelFilename) {Remove-Item $ExcelFilename}
	$data | Export-Excel $ExcelFilename -AutoNameRange -Show
}

Datafile:

Id;Name;Lang
support@;Brésil;France|Французский/Бразильский
support@;Нью-Йорк;Russian|Русский
support@;巴西;China|Китай

CSV output:

import-csv test1.csv -delim ';'

Id       Name     Lang
--       ----     ----
support@ Brésil   France|Французский/Бразильский
support@ Нью-Йорк Russian|Русский
support@ 巴西     China|Китай

I tested data with both encodings (UTF8BOM and UTF8noBOM) in both versions of PowerShell - 4 cases.

Input & output files:
test1.csv

test1.xlsx

Data file in UTF8BOM opened in Excel looks like this:

Data file in UTF8noBOM opened in Excel looks like this:

I had no any issue with results. It should just work. Datafile encoding does not matter (BOM or NOBOM), output is always correct.
I think the problem is in @andrewbeno environment or CSV file has wrong encoding (non UTF).

Additional test with non-UTF encoding

Encoding is Windows 1251
output file:

I'm inclined to think that the issue is non-UTF encoding of datafile (most probably ASCII).
Symbol

means lost data

I played with different encodings but no luck.
Cannot say anymore without seeing real datafile.

[dfinke]

Thanks, super!

[scriptingstudio]

Question mark implies lost data which can mean corrupted source or unsuitable argument of -encoding parameter for import-csv.
The ImportExcel module does not do anything with encoding.

[andrewbeno]

Thanks @dfinke and @scriptingstudio! I should've done more troubleshooting before posting, but I appreciate the help and hope you didn't spend too much time investigating. After reading your response, I read that one way to confirm the encoding of a CSV file is in Notepad++ in the lower-right corner, and saw that the CSV I was importing was not encoded with UTF-8 as you suggested.

I was able to workaround the issue by using the 'Default' encoding (initially I had no encoding parameter and then only tested with UTF8 before posting.. can't believe I didn't try "Default" 😑 )

$data = Import-Csv -Path $destinationPath -Encoding Default
$ExcelFilename = (Get-Item $destinationPath).Basename + ".xlsx"
$data | Export-Excel .\$ExcelFilename -AutoNameRange -Show

Thanks again and have a good one!
Andy

[dfinke]

Thanks @andrewbeno. Did not know -Encoding existed on Import-Csv