Solved

Loop script to CSV

Posted on 2013-10-22
5
457 Views
Last Modified: 2013-10-22
I've copied the below code I found to dump a list of exchange users data to a CSV file. Ther is a forEach loop that lists all the additional aliases assigned to a user and it works as designed except, it puts ALL the aliases into a single field called "EmailAddresses".  Ergo, when i open the CSV file, I get 2 or more aliases as ONE field.  How do I modify the loop to add a new comma delmited field after each alias? It would be cool if I could use an incrementor for the field header (ie, alias1, alias2) but it's not necessary if I can just get it to dump to individual columns.

Get-Mailbox -ResultSize Unlimited |Select-Object Firstname, Lastname, DisplayName,PrimarySmtpAddress, @

{Name=“EmailAddresses”;Expression={$_.EmailAddresses |Where-Object {$_.PrefixString -ceq “smtp”} | ForEach-Object

{$_.SmtpAddress+","}}} | Export-CSV c:\alldata\PS-Scripts\users.csv -NoTypeInformation




thanx for any thoughts, ideas, suggestions.

daver
0
Comment
Question by:davebird
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:footech
ID: 39591807
But that's just it, if you were to do as requested, you would have different fields for each row, so I can't see how that would be helpful.  The file would then be much more difficult to work with if you wanted to import the file again.
Perhaps joining the aliases by a newline (`n) instead of a comma would help with your viewing of the file in Excel.
0
 

Author Comment

by:davebird
ID: 39591887
Current code dumps:
FirstName, LastName, DisplayName, PrimaryEmailAddress, EmailAddresses
Where each Header is comma delimited and imports into Excel, each as it's own column.
However, the foreach loop adds all addtional EmailAddresses as a single column. I would like each additional email address to have its own column.
0
 
LVL 40

Expert Comment

by:footech
ID: 39592565
Yes, I understand what the current output would be.
The problem is what if one user doesn't have any additional email addresses, and then another user has twelve?  FYI, with PS exporting to .CSV you can't have a field/column that doesn't have a header.  Doing as you suggest would make importing the .CSV and working with those additional email addresses MUCH more difficult.  You pretty much lose any value of outputting as a .CSV.  You'd be just as well off outputting a text file and manually constructing any formatting you want done.
0
 
LVL 40

Accepted Solution

by:
footech earned 300 total points
ID: 39592763
If you're really set on trying it this is one method that should work.  I'm removing the quotes that typically surround each field, thus each comma (regardless of where it is) is interpreted as a field delimiter.
Get-Mailbox -ResultSize Unlimited |
 Select-Object Firstname, Lastname, DisplayName, PrimarySmtpAddress, @{Name="EmailAddresses";Expression={($_.EmailAddresses | Where-Object {$_.PrefixString -ceq "smtp"} | ForEach-Object {$_.SmtpAddress}) -join ","}} |
 ConvertTo-CSV -notype | ForEach-Object { $_ -replace '"',""} | Out-File c:\alldata\PS-Scripts\users.csv -encoding ascii

Open in new window

0
 

Author Closing Comment

by:davebird
ID: 39592787
Perfect!  Thank you.  This is not going to be used as an import process but rather a mechanism to look at all the aliases in a simple format.  The syntax to parse it eluded me.  
I appreciate your help!

daver
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
In this video we show how to create a Resource Mailbox in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: Navigate to the Recipients >> Resources tab.: "Recipients" is our default selection …
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question