Determine the file encoding of a file - CSV file with French accents or other exotic characters that you’re trying to import in Powershell - Microsoft Exchange pour Tous V2 - Microsoft Exchange made simple - Site Home - TechNet Blogs

Microsoft Exchange pour Tous V2 - Microsoft Exchange made simple

Nouveautés, principes, évolution, trucs et astuces - News, principles, evolution, tips and tricks

Determine the file encoding of a file - CSV file with French accents or other exotic characters that you’re trying to import in Powershell

Determine the file encoding of a file - CSV file with French accents or other exotic characters that you’re trying to import in Powershell

  • Comments 1
  • Likes

Bernie and I had an issue today trying to import a CSV file using Import-CSV in Powershell V2.0, as the French accents and some dashes were not imported correctly, and then we couldn’t use some of the information in the CSV to query Exchange or AD objects with other Powershell commandlets.

So it appears that this depends on the encoding of the CSV file and how Powershell’s Import-CSV is handling it.

Using Powershell v3.0, I was able to Import-CSV the CSV file with accents and then Export filtered results in a file, keeping these special characters in Powershell and in the final filtered file, because “Import-CSV” in Powershell v3.0 has the “-Encoding” parameter, which is missing in Powershell v1 and v2. So in Powershell V3, I saved my CSV file using Notepad and specifying “UTF-8” encoding. Then I did an Import-CSV using the “-Encoding UTF8” parameter (remember Powershell 3.0 only).

image

That worked ! I kept my French accents for the names in the CSV file.

But Powershell V1 and V2 Import-CSV does not have this “-Encoding” parameter. So Bernie and I had to find in which encoding we had to save our CSV file so that the Import-CSV was keeping the initial characters, so that we can handle the objects to do some stuff with them.

So we tried many, and found that saving our CSV file as “Unicode” with Notepad worked fine, and saved us lot of time for the future.

Note that by default Excel saves CSV files in the “ANSI” encoding. In my case, Powershell then imported the French accent and some other exotic characters with a “?” sign.

So the steps if you are using Excel to build your CSV are:

1- Save your Excel CSV as “.CSV” file

2- Open the .CSV again using NOTEPAD

3- Save again with NOTEPAD using the “Unicode” encoding

image

… and you’re good to Import-CSV in Powershell v1 and v2, and even v3 without needing to specify the –Encoding parameter.

Below is a nice Powershell script to enable you to check which encoding is your CSV file (or any other file but I didn’t test it for other ones):

http://poshcode.org/2059

Get-FileEncoding by Chad Miller 3 years ago
View followups from
JasonMArcher, RyanFisher, Enter your zip code here and Billy | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2059"></script>download | copy to clipboard | new post

Pasting here for my convenience – please check for the original one and check for updates on the above original location.

<#

.SYNOPSIS

Gets file encoding.

.DESCRIPTION

The Get-FileEncoding function determines encoding by looking at Byte Order Mark (BOM).

.EXAMPLE

Get-ChildItem  *.ps1 | select FullName, @{n='Encoding';e={Get-FileEncoding $_.FullName}} | where {$_.Encoding -ne 'ASCII'}

This command gets ps1 files in current directory where encoding is not ASCII

.EXAMPLE

Get-ChildItem  *.ps1 | select FullName, @{n='Encoding';e={Get-FileEncoding $_.FullName}} | where {$_.Encoding -ne 'ASCII'} | foreach {(get-content $_.FullName) | set-content $_.FullName -Encoding ASCII}

Same as previous example but fixes encoding using set-content

#>

function Get-FileEncoding

{

    [CmdletBinding()] Param (

     [Parameter(Mandatory = $True, ValueFromPipelineByPropertyName = $True)] [string]$Path

    )

 

    [byte[]]$byte = get-content -Encoding byte -ReadCount 4 -TotalCount 4 -Path $Path

 

    if ( $byte[0] -eq 0xef -and $byte[1] -eq 0xbb -and $byte[2] -eq 0xbf )

    { Write-Output 'UTF8' }

    elseif ($byte[0] -eq 0xfe -and $byte[1] -eq 0xff)

    { Write-Output 'Unicode' }

    elseif ($byte[0] -eq 0 -and $byte[1] -eq 0 -and $byte[2] -eq 0xfe -and $byte[3] -eq 0xff)

    { Write-Output 'UTF32' }

    elseif ($byte[0] -eq 0x2b -and $byte[1] -eq 0x2f -and $byte[2] -eq 0x76)

    { Write-Output 'UTF7'}

    else

    { Write-Output 'ASCII' }

}

 

 

Comments
  • what is this decoded?-]‘ßà&”MF.∂ïJ°†Ò’¶+õ]Ä™WmKnå1¸)≠#ÊI5_b≥°f€G+â=X\êz®qÌpkø⁄˙  *,'¡ïòe—¥v¡aß 9ç›38L®ª!ÇRj¯‰∆fiK˛ü¨Üψà
    ¥,‘màP‡ÆìŒã8‚q⁄«Œ¿=9ûhvR"à0ee8¨Èˆ–ˇˇ‚ ÿµÿW3ˇ_EÓµˇz†`eU
    iÅ‹$ÄQúg±ˇåâXR›€≥Œ«T€ruñ«m4ÂHø°Ruõ◊≤gÙ˚jÖ9»Ùf:::áàï?£õÅâ..Õsøõ #>ïÒî≠≥q¨√XΩdcºïÉ`Fƒ´£!6‚1Z¶ydÜÂÄ{P€Ó í≥êFæ%∂)Qœ`6Ì£”é;mê
    ©AkÛ≤oV†-Ühi\cùˇS∂`P<Å¡ˇçÉåfi9êÙ‡
    )◊LË¿sjó?W꺟ÈÕú®ïùnk≥FÖ7©B«QŸ–∞F+AE˛]†ˆÇ6Ê*"§º £ææ`œ›47ÒDa.˘ca‰SS∏ß|πi<π8çw. ÑÚxôƒ“#L‘À:i(Œ¨ˇ∂»£A¡⁄óD≤–kIIPô}*µòCæ5j%ø‚p°JdOì≤Á¯FÒìÍó.eO§[•Q{Ku÷ÉæÁ_≥fiŒÚH\uÖÆ® N@åIAÔAÂ!t‘r~∏aÉâ¬æ!ƒIÎLDD}Áùƒ√ÓÕÑÊ$›—N#q©Ú4^ب]?’ IéWU¸¸Ç/!…å∑4ÑNn"w£ØFA≈Üä+çF,X∆6>=ºò≥gÊx˙¿T˙> X˝◊5bÉRÖÁËÕV…™êC®üIÈ ›,a(Ìï´:U:M@r¨,U˛≤";íπ´M!·'π0÷πG®0¶ñí<Ö&˝ ó{πf/Y·6Í≤˜h»,#g0“õSmËaÒÀê(JöIâ˜-ÍC ⁄| îaÄæXºµ16÷»Ö ∑Á/ï¥goœŸˇ≤\¯*9ÿrƒº≈_
     Öçm?b*˙ËÙr/∑.¸sQQbjPõ¿ ªpk!¸#}cÆ•{'•˚n¢õ≥#&k‡˙t«~7áogF0KÜÍÈxæ˛ì •  EJUeiAµ
    ùíºç˚0$:fïÅø‰àwÌ_"¢è˜¯îDèÒˆ0isô≤]/1mtfl˘e2rëéÛ Ófl…<Àî”nΩ(÷Á¿˘π˘⁄߈é‚"?dÌ{e◊hΩô}JAEf™M&¥í695îÆ⁄∂'ø⁄fiçÎ8*º¬vã™Z1}gÍ√’kôü`Óë#Ö>¬Ñ!¶°1ô8∞rsoè¸fi—‚+›öñ
    '¿«p„:S
    ñX肶æ∑?l~∫Ê˚Ztt£ÿÙì j€ép«(¿‘a}º °qyt ∆Ü5¬œ¯≈l7È%¿-ÜW∆Œ;s
    °õ–;¿

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs