Home > Exchange Server > One Liners: Exporting Distribution List Membership to Excel

One Liners: Exporting Distribution List Membership to Excel

Exchange 2013 logo 128x128At 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.

  1. March 7th, 2012 at 14:45 | #1

    Thank you! This was a lifesaver.

  2. Fikru Hailu
    July 17th, 2012 at 05:12 | #2

    it is really nice to know the pipe technique
    thanks a lot

  3. Chuck Phelan
    October 4th, 2012 at 10:44 | #3

    Boss needed the info ASAP. He got the info ASAP!
    Thank you!

  4. bob
    November 15th, 2012 at 13:38 | #4

    how would I export all groups, not just one?

    • Pat Richard
      November 23rd, 2012 at 10:14 | #5

      This doesn’t support pipelining, so you’d have to call the one liner for each group.

  5. Kuriya
    March 14th, 2013 at 20:54 | #6

    I am creating it for DLs which are having multiple DLs as subset and members, Is there a script for same?

    • Pat Richard
      March 14th, 2013 at 21:11 | #7

      Not at this time.

  6. May 15th, 2013 at 14:37 | #8

    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

  7. Aditya
    June 17th, 2013 at 20:18 | #9

    Get-DistributionGroupMember -identity “testdl” | Export-Csv Command does not export “CONTACTS” added into Distribution List

    is there any way to it.

    • December 15th, 2013 at 12:49 | #10

      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

  8. September 15th, 2013 at 11:33 | #11


    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

    • Pat Richard
      September 16th, 2013 at 12:15 | #12

      For this purpose it’s perfectly fine.

  9. AMNA
    April 21st, 2014 at 07:27 | #13

    whats the method if we want to populate the Distribution group on Microsoft Exchange server from any existing Microsoft Excel file?

  10. May 15th, 2014 at 12:34 | #14

    # 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 $_ }

  11. Michael Lansang
    January 12th, 2016 at 15:21 | #15

    is it possible to change this to include the email address in the output, and not just the alias?

  12. Sue
    January 21st, 2016 at 12:11 | #16

    I also need the email address and not the alias.

  13. Ankit
    March 6th, 2016 at 23:50 | #17

    Thanks! It helped.
    Get-DistributionGroupMember -Identity “testDL” >c:\new folder\DL.csv

  14. Gilbert
    July 12th, 2016 at 19:00 | #18

    Great. Thanks. Was able to include the email address.

    get-distributiongroupmember -identity “DistributionList” | select name, primarysmtpaddress | export-csv c:\file.txt

  15. amit Pawar
    May 3rd, 2017 at 11:07 | #19

    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”

    • May 4th, 2017 at 15:41 | #20

      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’

  1. July 12th, 2012 at 04:40 | #1