Powershell export csv with columns

Question

i want to export csv file with columns "ParentGroupName", "MemberName", "DisplayName"

At the moment it is exporting the three datas into one column.

function getGroups{

    $Groups += Get-ADGroup -Filter * -SearchBase "ou=Groups,ou=DCM,ou=NTG,dc=prod,dc=main,dc=ntgov" | Select-Object -ExpandProperty samaccountname

    return $Groups
}

Measure-Command{ 


$Groups = getGroups
write-host "Groups:" $Groups.Count 

}

Measure-Command{



$date = $(get-date).ToString("dd MMM yyyy")
$global:FileName = "Active Directory Group Membership - DCM -" + $date

$stringBuilder = New-Object System.Text.StringBuilder

foreach ($GroupName in $Groups){

        Get-ADGroupMember -Identity $GroupName | Sort-Object $_.SamAccountName | ForEach-Object {
            $ParentGroupName = (Get-ADGroup -Identity $GroupName).SamAccountName 
            $MemberName = $_.SamAccountName # Member of the Group.


            if ($_.ObjectClass -eq 'group') {



                $DisplayName = ' - '

            } elseif ($_.ObjectClass -eq 'user') {


                $a = (Get-ADUser -Identity $MemberName -Properties Displayname)
                $DisplayName = $a.DisplayName 

            }



            $null = $stringBuilder.Append("$ParentGroupName, $MemberName, $DisplayName")


        }
    } 


outputArray = $stringBuilder.ToString()


out-file C:\Users\augut\Desktop\$FileName.csv


outputArray | out-file C:\Users\augut\Desktop\$FileName.csv
}

alroc · Accepted Answer · 2016-11-30 02:13:57Z

1

You're making a headache for yourself by manually constructing the CSV file. This can be simplified considerably by constructing a custom object for each item found with the properties you need recorded, then stuffing those into an array and exporting that array to a CSV file.

function getGroups{
    $Groups += Get-ADGroup -Filter * -SearchBase "ou=Groups,ou=DCM,ou=NTG,dc=prod,dc=main,dc=ntgov" | Select-Object -ExpandProperty samaccountname
    return $Groups
}
$Groups = getGroups
write-host "Groups:" $Groups.Count 

$date = $(get-date).ToString("dd MMM yyyy")
$global:FileName = "Active Directory Group Membership - DCM -" + $date

$results = @();

foreach ($GroupName in $Groups){
        Get-ADGroupMember -Identity $GroupName | Sort-Object $_.SamAccountName | ForEach-Object {
            $ItemProperties = @{
                "ParentGroupName" = (Get-ADGroup -Identity $GroupName).SamAccountName;
                "MemberName" = $_.SamAccountName
            }

            if ($_.ObjectClass -eq 'group') {
                $ItemProperties.Add("DisplayName","-");
            } elseif ($_.ObjectClass -eq 'user') {
                $ItemProperties.Add("DisplayName",(Get-ADUser -Identity $MemberName -Properties DisplayName).DisplayName);
            }
            $MyItem = New-Object -TypeName psobject -property $ItemProperties;
            $Results += $MyItem;
            $ItemProperties = $null;
        }
    } 
$results | export-csv -path "C:\Users\augut\Desktop\$FileName.csv" -NoTypeInformation

edited Nov 30, 2016 at 2:13

answered Nov 29, 2016 at 2:32

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GuzziT Over a year ago

thanks it mostly works accept for the displayName which seems to repeat one name for every groupName and userName: for example: mjms Jayne Fairnington DCM_NTH_FOI TP6 Jayne Fairnington DCM_NTH_FOI yfair Jayne Fairnington DCM_NTH_FOI ktimi Jayne Fairnington zzMinisterVowles_O bxl Jayne Fairnington DCM_DNH_SPIO_RW FMD Jayne Fairnington DCM_DNH_SPIO_RW MAT Jayne Fairnington DCM_DNH_SPIO_RW aleon Jayne Fairnington DCM OCM Chief of Staffs amart Jayne Fairnington DCM OCM Chief of Staffs bqb Jayne Fairnington DCM OCM Chief of Staffs

alroc Over a year ago

@GuzziT I just made a small edit, try the new version.

GuzziT Over a year ago

It didn't change the output in excel :/

Collectives™ on Stack Overflow

Powershell export csv with columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related