Formatting a .csv for a report

This topic contains 7 replies, has 4 voices, and was last updated by Profile photo of Colter Colter 1 month, 2 weeks ago.

  • Author
    Posts
  • #63271
    Profile photo of Colter
    Colter
    Participant

    Hey Folks,

    I've been stumped on this one. I need to read a .csv and output the data into a format that our access review tool can use.

    Here is an example of the data:

    User,Role,Role Description
    UserA,CAT,Meow Meow
    UserA,General,General End User
    UserA,Help,Helpdesk
    UserA,ZZTOP,Self Service
    UserA,Wizard,Support Role
    UserB,CAT,Meow Meow
    UserB,General,General End User
    UserB,Help,Helpdesk
    UserB,ZZTOP,Self Service
    UserB,Wizard,Support Role
    UserC,CAT,Meow Meow
    UserC,General,General End User
    UserC,Help,Helpdesk
    UserC,ZZTOP,Self Service
    UserC,Wizard,Support Role
    UserD,CAT,Meow Meow
    UserD,General,General End User
    UserD,Help,Helpdesk
    UserD,ZZTOP,Self Service
    UserD,Wizard,Support Role
    UserE,SECURITY1,Secure
    UserE,SECURITY2,More Secure
    UserE,EXEMPT,Exempts
    UserE,General,General End User
    UserE,ZZTOP,Self Service

    The output needs to read:
    CAT,Meow Meow,UserA,UserB,UserC,UserD
    (Role, Role Description, User, User, User, etc)

    Here is what I have so far, but a line is being outputted for each user. How could I adjust this to match what the output needs to be?

    $Data = Import-Csv "C:\Data.csv"
    $UniqueRoles = Import-Csv "C:\Data.csv" | Select-Object Role | Sort-Object Role -Unique
    
    Foreach($R in $UniqueRoles){
        ForEach ($D in $Data){
            If ($R.Role -eq $D.Role){
    $1 = $R.Role + "," + $D."Role Description" + ",$($D.User)"
            }
        }
    $1 | out-file "C:\report.txt" -Append
    }
    
    

    Any help is greatly appreciated. Thanks!

  • #63360

    Hi

    How you want the output to be? At your script you remove everything else than Role with "Select-object Role", if you want to keep other values also, remove that.

    Following outputs txt file with unique roles and other values.

    $Data = Import-Csv "C:\temp\Data.csv" -Delimiter ',' | Sort-Object Role -Unique
    
    Foreach($R in $Data){
    "$($R.Role),$($R."Role Description"),$($D.User)" | out-file "C:\temp\report1.txt" -Append
    
    }
    

    Jake

    • #63367
      Profile photo of Colter
      Colter
      Participant

      Hi Jarkko,

      The output needs to be

      role,role description, then each user with that role delimited by comma

      So for the "CAT" role the output would look like this:
      CAT,Meow Meow,UserA,UserB,UserC,UserD

      My output currently looks like this:

      CAT,Meow Meow,UserD
      EXEMPT,Exempts,UserE
      General,General End User,UserE
      Help,Helpdesk,UserD
      SECURITY1,Secure,UserE
      SECURITY2,More Secure,UserE
      Wizard,Support Role,UserD
      ZZTOP,Self Service,UserE

      The problem I am running into is that the output is only displaying one of the users.

  • #63378
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Try something like this:

    $desktop = [environment]::GetFolderPath("Desktop")
    $data = Import-CSV ("{0}\Archive\test.csv" -f $desktop)
    
    $roleGroup = $data |
    Group-Object -Property "Role" |
    Sort-Object -Property Count -Descending
    
    $report = foreach ($role in $roleGroup) {
        $role | Select Name, Count, @{Name="Users";Expression={$role.Group | Select -ExpandProperty User}}
    }
    
    $report
    

    Output:

    Name      Count Users                          
    ----      ----- -----                          
    General       5 {UserA, UserB, UserC, UserD...}
    ZZTOP         5 {UserA, UserB, UserC, UserD...}
    CAT           4 {UserA, UserB, UserC, UserD}   
    Help          4 {UserA, UserB, UserC, UserD}   
    Wizard        4 {UserA, UserB, UserC, UserD}   
    SECURITY1     1 UserE                          
    SECURITY2     1 UserE                          
    EXEMPT        1 UserE  
    
  • #63381

    Hi

    This will get you where output is role and users, but description is missing. Let's check how we could add that there also. 🙂

    $importedData = Import-Csv "C:\temp\Data.csv" -Delimiter ','
    
    $AllData = @{}
    
    ForEach ($u in $importedData) {
    
        $Role = $u.role
        $User = $u.user
    
            IF ($AllData.ContainsKey("$Role")) {
    
                $oldValue = ($AllData.GetEnumerator() | where {$_.Name -eq $Role}).Value
                $newValue = "$oldValue,$User"
    
                $AllData.set_Item("$Role","$newValue")
            } #IF
    
                ELSE {
    
                    $AllData.Add("$Role","$User")
    
        
                } #ELSE
    
    }
    $AllData
    
    Name                           Value                                                                                                                                                                                                
    ----                           -----                                                                                                                                                                                                
    Wizard                         UserA,UserB,UserC,UserD                                                                                                                                                                              
    SECURITY2                      UserE                                                                                                                                                                                                
    SECURITY1                      UserE                                                                                                                                                                                                
    CAT                            UserA,UserB,UserC,UserD                                                                                                                                                                              
    EXEMPT                         UserE                                                                                                                                                                                                
    Help                           UserA,UserB,UserC,UserD                                                                                                                                                                              
    ZZTOP                          UserA,UserB,UserC,UserD,UserE                                                                                                                                                                        
    General                        UserA,UserB,UserC,UserD,UserE                                                                                                                                                                        
    
    
    

    Jake

  • #63384
    Profile photo of random commandline
    random commandline
    Participant
    $group = Import-Csv "C:\Data.csv" | Group-Object -Property Role
    
    $result = 
    foreach ($g in $group){
        [PSCustomObject]@{
            Role = $g.name
            'Role Description' = $g.Group.'Role Description' | 
                Select-Object -First 1
            Users = $g.Group.user -join ','}
    }
    
    $result | Export-Csv "C:\DataResult.csv" -NoTypeInformation
    
    • #63394
      Profile photo of Colter
      Colter
      Participant

      Awesome, Thanks everyone.

      random commandline, your script output the users in a single set of quotation "UserA,UserB,UserC,UserD", but I need them each to be in quotation "UserA","UserB","UserC","UserD". or no quotations at all, but I think I can figure this one out.

      Thanks again!

  • #63387

    Hi

    Nice Random commandline! Shows once again that PS should do all the work.

    Jake

You must be logged in to reply to this topic.