• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Loop script to CSV

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
David Bird
Asked:
David Bird
  • 3
  • 2
1 Solution
 
footechCommented:
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
 
David BirdPartnerAuthor Commented:
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
 
footechCommented:
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
 
footechCommented:
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
 
David BirdPartnerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now