Format the script to export in csv or Excel.

Hi Team,

As per my requirement,I managed to write a script. But the output is stored in a single cell in the output for the user. Can you help by advising me to finetune the script and export to a excel/csv with formatted output.

Sno 521 Exchange version mailbox size No. of Items in MB Dumpster size No. of Items in Dumpster LH enabled Current Permissions

The value of exchange version is in numbers so I have used if condition to store the Exchange value. The mailbox size and Dumpster size results are in bytes. I tried to divide them with /1MB but not getting the desired results. Also is it possible to add few color in the sheet if the Litigationhold enable is true = Green and if it is false then RED.

$users = GC c:\temp\alias.txt
foreach ($user in $users)
$mailbox = “”
$mailbox += (Get-mailbox -Identity $user).alias + “,”
$mailbox += (Get-mailbox -Identity $user).LitigationHoldEnabled
$mailbox += (Get-mailbox -Identity $user).PrimarySMTPAddress
$mailbox += (Get-mailbox -Identity $user).Displayname
$mailbox += (Get-MailboxStatistics -Identity $user).ItemCount
$mailbox += (Get-MailboxStatistics -Identity $user).TotalItemsize
$mailbox += (Get-MailboxStatistics -Identity $user).DeletedItemCount
$mailbox += (Get-MailboxStatistics -Identity $user).TotalDeletedItemsize
$permission = (Get-MailboxPermission -Identity $user).user

$ExchVersion = (Get-mailbox $user).Exchangeversion
if($ExchVersion -like “0.20 (”)
$version = “Exchange2013”
$version = “Exchange2010”

$mailbox = $mailbox.TrimEnd(",")
$mailbox +";"+$version + “;” +$permission | out-file $outputfile -Append

The reason you’re seeing everything dumped into a single cell is because with the += you’re effectively concatenating each output into a single string. A better way to handle this would be to create an object and add each of your queries as a property of that object.

$users = GC "c:\temp\alias.txt"
foreach ($user in $users)
    $Mailbox = Get-Mailbox $user
    $MailboxStat = Get-MailboxStatistics $user
    $MailboxPermission = Get-MailboxPermission $User
    ForEach ($Account in $MailboxPermission)
            $Accounts += $Account.User + " " | ? { $_ -notmatch "S-1-5-21" }
    $ExchangeVersion = $Mailbox.ExchangeVersion
    if($ExchangeVersion -like "0.20 (")
            $version = "Exchange2013"
            $version = "Exchange2010"

    $obj = New-Object PSObject
    $obj | Add-Member -MemberType NoteProperty -Name Alias -Value $Mailbox.Alias
    $obj | Add-Member -MemberType NoteProperty -Name LitigationHoldEnabled -Value $Mailbox.LitigationHoldEnabled
    $obj | Add-Member -MemberType NoteProperty -Name PrimarySMTPAddress -Value $Mailbox.PrimarySMTPAddress
    $obj | Add-Member -MemberType NoteProperty -Name Displayname -Value $Mailbox.Displayname
    $obj | Add-Member -MemberType NoteProperty -Name ItemCount -Value $MailboxStat.ItemCount
    $obj | Add-Member -MemberType NoteProperty -Name TotalItemsize -Value $MailboxStat.TotalItemsize
    $obj | Add-Member -MemberType NoteProperty -Name DeletedItemCount -Value $MailboxStat.DeletedItemCount
    $obj | Add-Member -MemberType NoteProperty -Name TotalDeletedItemsize -Value $MailboxStat.TotalDeletedItemsize
    $obj | Add-Member -MemberType NoteProperty -Name MailboxPermission -Value $Accounts

    $obj | Export-CSV "c:\temp\file.csv" -Append -NoTypeInformation

As for the mailbox and dumpster sizes, the values returned by TotalItemSize and TotalDeletedItemSize are strings (I believe), not numbers, and therefor cannot be used in calculation. I’m sure if there is a way to convert a string to a number someone else here can answer that. I’m not sure how to color cells/rows in Excel either, but I imagine that wouldn’t be too difficult.

Hope that helps!

  • Joshua

Hi Joshua,

Perfect script. Thank you so much.

When we run the script for the second time the previous output is being stored. Is the any way to clear the output file before running the script.

Thank you again.


What I typically do is check for the file and delete it if it exists, like this.

If (Test-Path c:\filepath)
    Remove-Item c:\filepath

Append that to the beginning of your script. That way the file is created new everytime the script is run.

  • Joshua