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).

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

… 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).

Based on port of C# code from http://www.west-wind.com/Weblog/posts/197245.aspx

.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' }

}

Thanks Chris, Manfred and friends from the following article:

http://social.technet.microsoft.com/Forums/exchange/en-US/5ad656a5-fe70-477f-a608-0e588096f227/how-to-get-mailbox-folder-permissions-to-all-mailbox-folders-in-all-mailboxes?forum=exchangesvradminlegacy

Manfred Preissner : http://social.technet.microsoft.com/profile/manfred%20preissner/?ws=usercard-mini

$SpecialExchangeFolders = "Top of Information Store|Recoverable Items|Deletions|Purges|Versions"

$CurrentUser = gci env:username | % { $_.value }

[string[]] $FolderPaths = Get-MailboxfolderStatistics $CurrentUser | % {$_.folderpath}

$ExchangeFolderPaths = $FolderPaths | % {$CurrentUser + ":" + $_.replace('/','\')}

$UsableExchangeFolderPaths = $ExchangeFolderPaths | where { $_ -notmatch $SpecialExchangeFolders }

$UsableExchangeFolderPaths | % { get-mailboxfolderPermission $_ } | ft FolderName, User, Acces sRights, Identity -auto

Result will be like :

FolderName                   User      AccessRights
----------                   ----      ------------
Calendar                     Default   {Owner}
Calendar                     Anonymous {None}
Calendar                     User177   {CreateItems, EditOwnedItems}
Calendar                     User1769 {CreateItems, EditOwnedItems}
Calendar                     User1768 {CreateItems, EditOwnedItems}
Calendar                     User1767 {CreateItems, EditOwnedItems}
Calendar                     User1766 {CreateItems, EditOwnedItems}
Calendar                     User1765 {CreateItems, EditOwnedItems}
Calendar                     User1764 {CreateItems, EditOwnedItems}
Calendar                     User1763 {CreateItems, EditOwnedItems}
Calendar                     User1762 {CreateItems, EditOwnedItems}
Calendar                     User1761 {CreateItems, EditOwnedItems}
Calendar                     User1760 {CreateItems, EditOwnedItems}
Contacts                     Default   {None}
Contacts                     Anonymous {None}
Conversation Action Settings Default   {None}
Conversation Action Settings Anonymous {None}
Deleted Items                Default   {None}
Deleted Items                Anonymous {None}
Drafts                       Default   {None}
Drafts                       Anonymous {None}
Inbox                        Default   {None}
Inbox                        Anonymous {None}
Journal                      Default   {None}
Journal                      Anonymous {None}
Junk E-Mail                  Default   {None}
Junk E-Mail                  Anonymous {None}
News Feed                    Default   {None}
News Feed                    Anonymous {None}
Notes                        Default   {None}
Notes                        Anonymous {None}
Outbox                       Default   {None}
Outbox                       Anonymous {None}
Quick Step Settings          Default   {None}
Quick Step Settings          Anonymous {None}
RSS Feeds                    Default   {None}
RSS Feeds                    Anonymous {None}
Sent Items                   Default   {None}
Sent Items                   Anonymous {None}
Suggested Contacts           Default   {None}
Suggested Contacts           Anonymous {None}
Tasks                        Default   {None}
Tasks                        Anonymous {None}

Cherry on the cake, visualizing the number of permissions per folder in an Excel graph.

To do so, just copy paste the result above, OR in your script, export the results into a CSV file (Export-CSV can do the trick, as well as Out-File or redirecting “>” into a file, up to you !) and then “Text to columns” Excel menu, then “Format as table” and then “Insert pivot graph”, et voilà :

This is just an example showing what we can do with Excel and a Cut/n/Paste from Powershell raw data…

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

Exchange 2007/2010/2013 – Using Get-MailboxFolderPermissions to export MAPI permissions of folders for a given mailbox