One Liners: Exporting Distribution List Membership to Excel
At least three times in the past couple of weeks, I’ve been asked how to dump the members of a DL to Excel for reporting.
Fortunately, it’s a very simple task using two PowerShell cmdlets, Get-DistributionGroupMember and Export-Csv.
Remember than in PowerShell, we can pipe the results of one command as input into another. So first, we get the membership of a list, then we send it to the CSV file for Excel. What we wind up with is:
Get-DistributionGroupMember -Identity "testdl" | Export-Csv -Path "C:\MyFile.Csv"
Where testdl is our distribution group, and myfile.csv is the resulting CSV file.
We can clean that up a little by using the -NoTypeInformation switch during the export-csv cmdlet so that we don’t get the top line of type information.
Get-DistributionGroupMember -Identity "testdl" | Export-Csv -Path "C:\MyFile.Csv" -NoTypeInformation
That gives us a nice clean CSV file that we can then further manipulate as needed in Excel.
If you’d like to learn a lot more about PowerShell and Exchange 2007, check out Professional Windows PowerShell for Exchange Server 2007 SP1 from Wrox. It’s a great reference book.
Thank you! This was a lifesaver.
it is really nice to know the pipe technique
thanks a lot
fikru
MCSE
Boss needed the info ASAP. He got the info ASAP!
Thank you!
how would I export all groups, not just one?
This doesn’t support pipelining, so you’d have to call the one liner for each group.
I am creating it for DLs which are having multiple DLs as subset and members, Is there a script for same?
Not at this time.
Not a one liner but I use this –
Do { $Group = Read-Host ” What is the Distribution Group you want the members from? ” }
While ( ! $Group )
$Date = (get-date).ToString(“MM-dd-yy”)
Get-DistributionGroupMember “$Group” | Select @{Name=”Distribution Group”;Expression={$Group}},DisplayName,Alias | Export-CSV “C:\Temp\$Date $Group.csv” -NoTypeInformation
Get-DistributionGroupMember -identity “testdl” | Export-Csv Command does not export “CONTACTS” added into Distribution List
is there any way to it.
Have you tried ? – (Exchange 2010)
Get-DistributionGroup “Powershell” | Get-DistributionGroupMember | Select Name,RecipientType
Name RecipientType
—– ————-
TBOLTON UserMailbox
Tim Test5 MailContact
TEST6, Tim Contact
Mine also works with –
Get-DistributionGroupMember “Powershell” | Select Name,RecipientType
Name RecipientType
—– ————-
TBOLTON UserMailbox
Tim Test5 MailContact
TEST6, Tim Contact
Dangerous!
Many users believe CSV is just another type of Excel file; however this is not the case.
Microsoft Excel will automatically convert data columns into the format that
it thinks is best when opening a CSV or a Tab delimited data file.
For example, Excel will remove leading Zeros of Numbers, change Data/Time Formats or uses
the scientific number format for large Numbers and others.
This can go unnoticed in large data sets.
If Microsoft Excel outputs an Excel worksheet into a CSV file, the output does not always follow the CSV format rules. Excel only places quotes around certain fields not on all fields. This leads to unreadable CSV files for PowerShell!
I have create 2 PowerShell Functions as workaround
Export-XLS which uses even a special CSV format and Export-XLSX to create real Excel files. Botjh are very well documented, so read bevor use 😉
See: http://poshcode.org/4369
And: http://gallery.technet.microsoft.com/scriptcenter/Export-XLSX-PowerShell-f2f0c035
greets Peter Kriegel
http://www.admin-source.de
For this purpose it’s perfectly fine.
whats the method if we want to populate the Distribution group on Microsoft Exchange server from any existing Microsoft Excel file?
# I have placed the userid’s into a txt file called Minasi.txt
$AddMembers = gc “Minasi.txt”
# Now you insert the name of the – Existing – Disto Group – example here is “MR&D”
$AddMembers | ForEach {Add-DistributionGroupMember -Identity “MR&D” -Member $_ }
is it possible to change this to include the email address in the output, and not just the alias?
I also need the email address and not the alias.
Thanks! It helped.
Get-DistributionGroupMember -Identity “testDL” >c:\new folder\DL.csv
Great. Thanks. Was able to include the email address.
get-distributiongroupmember -identity “DistributionList” | select name, primarysmtpaddress | export-csv c:\file.txt
Hi,
I have received below error.
WARNING: By default, only the first 1000 items are returned. Use the ResultSize parameter to specify the number of
Get-DistributionGroupMember {DL Name} | Sort -Property DisplayName | Select DisplayName, Alias, Department | Export-CSV -Path C:\DLname.csv -NoTypeInformation -Encoding “Unicode”
ResultSize is your friend.
Get-DistributionGroupMember {DL Name} -ResultSize Unlimited | Sort-Object -Property DisplayName | Select-Object -Property DisplayName, Alias, Department | Export-CSV -Path C:\DLname.csv -NoTypeInformation -Encoding ‘Unicode’