Formatting a .csv for a report

PowerShell Help
1

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!

2

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

3

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.

4

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
5

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. :slight_smile:

$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

6 
$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
7

Hi

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

Jake

8

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!