I'm Importing a CSV file and reading a column that look like this

Exchange Mailboxes
Include:[[email protected]] 
Include:[[email protected]]

Include:[[email protected]]

I use Get-EXOMailbox to get their DisplayName and Id. After that I'm trying to pass it in my New-Object like below so that I can export it. The problem I have is when I look at my Excel file, it showing System.Object[] on every row instead of showing each actual DisplayName and Id.

Any help on how to display it correctly would be really appreciated.

         $result =     Import-Csv "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv" |
              Where-Object { -join $_.psobject.Properties.Value } |
              ForEach-Object { 
                $exoMailbox = ($_.'Exchange Mailboxes' -split '[][]')[1]
                $exoUser = Get-EXOMailbox -Filter "PrimarySmtpAddress -eq  '$exoMailbox'"
                # Construct and output a custom object with the properties of interest.
                [pscustomobject] @{  
                  UserName = $exoUser.DisplayName
                  UserId = $exoUser.Identity
                }
              }




        New-Object PsObject -Property @{
            'Searched User'           = $result.UserName   //I'm trying to pass here
            'SharePoint URL'          = $spUrl
            'Searched User GMID'      = $result.UserId      //and here
            'Site Owner'              = $spositeOwner
            User                      = $u.User
            "Result Status"           = $u."Result Status"
            "Date & Time"             = $u."Date & Time"
            "Search Conditions"       = $u."Search Conditions"
            "SharePoint Sites"        = $u."SharePoint Sites"
            "Exchange Public Folders" = $u."Exchange Public Folders"
            "Exchange Mailboxes"      = $u."Exchange Mailboxes".Split([char[]]@('[', ']'))[1]
            "Case Name"               = $u."Case Name"     
            "Search Criteria"         = $u."Search Criteria"
            "Record Type"             = $u."Record Type"
            "Hold Name"               = $u."Hold Name".Split(('\'))[1]
            "Activity"                = if ($null -ne ($importData | where-object { $_.Name -eq $u."Activity" }).Value) { ($importData | where-object { $_.Name -eq $u."Activity" }).Value }
            else { $u."Activity" }
        }  | Select-object -Property User, "Date & Time", "Case Name", "Hold Name", "Record Type", "Activity" , "Searched User", "Searched User GMID", "SharePoint URL", "Exchange Mailboxes", "Exchange Public Folders" , "Search Criteria", "Result Status"
    }
    
    $xlsx = $result | Export-Excel @params
    $ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
    $ws.Dimension.Columns
    $ws.Column(1).Width = 20
    $ws.Column(2).Width = 20
    $ws.Column(3).Width = 15
    $ws.Column(4).Width = 15
    $ws.Column(5).Width = 15
    $ws.Column(6).Width = 160
    $ws.View.ShowGridLines = $false
    Close-ExcelPackage $xlsx

CodePudding user response：

$result is an array of objects, containing an object for each non-empty row in your input CSV; thus, adding values such as $result.UserName to the properties of the object you're creating with New-Object will be arrays too, which explains your symptom (it seems that Export-Excel, like Export-Csv doesn't meaningfully support array-valued properties and simply uses their type name, System.Object[] during export).

It sounds like the easiest solution is to add the additional properties directly in the ForEach-Object call, to the individual objects being constructed and output via the existing [pscustomobject] literal ([pscustomobject] @{ ... }):

$result = 
  Import-Csv "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv" |
  Where-Object { -join $_.psobject.Properties.Value } | # only non-empty rows
  ForEach-Object { 

    $exoMailbox = ($_.'Exchange Mailboxes' -split '[][]')[1]
    $exoUser = Get-EXOMailbox -Filter "PrimarySmtpAddress -eq  '$exoMailbox'"

    # Construct and output a custom object with the properties of interest.
    [pscustomobject] @{  
      UserName = $exoUser.DisplayName
      UserId   = $exoUser.Identity
      # === Add the additional properties here:
      'Searched User'           = $exoUser.UserName 
      'SharePoint URL'          = $spUrl
      'Searched User GMID'      = $exoUser.UserId 
      'Site Owner'              = $spositeOwner
      # ...
    }

  }

Note:

• The above shows only some of the properties from your question; add as needed (it is unclear where $u comes from in some of them.

• Using a custom-object literal ([pscustomobject] @{ ... }) is not only easier and more efficient than a New-Object PSObject -Property @{ ... }^[1] call, unlike the latter it implicitly preserves the definition order of the properties, so that there's no need for an additional Select-Object call that ensures the desired ordering of the properties.

^{[1] Perhaps surprisingly, PSObject ([psobject]) and PSCustomObject ([pscustomobject]) refer to the same type, namely System.Management.Automation.PSObject, despite the existence of a separate System.Management.Automation.PSCustomObject, which custom-objects instances self-report as (([pscustomobject] @{}).GetType().FullName) - see GitHub issue #4344 for background information.}