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 (of mine). — mklement0
– mklement0, Commented Jul 25, 2017 at 21:35

BenH · Accepted Answer · 2017-07-25 21:40:03Z

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.

Hackerman · Accepted Answer · 2017-07-25 20:43:39Z

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, 2017 at 20:43

Hackerman

12.3k2 gold badges37 silver badges47 bronze badges

Comments

mklement0 · Accepted Answer · 2017-07-25 21:22:52Z

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, 2017 at 21:22

mklement0

452k68 gold badges726 silver badges986 bronze badges

answered Jul 25, 2017 at 20:45

Jason Snell

1,46512 silver badges22 bronze badges

1 Comment

Prox Over a year ago

I do want to overwrite it.

ZenoArrow · Accepted Answer · 2017-07-25 21:53:28Z

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

Collectives™ on Stack Overflow

How to output foreach into CSV using Powershell?

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Linked

Related