Skip to main content

Microsoft

How to Combine PowerShell Cmdlet Output in a Single CSV File

If you have been working with Office 365, you have probably been extracting mailbox data and exporting it to CSV for various reporting tasks. You may have also gone further and attempted to pipeline data from multiple Cmdlets into Export-CSV. Let’s use the following scriptlet as an example:

Get-Mailbox Test.user1@company.com | Get-MailboxStatistics | Select Alias,Displayname,TotalItemSize,ArchiveStatus | Export-CSV C:\test.csv

Now the problem with the previous scriptlet is that the data from the first command Get-Mailbox will not be displayed in the CSV output, and only the Get-MailboxStatistics data is shown. In the following image, notice that the Alias and Archivestatus columns are blank:
tressel_excel
So how can we combine multiple Cmdlets’ output data into one CSV file? The following snippet stores data from Get-Msoluser, Get-MailboxStatistics, and Get-Mailbox Cmdlets using a hash table and an array, before exporting to CSV. For the sake of brevity, I will assume that you already have PowerShell V2, you are connected via Remote PowerShell to Exchange Online, and the Microsoft Online Service Module before using this snippet.

$DataPath = "C:\o365UserData.csv"
$Results = @()
$MailboxUsers = get-mailbox -resultsize unlimited
foreach($user in $mailboxusers)
{
$UPN = $user.userprincipalname
$License = Get-MsolUser -userprincipalname $UPN
$MbxStats = Get-MailboxStatistics $UPN
      $Properties = @{
      Name = $user.name
      UPN = $UPN
      Alias = $user.alias
      UsageLocation = $user.usagelocation
      License = $License.Licenses[0].AccountSkuId
      ArchiveStatus = $user.archivestatus
      Server = $MbxStats.servername
      DatabaseInstructions = $MbxStats.databasename
      TotItemSize = $MbxStats.totalitemsize
      }
$Results += New-Object psobject -Property $properties
}
$Results | Select-Object Name,UPN,Alias,UsageLocation,License,ArchiveStatus,Server,DatabaseInstructions,TotItemSize | Export-Csv -notypeinformation -Path $DataPath

Now let’s take a look at how our CSV file looks using this new method (separated into two pictures):
excel5
excel6
You can see that the new CSV file contains the missing data from the first attempt, as well as more data from the 3 Cmdlets.

Thoughts on “How to Combine PowerShell Cmdlet Output in a Single CSV File”

  1. Great script!. The first real working one with a .csv output I could find.
    I altered it a little (used a .txt file with a select group of users as input, only needed the alias and the totalitemsize and wanted the output only in MB’s) but it all works fine now. Thnx.

  2. Mines very close, however doesn’t work fully. Do you know if there is an issue using a Win32_Product along side others? I can see information using Win32_ComputerSystem, however not the information using Win32_Product using this method of exporting to a csv. To an unchanged txt file no problem, formatted csv, no worky.

  3. Any idea on why my column for the user’s E-Mail address shows this output:
    Mail
    —-
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    Where as the other columns fully displays:
    SamAccountName Name
    ————– —-
    Outside Users Kevin Frankiesh
    Outside Users David Letterman
    Outside Users Kevin Frankiesh
    Outside Users Dina Pugliese
    STAFF-EMPLOYEES Kevin James
    STAFF-EMPLOYEES Jerry Brown
    STAFF-EMPLOYEES Maria Benedi
    STAFF – PARTY EVENTS Arnold Schwarzenegger
    STAFF – PARTY EVENTS Paul Muller
    STAFF – PARTY EVENTS Stephanie Sebastaini
    STAFF-EXTERNAL Kevin Frankiesh
    STAFF-EXTERNAL Dina Pugliese
    STAFF-EXTERNAL Jerry Brown
    STAFF-EXTERNAL Pamela Anderson
    STAFF-SECURITY Jerry Brown
    STAFF-SECURITY Stephanie Sebastaini
    STAFF-SECURITY Pamela Anderson
    Please advise.
    Thanks,
    Adam

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Tim Tressel

More from this Author

Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram