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.

In the CSV the first record of the 'users' column is placed in the 'users' column but all subsequent records are placed in the 'identity' column. I cannot seem to get the CSV to match the Out-Gridview. — Glenn
– Glenn, Commented Oct 3, 2018 at 15:58
Hi Try to replace psobject to pscustomobject on $mailboxobject — Kemal K.
– Kemal K., Commented Oct 3, 2018 at 17:46
Why is it necessary to have the CSV output like that? storing newline delimited strings in csv makes for odd looking output. That is how newlines appear in files. I don't think what you want is going to work. that being said it might be easier if you show what the csv looks like and what you want it to look like. I would have opted to flatten the arrays into semicolon strings to allow for one entry per identity or created one line per permission and have the identity column duplicated so the data is not lost when looking at one permission entry. — Matt
– Matt, Commented Oct 9, 2018 at 13:16
Are you basically asking to insert arbitrary whitespace into the csv output to have more tabular output? The csv is properly formed and PowerShell (and other applications) read it correctly as is. Changing that will introduce undesirable data into the output that would affect how it gets read. — Matt
– Matt, Commented Oct 9, 2018 at 13:30

Jacob Colvin · Accepted Answer · 2018-10-09 15:58:27Z

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

Is this really necessary? The output did open for me ok on Excel without going down this path.
It depends on exactly what the OP wants, but at least some of the alignment stuff is needed. @Matt Also for me the cells didn't expand by default just using a CSV.

fbehrens · Accepted Answer · 2018-10-14 08:14:07Z

0

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*

answered Oct 14, 2018 at 8:14

fbehrens

6,6612 gold badges21 silver badges22 bronze badges

Comments

Matheus Valiente Souza · Accepted Answer · 2018-10-15 19:03:38Z

0

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

answered Oct 15, 2018 at 19:03

Matheus Valiente Souza

3682 silver badges8 bronze badges

Comments

Kemal K. · Accepted Answer · 2018-10-17 14:18:30Z

0

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/

answered Oct 17, 2018 at 14:18

Kemal K.

4114 silver badges8 bronze badges

Collectives™ on Stack Overflow

Export custom PowerShell object to CSV

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related