Forum Discussion
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 the correct info in each column of the sheet but I would like each column to have a header of Group Name, Member Name, Samaccountname, Userprincipalname, Office and Department. I have tried different things but so far nothing is working. Any help is greatly appreciated. Below is what I have now for the scritp.
# Get a list of all AD groups and sort them by name
$groupList = Get-ADGroup -Filter {name -like 'GLN-GOV*'} | Sort-Object Name | Select-Object Name
# Loop through each group and get its members
foreach ($group in $groupList) {
$memberList = Get-ADGroupMember -Identity $group.Name | get-aduser -properties * | select name,samaccountname,userprincipalname,office,department
# Output the group and its members to the file, Change the output location as per your requirements
foreach ($member in $memberList) {
$output = """$($group.Name)"",""$($member.Name)"",""$($member.samaccountName)"",""$($member.userprincipalname)"",""$($member.office)"",""$($member.department)"""
Add-Content -Path ".\testing.csv" -Value $output
}
}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
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
- Hello Leon, this works perfect. Thank you for the help!
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
