Adding object property array values to individual csv cells

Welcome Forums General PowerShell Q&A Adding object property array values to individual csv cells

This topic contains 4 replies, has 2 voices, and was last updated by

 
Participant
1 year ago.

  • Author
    Posts
  • #89266

    Participant
    Points: 2
    Rank: Member

    Script goal: Get user mailbox properties and export each value of the 'EmailAddresses' property array to its own cell in a csv.

    This is one of my first experiences working with custom psobjects and property arrays so naturally I came across a ton of road blocks on the road to getting this to work. It was quite the arduous experience getting these the values of a property array into their own cells in a csv. My question is, if anyone has come across this need in their environment, how have you solved it and please notify me if it would appear I am not taking advantage of something powershell has got to offer.

    $mailboxes = Get-Mailbox | Select-Object Name, Identity, RecipientTypeDetails, EmailAddresses -First 20
    
    $modArray = @()
    $emailCount = @()
    Foreach ($a in $mailboxes) {
        # Regex = Exclude any item that matches 'smtp/SMTP' 
        [regex]$regex = "^(?i)smtp"
        # Convert $a.Emailaddresses to an Array list to circumvent "collection was of a fixed size" error
        [System.Collections.ArrayList]$arrayList = $a.EmailAddresses
        # Find emails in $arrayList that dont start with smtp/SMTP and stores them in $remEmails
        $remEmails = $arrayList | Where-Object {$_ -notmatch $regex} 
        # iterate through $arrayList items for removal from array 
        For ($i = 0; $i -lt $remEmails.Count; $i++) {
            If ($remEmails.Count -gt 1) {
                $arrayList.Remove($remEmails[$i])
            }
            Else {
                $arrayList.Remove($remEmails)
            }
        }
        # Add $a into $modArray with its new $a.EmailAddresses values
        $modArray += $a
        # Store email address count of each mailbox. See more info in comment above $count var
        $emailCount += $a.EmailAddresses.Count
    }
    
    # Find the largest number in the $emailCount array
    # This is used to set the value for the amount of additional properties to add to the psObject
    # Not desireable but necessary to be able to export-csv all the properties
    $count = $emailCount | Measure-Object -Maximum | Select-Object -ExpandProperty Maximum
    
    $result = @()
    ForEach ($m in $modArray) {
        # Create new psObject. [ordered] was a life saver because hashtables dont retain property order.
        $obj = New-Object psobject -Property ([ordered]@{
    
                Name     = $m.Name
                Identity = $m.Identity
                Mailtype = $m.RecipientTypeDetails
                Email    = $m.EmailAddresses | Where-Object {$_ -clike "SMTP:*"}
            })
        # Create new alias properties based on the number stored in $count variable
        For ($i = 0; $i -lt $count; $i++) {
            $obj | Add-Member -MemberType NoteProperty -Name "Alias_$i" -Value $null
        }
        
        # Adding the values to the Alias properties
        If ($m.EmailAddresses.Count -gt 1) {
            $addr = $m.EmailAddresses -cnotlike "SMTP:*"
            For ($i = 0; $i -lt $addr.Count; $i++) {
                $addrValue = "Alias_$i"
                $obj.$addrValue = $addr[$i]
            }
        }
        Else {
            $obj = New-Object psobject -Property ([ordered] @{
                
                    Name     = $m.Name
                    Identity = $m.Identity
                    Mailtype = $m.RecipientTypeDetails
                    Email    = $m.EmailAddresses | Where-Object {$_ -clike "SMTP:*"}
                })
        }
        # Add $obj to $result array
        $result += $obj 
        $obj = $null
    }
    $result | Export-Csv $env:USERPROFILE\Desktop\Users.csv -NoTypeInformation
    
  • #89287

    Participant
    Points: 332
    Helping Hand
    Rank: Contributor

    I've never needed to approach this use case as you have, nor anyone else to date, and I would have kept the emails in one column.

    However, PoSH is all about discovery and experimentation. There are always different ways to accomplich the same tasks. Some more elegant than others.

    What you have here works, and that's fine for you then all else is really another opinion. Such as the example I am going to show below. I took your use case and got the needed result with only half the code relative to your post. That does not make my approach better than yours, just different.

    There are common patterns and practices offered by many resources, but even they are not always followed, and some of those can run into hiccups and their own set of issues.

    No matter whom you connect with, one will always know a trick to two that you may not have known about or tried. Your code may provide edification for those who did not know or conceive of the way you completed your task. All things are beneficial. Meaning, making mistakes, is not a bad thing in general as long as
    you can recover for it. So plan ahead.

    Code will have mistakes, depending on the training, experience, mindset and opinions of others who see it.
    I follow many of the PoSH MVP's blogs, presentations, white papers, books and Microsoft TechNet, etc. Yet, those tow can be wrong and have mistakes, and I may not agree with all they say and do.

    Things I live by when coding:
    1 – Have you done all you can to make the code as tight as possible.
    2 – Have you fully documented in the code why you did the things they way you did them.
    3 – Have you fully explained what code segments really do, or shortcuts you've taken.
    4 – Is it really readable by any to whom you plan to share it with.
    5 – Is it production ready, and how are you version controlling it.
    6 – Is it as modular / reusable / extensible as possible
    7 – Will those who follow you be able to deal with it as whatever level is necessary.
    8 – Have you applied all risk management techniques needed to ensure the validity/veracity and execution of your code in the planned target environments.
    9 – Have you provided enough examples to help users fully understand how the code is to be used.

    Now, as for my code block below. It is just another way of doing the same thing, and since it's just for this forum exchange, much of the points above were not prudent. In the sample below, I have taken a different route than you did and wanted to keep the code to a minimum as much as I could. This is not necessarily any better than what you have posted, just another way of looking at your use case and the approach for it.
    I always try to generate as little code as possible to get a task done as well as avoid shorthand / alias names, which other may not know about or use. IMHO, it's easier to maintain, understand, less resource intensive, etc.
    This same is only specifying the currently logged on user, but of course you do as you have in your post for more users. Lastly, I would make this an advanced function as well. I always, work to use functions as a rule, no matter the length of the code.

    Clear-Host

    $UserMailData = Get-Mailbox `
    | Where Identity -Match $env:USERNAME `
    | Select-Object Name, Identity, RecipientTypeDetails -ExpandProperty EmailAddresses `
    | Select Name,Identity,RecipientTypeDetails,ProxyAddressString `
    | Group-Object Name | Select Group

    $UserMailObject = [PSCustomObject]@{
    Name = $UserMailData.Group.Name[0]
    Identity = ($UserMailData.Group.Identity[0] `
    | Select Parent,Name) -replace '@{Parent='," -replace '; Name=','/' -replace '}',"
    MailType = $UserMailData.Group.RecipientTypeDetails[0]
    }

    $Counter = $UserMailData.Group.ProxyAddressString.Count -1
    0..$Counter |
    %{
    $MailBoxType = [regex]::match($UserMailData.Group.ProxyAddressString[$_],'^(.*):').Groups[1].Value
    $UserMailObject |
    Add-Member -MemberType NoteProperty -Name "EmailAlias_$_-$MailBoxType" `
    -Value ($UserMailData.Group.ProxyAddressString[$_])
    }

    $UserMailObject | Format-List
    $UserMailObject | Format-Table -AutoSize -Wrap
    $UserMailObject | Out-GridView -Title 'Exchange mailbox user address report' -PassThru

    • #89312

      Participant
      Points: 2
      Rank: Member

      Postanote, thank you so much for your input, this is exactly the kind of response I was hoping for. The reason for the separation in columns is for client readability and it seemed like a task I could accomplish with relative ease but came with a lot of road blocks. I definitely appreciate the response as I am always look for new ways to accomplish tasks with less code while trying to make it as efficient as possible and I will be analyzing your code for a good part of the day now.

    • #89513

      Participant
      Points: 332
      Helping Hand
      Rank: Contributor

      I though about this a bit more and as I stated earlier, my normal pattern to coding ...

      '1 – Have you done all you can to make the code as tight as possible.'

      ... I re-looked at what I gave you and said, I can do better, and not use any unnecessary hacks.
      Just ask for the properties (and there values of course) and output them.

      Here is the result of a bit more clean up / fine tuning of what I gave you earlier. As you will note, you had all you needed in the first Select statement. All that was needed was to use PoSH native to get the end result you were after.

      Function Get-UserEmailReport
      {
      ForEach($UserEmailData in (Get-Mailbox | Select Name,Identity,RecipientTypeDetails,EmailAddresses))
      {
      $UserMailObject = [PSCustomObject]@{
      Name = $UserEmailData.Name | Select -Unique
      Identity = $UserEmailData.Identity | Select -Unique
      MailType = $UserEmailData.RecipientTypeDetails | Select -Unique
      }
      0..($UserEmailData.EmailAddresses.ProxyAddressString.Count) |
      %{$UserMailObject | Add-Member -MemberType NoteProperty -Name "EmailAlias_$_" -Value ($UserEmailData.EmailAddresses.ProxyAddressString[$_])
      }
      $UserMailObject
      }
      }
      Get-UserEmailReport | Out-GridView -Title 'Exchange mailbox user address report' -PassThru

  • #89384

    Participant
    Points: 332
    Helping Hand
    Rank: Contributor

    No worries, and please excuse all the typos. I am a completely self-taught typist and not very good at it anyway.

    Spellcheckers / grammar checkers often to weird things to my actions, well, that and it's just me not going back an double checking which I wrote before sending / posting. Many times, I look at old email and posts, and ask myself, 'WTH was that suppose to mean / say!?' 8^}

    Glad it was helpful though. take care.

The topic ‘Adding object property array values to individual csv cells’ is closed to new replies.