How to output foreach into CSV using Powershell?

Question

I am displaying all users in a group with this:

Import-Module ActiveDirectory

$Groups = Get-ADGroup -Filter {name -like "UC_*"} | Select -ExpandProperty Name   # UC_* is my group

foreach ($Group in $Groups){
Write-Output "Group" 
Write-Output "-----"
$Group
Write-Output ""
Get-ADGroupMember -Identity $Group | select -Property Name, samaccountname 
Write-Output ""
}

I want to output this to a CSV but when I do, it looks like each line is getting overwritten by the next line in the loop so there is no data in the CSV. This is not working properly:

# Get users in a group or groups

Import-Module ActiveDirectory


Function Get-Users {
    $Groups = Get-ADGroup -Filter {name -like "UC_*"} | Select -ExpandProperty Name   # UC_* is my group

    foreach ($Group in $Groups){
    Write-Output "Group" 
    Write-Output "-----"
    $Group
    Write-Output ""
    Get-ADGroupMember -Identity $Group | select -Property Name, samaccountname 
    Write-Output ""
    }
}

Get-Users | export-csv "c:\temp\myfile.csv"

How can I output all content to a CSV properly?

As an aside: using a script block (`{...}`) as the `-Filter` argument - even though it works in this _particular_ case - is a bad idea; see [this answer](https://stackoverflow.com/a/44184818/45375) (of mine). — mklement0, Jul 25 '17 at 21:35

BenH · Accepted Answer · 2017-07-25T21:40:03.140

The output of your function is Write-output's, $Group, and the objects returned from Get-ADGroupMember. This collection of different objects makes it not able to be exported by the Export-CSV, but would be something for a text document via Out-File.

If you want to export to a csv, you need to create a collection of consistent objects with the properties you want to export:

So we'll loop over each group with ForEach-Object, and store the group membership in $members. Then can loop over that with foreach, this means that we can still use the information from the ForEach-Object loop in the $_ to get the name of the group, and the user information in $member and create an object for every user with just the information that's needed by making a [pscustomobject]

Get-ADGroup -Filter "name -like 'UC_*'" |
    ForEach-Object {
        $members = Get-ADGroupMember $_
        foreach ($member in $members) {
            [pscustomobject]@{
                "Group Name"     = $_.Name
                "SamAccountName" = $member.SamAccountName
                "User Name"      = $member.name
            }
        }
    } |
    Export-Csv "c:\temp\myfile.csv"

Also as @mklement0 mentioned, it's best practice to not use scriptblock's with the filters on the AD cmdlets. His excellent answer here gives details why.

score 0 · Answer 2 · answered Jul 25 '17 at 20:43

0

How about this:

$Groups = Get-ADGroup -Filter {name -like "UC_*"} | Select -ExpandProperty Name   # UC_* is my group

$Groups | Export-Csv  "c:\temp\myfile.csv" -NoTypeInformation

answered Jul 25 '17 at 20:43

Hackerman

12,139
2
34
45

score 0 · Answer 3 · edited Jul 25 '17 at 21:22

0

There are two options for outputting to a CSV. Using Export-CSV will overwrite a file that is already there. So you can put everything into a variable and output that to a CSV or you can add -Append to the Export-CSV cmdlet call. This will append data rather than overwrite it.

edited Jul 25 '17 at 21:22

mklement0

382,024
64
607
775

answered Jul 25 '17 at 20:45

Jason Snell

1,425
12
22

I do want to overwrite it. – Prox Jul 25 '17 at 20:47

ZenoArrow · Answer 4 · 2017-07-25T21:53:28.043

Store the data you're parsing in your function in an object.

For example, change this...

$Groups = Get-ADGroup -Filter {name -like "UC_*"} | Select -ExpandProperty Name   # UC_* is my group

foreach ($Group in $Groups){
Write-Output "Group" 
Write-Output "-----"
$Group
Write-Output ""
Get-ADGroupMember -Identity $Group | select -Property Name, samaccountname 
Write-Output ""

... to something like this ...

$users = @()

$groups = Get-ADGroup -Filter {name -like "UC_*"} | Select -ExpandProperty Name

$groups | % {

    $group | New-Object System.Object
    $group | Add-Member -MemberType NoteProperty -Name GroupName -Value $_

        Get-ADGroupMember -Identity $_ | Select -Property Name, samaccountname | % {

$group | Add-Member -MemberType NoteProperty -Name MemberName -Value $_.Name
$group | Add-Member -MemberType NoteProperty -Name samaccountname -Value $_.samaccountname


}


    $users += $group

}

I haven't tested the code above, but hopefully it helps.

I think you'll run into an issue here that `$group.members` will be an object rather than strings. The solution could be to use `Out-String` or to make an object per user. Only can you output with `Export-CSV` — BenH, Jul 25 '17 at 21:37
Good point. I made a further edit to try to take care of that, though it's screwed up the formatting and I'm not sure how to fix it. Is there a way of seeing the underlying code block markup? — ZenoArrow, Jul 25 '17 at 21:57

How to output foreach into CSV using Powershell?

4 Answers4