Export custom PowerShell object to CSV

Question

I want to create a full mailbox permission report and export it to a CSV in PowerShell.

$report = @()
$mailboxes = Get-Mailbox -ResultSize unlimited -RecipientTypeDetails sharedmailbox

foreach ($mailbox in $mailboxes)
{ 
    $permissions = Get-Mailbox $mailbox.PrimarySmtpAddress | Get-MailboxPermission | Select-Object user, accessrights
    $users = $permissions.user
    $accessRights = $permissions.accessrights
    $sendAsPermissions = Get-Mailbox $mailbox.PrimarySmtpAddress | Get-ADPermission | Where-Object {$_.extendedrights -eq "send-as"} | select -ExpandProperty user
    $sendOnBehalfPermissions = Get-Mailbox $mailbox.PrimarySmtpAddress | select -ExpandProperty grantsendonbehalfto

    $properties = @{
    Identity = $mailbox.PrimarySmtpAddress
    Users = ($permissions.user -join "`n")
    AccessRights = ($permissions.accessrights -join "`n")
    SendAs = ($sendAsPermissions -join "`n")  
    SendOnBehalf = ($sendOnBehalfPermissions -join "`n")
    }

    $mailboxObject = New-Object -TypeName psobject -Property $properties

    #$mailboxObject | select identity, users, accessrights | Export-Csv -Append -NoTypeInformation -Path c:\temp\test.csv
    $mailboxObject | select identity, users, accessrights, sendas, sendonbehalf | out-gridview
}

The gridview shows exactly what I want but I'm having a hard time getting it correctly exported to a CSV.

Answer 1

I know for a fact this could be both better and more suited to your exact case, but figured it was still worth sharing.

You can get the Import-Excel module which easily allows you to create and format Excel files. You may install it from the Powershell Gallery located here. This is basically assuming that your sheets are for human eyes only, and given the format, I think I can say with some degree of confidence that is true.

First I started by collecting all the objects before appending, which I think is a pretty common mistake. Just output the object directly and collect it in a variable.

$report = @()
$mailboxes = Get-Mailbox -ResultSize unlimited -RecipientTypeDetails sharedmailbox

$collectOutput = foreach ($mailbox in $mailboxes)
{ 
  $permissions = Get-Mailbox $mailbox.PrimarySmtpAddress | Get-MailboxPermission | Select-Object user, accessrights
  $users = $permissions.user
  $accessRights = $permissions.accessrights
  $sendAsPermissions = Get-Mailbox $mailbox.PrimarySmtpAddress | Get-ADPermission | 
    Where-Object {
      $_.extendedrights -eq 'send-as'
    } | Select-Object -ExpandProperty user
  $sendOnBehalfPermissions = Get-Mailbox $mailbox.PrimarySmtpAddress | Select-Object -ExpandProperty grantsendonbehalfto

  [pscustomobject]@{
    Identity     = $mailbox.PrimarySmtpAddress
    Users        = ($permissions.user -join "`r`n")
    AccessRights = ($permissions.accessrights -join "`r`n")
    SendAs       = ($sendAsPermissions -join "`r`n")
    SendOnBehalf = ($sendOnBehalfPermissions -join "`r`n")
  }
}

Now I'm going to use the Export-Excel cmdlet to generate a OfficeOpenXml.ExcelPackage object, which we can then manipulate with a few helper functions. I found that this almost emulated Out-Gridview. The header wrapping is just a little bit off, but you're welcome to experiment with different parameters until you find something that looks exactly right to you.

$sheet = $collectOutput | Export-Excel -Path 'C:\Users\username\Desktop\testing.xlsx' -PassThru -AutoSize

$sheet.Sheet1.Column(1) | Set-ExcelRange -VerticalAlignment Center
$sheet.Sheet1.Column(2) | Set-ExcelRange -WrapText
$sheet.Sheet1.Column(3) | Set-ExcelRange -WrapText
$sheet.Sheet1.Column(4) | Set-ExcelRange -WrapText

$sheet.Save()
$sheet.Dispose()

Answer 2

To convert the object you might use pipe on the select result to ConvertTo-Csv, like this:

  $selectResult = $mailboxObject | select identity, users, accessrights, sendas, sendonbehalf
  $selectResult | out-gridview
  $selectResult | ConvertTo-Csv > .\mailbox.csv

Answer 3

the Export-Csv is for that.

Here is a working example:

$o = [PSCustomObject]@{a = 1 } ,
     [PSCustomObject]@{a = 2 }

# Export     
$o | Export-Csv -Path a.csv

# check the file
Get-Content ./a.csv

To find commands working with csv you can use

Get-Command *csv*

Answer 4

Can you try this

Identity = $mailbox.PrimarySmtpAddress
Users = (@($permissions.user -join "`n") |Out-String).Trim()
AccessRights = (@($permissions.accessrights -join "`n")|Out-String).Trim()
SendAs = (@($sendAsPermissions -join "`n") |Out-String).Trim() 
SendOnBehalf =(@($sendOnBehalfPermissions -join "`n")|Out-String).Trim()

**you can look this site **https://learn-powershell.net/2014/01/24/avoiding-system-object-or-similar-output-when-using-export-csv/