Solved

Loop script to CSV

Posted on 2013-10-22
5
443 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
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:footech
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:footech
Comment Utility
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 39

Accepted Solution

by:
footech earned 300 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Marketers need statistics and metrics like everybody else needs oxygen. In this article we explain how to enable marketing campaign statistics for Microsoft Exchange mail.
In this video we show how to create a Shared Mailbox in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >> Sha…
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now