none
Output as .csv RRS feed

  • Pertanyaan

  • I have a  .csv file that lists Teams, owners and members which looks like this


    DisplayName : TeamSite1
    Members     :member1email
    Members     :member2email
    Owner       : owneremail


    I'm trying to change the output so it looks like this , with three columns (DisplayName, Member, Owner)

    
    DisplayName,  Member,  Owner
    TeamSite1 , member2email, owneremail
    TeamSite1,  member1email, owneremail


    I'm new to powershell, i think i need to  search through the file for the first instance of "Displayname:     " ,  add a column to the right with the email address to the right of line 2  "Members:    " and add that email to a new Member column. Then go on to the next line and repeat until i get to the owner and then put that in the third column. Then repeat for all the Teams in the file

    Any pointers or solution much appreciated!



     





    • Diedit oleh Jude Lymn Selasa, 28 Januari 2020 19.13
    Selasa, 28 Januari 2020 19.11

Jawaban

  • If the "Owner" followed the "DisplayName", and then the "Members" followed the "Owner" in the input it'd be a lot easier. :-)

    Here's one way to accomplish your data transformation (note that there's no checking to see if the required displayname, members, and owner tags appear in every set!):

    $hash = @{
        DisplayName = ''
        Members     = ''
        Owner       = ''
    }
    $stuff = @()
    Get-Content c:\junk\xxx.txt |
        foreach {
            if ($_ -match "^(.+):(.+)$" ) {
                $k = $matches[1].Trim()
                $v = $matches[2].Trim()
            
                if ($k -eq 'Owner') {
                    $stuff |                    # stuff the owner into each object
                        foreach {
                            $_.Owner = $v
                            Write-Output $_     # emit the data
                        }
                    $hash.DisplayName = ""      # and clear the hash values
                    $hash.Members = ""          # note that the "Owner" data is never stored in the hash, it's just a trigger
                    $stuff = @()                # set the array to empty
                }
                elseif ($k -eq 'DisplayName') {
                    $hash.DisplayName = $v
                }
                elseif ($k -eq 'Members') {
                    $hash.Members = $v
                    $stuff += [PSCustomObject]$hash
                    $hash.Members = ""         # keep the display name, just erase the "members"
                }
                    
            }
        } | export-csv c:\junk\xxx.csv -NoTypeInformation


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Ditandai sebagai Jawaban oleh Jude Lymn Rabu, 29 Januari 2020 14.25
    Selasa, 28 Januari 2020 21.16

Semua Balasan

  • What is your code?  To get a table use "Format-Table"

    A CSv file does not have lines it has rows and fields.  Csv files are not PowerShell they are an industry data standard.  Look up what a Csv file is to learn how to work with Csv files.


    \_(ツ)_/


    Selasa, 28 Januari 2020 19.14
    Moderator
  • If the "Owner" followed the "DisplayName", and then the "Members" followed the "Owner" in the input it'd be a lot easier. :-)

    Here's one way to accomplish your data transformation (note that there's no checking to see if the required displayname, members, and owner tags appear in every set!):

    $hash = @{
        DisplayName = ''
        Members     = ''
        Owner       = ''
    }
    $stuff = @()
    Get-Content c:\junk\xxx.txt |
        foreach {
            if ($_ -match "^(.+):(.+)$" ) {
                $k = $matches[1].Trim()
                $v = $matches[2].Trim()
            
                if ($k -eq 'Owner') {
                    $stuff |                    # stuff the owner into each object
                        foreach {
                            $_.Owner = $v
                            Write-Output $_     # emit the data
                        }
                    $hash.DisplayName = ""      # and clear the hash values
                    $hash.Members = ""          # note that the "Owner" data is never stored in the hash, it's just a trigger
                    $stuff = @()                # set the array to empty
                }
                elseif ($k -eq 'DisplayName') {
                    $hash.DisplayName = $v
                }
                elseif ($k -eq 'Members') {
                    $hash.Members = $v
                    $stuff += [PSCustomObject]$hash
                    $hash.Members = ""         # keep the display name, just erase the "members"
                }
                    
            }
        } | export-csv c:\junk\xxx.csv -NoTypeInformation


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Ditandai sebagai Jawaban oleh Jude Lymn Rabu, 29 Januari 2020 14.25
    Selasa, 28 Januari 2020 21.16
  • Yes you're right, it's just a .csv file

    This is the original code from https://blog.it-koehler.com/en/Archive/1763

    It outputs to a table and i just added a line to output to .csv


    #get all groups created with ms teams (filtered ExchangeProvisioningFlag:481)
    #raw powershell command: Get-UnifiedGroup | Where-Object {$_.ProvisioningOption -eq "ExchangeProvisioningFlags:481" } | fl
    $teamsgroups = (Get-UnifiedGroup | Select-Object DisplayName,Alias,ProvisioningOption,SharePointSiteUrl,SharePointDocumentsUrl,AccessType,Language,ExchangeGuid,ManagedBy) 
    #notification for powershell
    Write-Host "Getting Office 365 Groups created with MS Teams... " -ForegroundColor Green
    #generate array for teamsgroups
    $teams = @()
    #loop to get information

    ForEach ($group in $teamsgroups){
      #get-members of group
      $identity = (($group).ExchangeGuid)
      $members = (Get-UnifiedGroupLinks -Identity "$identity" -LinkType Members | Select-Object PrimarySMTPAddress) 
      $owners = (Get-UnifiedGroupLinks -Identity "$identity" -LinkType Owners | Select-Object PrimarySMTPAddress)   
      $member = (($members).primarySMTPAddress) | Out-String -Width 4096
      $owner = (($owners).primarySMTPAddress) | Out-String
      # Adding pscustomobjets entries to array
      $teams += [pscustomobject]@{
        DisplayName   = ($group).DisplayName
        Alias    = ($group).Alias
        AccessType = ($group).AccessType
        Language = ($group).Language
        Members = ("$member")
        Owner = ("$owner")
      }
    }

    #$teams | Format-Table -AutoSize -Wrap  
    $teams | Out-GridView -Title "All Office365 Groups created in MS Teams" 
    #$teams | Format-Table -AutoSize -Wrap  

    #$teams | output-csv "c:\temp\teams.csv"

    It outputs to look like this 

    DisplayName , Name
    Alias       , Alias
    AccessType  , Public
    Language    , en-GB
    Members     , email


    • Diedit oleh Jude Lymn Rabu, 29 Januari 2020 09.48
    Rabu, 29 Januari 2020 09.47
  •  I just tried this and is seems to be working great. Thanks very much!
    Rabu, 29 Januari 2020 09.54
  • Post a sample of the Csv.  Nothing you have posted tells us what it is.  YOU have claimed that two different things are Csv files.  

    \_(ツ)_/

    Rabu, 29 Januari 2020 10.19
    Moderator
  • I can't upload files, but as i said before..

    the output is a .csv that looks like this..

    DisplayName , Name 

    Alias       , Alias
    AccessType  , Public
    Language    , en-GB
    Members     , email

    Rabu, 29 Januari 2020 10.29
  • sorry for the  basic question but how does this section work? I see it searches for the : delimter, but what else does it do?

       foreach {
            if ($_ -match "^(.+):(.+)$" ) {
                $k = $matches[1].Trim()
                $v = $matches[2].Trim()

    I need to do some research on what a hash value is.. 

    But it's working great, thanks

    Rabu, 29 Januari 2020 10.32
  • The regex "memorizes" the data on either side of the colon. If the line of data doesn't match the pattern the line is skipped (because the result of the -match operator will return a value of $False.

    The memorized data (taken from the built-in $matches object) is then trimmed of leading and trailing space and placed into variables $k (mean as a mnemonic for "key") and $v (a mnemonic for "value").

    In the regex, the "^" and "$" are anchors, denoting the beginning and end of the string being matched.

    The -match and regex could be replaced by this:

    $a = $_ -split(":")
    if ($a.count -eq 2){
        $k = $a[0].Trim()
        $v = $a[1].Trim()
        etc.
        etc.
        etc.
    }

    Or this, if your an aficionado of parenthesis or obfuscation :-)

    if ( (($a = $_ -split(":")).count -eq 2)){
        $k = $a[0].Trim()
        $v = $a[1].Trim()
        .
        .
        .
    }

     

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Rabu, 29 Januari 2020 16.05
  • I can't upload files, but as i said before..

    the output is a .csv that looks like this..

    DisplayName , Name 

    Alias       , Alias
    AccessType  , Public
    Language    , en-GB
    Members     , email

    Doesn't look like a Csv to me.


    \_(ツ)_/

    Rabu, 29 Januari 2020 17.40
    Moderator