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
Solved

Loop script to CSV

Posted on 2013-10-22
5
452 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
In this video we show how to create an Accepted Domain 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 >> Ac…
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

828 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