Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Loop script to CSV

Posted on 2013-10-22
5
Medium Priority
?
461 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 41

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 41

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 41

Accepted Solution

by:
footech earned 1200 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

Q2 2017 - Latest Malware & Internet Attacks

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out our latest Quarterly Internet Security Report!

Question has a verified solution.

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

One-stop solution for Exchange Administrators to address all MS Exchange Server issues, which is known by the name of Stellar Exchange Toolkit.
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
In this video we show how to create an Address List 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 Organization >> Ad…
To show how to create a transport rule 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 Mail Flow >> Rules tab.:  To cr…
Suggested Courses

618 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