powershell add column to existing csv file with Mailbox property

user_576952 0
Viewed 38

I'm currently running a script that obtains a list of Exchange Online mailboxes from a CSV file, and converts them to Shared Mailboxes. I then have the script waiting for a period of time, before looping through the CSV again and extracting the type of Mailbox it is. It's a check to confirm that the mailbox has actually converted successfully. Finally, I want it to add a new column to the CSV file with the mailbox type.

Currently, it all seems to work correctly, except writing back to the CSV. The CSV ends up blank.

$SMTP = import-csv -Path .\Convert.csv | & {
    process {
        Set-Mailbox -Identity $_.FullAddress -Type Shared -MessageCopyForSentAsEnabled $True -MessageCopyForSendOnBehalfEnabled $True -ErrorAction Continue -WarningAction Continue
    }
    
}

Start-Sleep -Seconds 15
    
$SMTP = import-csv -Path .\Convert.csv | & {
    process {
        $MBType = Get-EXOMailbox -Identity $_.FullAddress | Select RecipientTypeDetails
        Select-Object *,@{Name='Type';Expression={$MBType}} |
    
        Export-CSV -Path .\Convert.csv -NoTypeInformation
    }
}

Initially I thought it's because it can't write to a file that is open, but when exporting to a new CSV, the result is still a blank file.

Thanks in advance for any assistance.

csv powershell
1 Answers

Try the following:

(Import-Csv .\Convert.csv) | 
  Select-Object *, 
                @{
                  Name = 'Type'
                  Expression = { (Get-EXOMailbox -Identity $_.FullAddress).RecipientTypeDetails } 
                } |
  Export-CSV -Path .\Convert.csv -NoTypeInformation

  • (...) around the Import-Csv call ensures that the input file is read in full into memory first, which is the prerequisite for writing back to the same file in the same pipeline.

    • That said, if you change your first pipeline to ($SMTP = import-csv -Path .\Convert.csv) | ..., you can use $SMTP as the input to your second one, in lieu of (Import-Csv .\Convert.csv)

  • You can pipe directly to Select-Object with the added calculated property.

As for what you tried:

  • Your Select-Object call is missing input, which should have been: $_ | Select-Object ... in order to process the input object at hand (as reflected in automatic $_ variable).

  • Without input, Select-Object produces no output, and therefore Export-Csv produced an empty file.

    • Aside from that, the Export-Csv should be in a subsequent pipeline segment, so as to receive all objects to write to the file, as shown above.
user_453750
Related