# Formatting a .csv for a report **URL:** \ **Category:** PowerShell Help\ **Created:** [February 1, 2017, 4:40pm UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972 "2017-02-01T16:40:08Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![coltpale](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coltpale](https://forums.powershell.org/u/coltpale)\ **Post date:** [February 1, 2017, 4:40pm UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/1 "2017-02-01T16:40:08Z")
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! ---
**Author:** ![jarkko](https://avatars.discourse-cdn.com/v4/letter/j/eb8c5e/32.png) [@jarkko](https://forums.powershell.org/u/jarkko)\ **Post date:** [February 2, 2017, 11:03am UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/2 "2017-02-02T11:03:40Z")
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 ---
**Author:** ![coltpale](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coltpale](https://forums.powershell.org/u/coltpale)\ **Post date:** [February 2, 2017, 11:13am UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/3 "2017-02-02T11:13:14Z")
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. ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [February 2, 2017, 11:54am UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/4 "2017-02-02T11:54:35Z")
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 ``` ---
**Author:** ![jarkko](https://avatars.discourse-cdn.com/v4/letter/j/eb8c5e/32.png) [@jarkko](https://forums.powershell.org/u/jarkko)\ **Post date:** [February 2, 2017, 11:54am UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/5 "2017-02-02T11:54:39Z")
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 ---
**Author:** ![random-commandline](https://avatars.discourse-cdn.com/v4/letter/r/e56c9b/32.png) [@random-commandline](https://forums.powershell.org/u/random-commandline)\ **Post date:** [February 2, 2017, 11:58am UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/6 "2017-02-02T11:58:32Z")
``` $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 ``` ---
**Author:** ![jarkko](https://avatars.discourse-cdn.com/v4/letter/j/eb8c5e/32.png) [@jarkko](https://forums.powershell.org/u/jarkko)\ **Post date:** [February 2, 2017, 12:02pm UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/7 "2017-02-02T12:02:08Z")
Hi Nice Random commandline! Shows once again that PS should do all the work. Jake ---
**Author:** ![coltpale](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@coltpale](https://forums.powershell.org/u/coltpale)\ **Post date:** [February 2, 2017, 12:20pm UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/8 "2017-02-02T12:20:02Z")
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! ---
**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\ **Post date:** [May 16, 2024, 8:39pm UTC](https://forums.powershell.org/t/formatting-a-csv-for-a-report/7972/9 "2024-05-16T20:39:20Z")