Forum Discussion

charlie4872's avatar
charlie4872
Brass Contributor
Jul 26, 2023
Solved

Creating CSV output with headers

Hello I am trying to get the below script to output the results of the script to a .csv file with headers for each of the values gathered from the script. Currently it just outputs a .csv file with t...
Windows PowerShell
  • LeonPavesic's avatar
    LeonPavesic
    Jul 26, 2023

    Hi charlie4872,

    To add headers to your CSV output, you can try the following changes to your script. Instead of appending each line of data directly, we can first try to write the headers and then write the data below them.

    Here's the updated script:

    # Get a list of all AD groups with names starting with 'GLN-GOV' and sort them by name
$groupList = Get-ADGroup -Filter {Name -like 'GLN-GOV*'} | Sort-Object Name | Select-Object Name

# Set the CSV file path
$csvFilePath = ".\testing.csv"

# Define headers
$headers = "Group Name", "Member Name", "SamAccountName", "UserPrincipalName", "Office", "Department"

# Write the headers to the CSV file
$headers -join "," | Out-File -FilePath $csvFilePath -Encoding UTF8

# Loop through each group and get its members
foreach ($group in $groupList) {
    $memberList = Get-ADGroupMember -Identity $group.Name | Get-ADUser -Properties Name, SamAccountName, UserPrincipalName, Office, Department |
                  Select-Object Name, SamAccountName, UserPrincipalName, Office, Department

    # Output the group and its members to the file
    foreach ($member in $memberList) {
        $output = """$($group.Name)"",""$($member.Name)"",""$($member.SamAccountName)"",""$($member.UserPrincipalName)"",""$($member.Office)"",""$($member.Department)"""
        Add-Content -Path $csvFilePath -Value $output
    }
}





    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

LainRobertson's avatar
LainRobertson
Silver Contributor
Jul 27, 2023

charlie4872 

 

You can let PowerShell do the heavy lifting through leveraging a HashTable, as demonstrated below between lines 15 and 23 inclusive.

 

The operands on the left will be what's used as the column headings, meaning you can name them whatever you like.

 

The advantage with this approach is it's useful for more than just simple CSV output.

 

# Specify output file name.
$csvFile = "D:\Data\Temp\Forum\forum.csv";

# Get a list of all AD groups and sort them by name
$groups = Get-ADGroup -Filter {name -like 'GLN-GOV*'} -Property name, objectGUID | Select-Object -Property name, objectGUID | Sort-Object -Property name;

# Loop through each group and get its members
$groups |
    ForEach-Object {
        $groupName = $_.name;

        (Get-ADGroupMember -Identity ($_.objectGUID)).distinguishedName |
            Get-ADObject -Properties objectClass, department, name, physicalDeliveryOfficeName, sAMAccountName, userPrincipalName -ErrorAction:SilentlyContinue |
                ForEach-Object {
                    [PSCustomObject] @{
                        group = $groupName;
                        objectClass = $_.objectClass;
                        name = $_.name;
                        sAMAccountName = $_.sAMAccountName;
                        userPrincipalName = $_.userPrincipalName;
                        office = $_.physicalDeliveryOfficeName;
                        department = $_.department;
                    }
                }
} | Export-Csv -NoTypeInformation -Path $csvFile;

 

Note: This is not a particularly efficient script, but it does what you've asked.

 

Cheers,

Lain

Resources