Exchange Powershell Question - Export Data as CSV or Excel Spreadsheet
I'm using some code to export a list of users that belong to Dynamic Mail Groups in an exchange powershell:
$members = Get-DynamicDistributionGroup -Identity "<some group>"
Get-Recipient -RecipientPreviewFilter $members.RecipientFilter -OrganizationalUnit $members.RecipientContainer > "C:\Scripts\Dynamic Group Export\somegroup.txt"
And it exports a TXT file, that can work as a basic list, but the boss wants it in an excel spreadsheet, with different groups for each workbook.
Is there a way to export the first name and last name for each address into an excel spreadsheet or CSV, into multiple workbooks per group?
August 16th, 2012 12:45pm
Hello,
$member=Get-DynamicDistributionGroup -identity "Group Name"
$members=Get-Recipient -RecipientPreviewFilter $member.RecipientFilter -OrganizationalUnit $member.RecipientContainer
$members| foreach{Get-user -identity $_.Name| select UserPrincipalName, firstname, lastname } > "C:\memberlist.csv"
Thanks,
Evan
Evan Liu
TechNet Community Support
Closer, but all the fields are concatenated into one cell:
username@domain.com Doe1 John
Is there a way to separate the fields by a comma for the CSV?
Free Windows Admin Tool Kit Click here and download it now
August 17th, 2012 7:23am
On Fri, 17 Aug 2012 12:28:25 +0000, Bigun139 wrote:
>
>
>I chopped the code you gave me and with some googling I found a much better solution for a CSV export: $members = Get-DynamicDistributionGroup -Identity "GroupNameHere"
>$members = Get-Recipient -RecipientPreviewFilter $members.RecipientFilter -OrganizationalUnit $members.RecipientContainer
>$row = @()
>$members | foreach{
> $lastname = Get-user -identity $_.Name | select lastname
Your assumption that only users will be included in the DDL won't
always hold true. Since you're already dealing with Recipient objects
there's no reason to back to the AD for the information you already
have -- just use "$_". But see below for more.
> $lastname = "$lastname"
> $lastname = $lastname.Replace("@{LastName=", "")
> $lastname = $lastname.Replace("}", "")
> $firstname = Get-user -identity $_.Name | select firstname
> $firstname = "$firstname"
> $firstname = $firstname.Replace("@{FirstName=", "")
> $firstname = $firstname.Replace("}", "")
> $row += "`"$lastname`" `"$firstname`""
>}
>$row > "C:\GroupFileNameHere.csv"
If the number of "members" is significant then the "$row +=" will get
pretty slow (and you've already got another list, "$members" to deal
with). Powershell's wrapper makes list handling pretty slow as the
size of the list increases.
>This should give you a proper formatted CSV of Dynamic Mail Group Members
This is less verbose and doesn't use a list to keep the results in.
Line-wrapping will probably interfere with the formatting so I put a
semicolon at the end of the 1st line. The whole thing can go in one
line if you like (although I'm no fan of "one-liners").
Since some of the objects returned as "members" of a DDL may not be
user objects they may not have a firstname or lastname. I added an
"Account" column so it doesn't look like there's nothing there. :-)
"Account,Lastname,Firstname" | out-file "C:\GroupFileNameHere.csv";
$ddl = Get-DynamicDistributionGroup -Identity "GroupNameHere";
Get-Recipient -RecipientPreviewFilter $ddl.RecipientFilter
-OrganizationalUnit $ddl.RecipientContainer | foreach {
"`"$($_.samaccountname)`"`"$($_.lastname)`",`"$($_.firstname)`"" |
out-file "C:\GroupFileNameHere.csv" -append
}
---
Rich Matheisen
MCSE+I, Exchange MVP
--- Rich Matheisen MCSE+I, Exchange MVP
August 17th, 2012 3:34pm