Formatting a .csv for a report

Welcome Forums General PowerShell Q&A Formatting a .csv for a report

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

2 years ago.

  • Author
  • #63271

    Points: 0
    Rank: Member

    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,ZZTOP,Self Service
    UserA,Wizard,Support Role
    UserB,CAT,Meow Meow
    UserB,General,General End User
    UserB,ZZTOP,Self Service
    UserB,Wizard,Support Role
    UserC,CAT,Meow Meow
    UserC,General,General End User
    UserC,ZZTOP,Self Service
    UserC,Wizard,Support Role
    UserD,CAT,Meow Meow
    UserD,General,General End User
    UserD,ZZTOP,Self Service
    UserD,Wizard,Support Role
    UserE,SECURITY2,More Secure
    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

    Points: -11
    Rank: Member


    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


    • #63367

      Points: 0
      Rank: Member

      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
      General,General End User,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

    Points: 503
    Helping Hand
    Rank: Major Contributor

    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}}


    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

    Points: -11
    Rank: Member


    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"
            } #IF
                ELSE {
                } #ELSE
    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                                                                                                                                                                        


  • #63384

    Points: 17
    Rank: Member
    $group = Import-Csv "C:\Data.csv" | Group-Object -Property Role
    $result = 
    foreach ($g in $group){
            Role = $
            'Role Description' = $g.Group.'Role Description' | 
                Select-Object -First 1
            Users = $g.Group.user -join ','}
    $result | Export-Csv "C:\DataResult.csv" -NoTypeInformation
    • #63394

      Points: 0
      Rank: Member

      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

    Points: -11
    Rank: Member


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


The topic ‘Formatting a .csv for a report’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort