exporting to csv doesn't work 1

[kokser]

I'm using PS 1.0 on a server 2008 with Exchange 2007.

Script:

## Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin
get-date -format d
get-mailbox | get-mailboxstatistics | where {$_.ObjectClass -eq "Mailbox"} |ft @{label="User";expression={$_.DisplayName}},@{label="Size(KB)";expression={$_.TotalItemSize.Value.ToKB()}}, @{label="Items";expression={$_.ItemCount}}, @{label="DN";expression={(get-mailbox $_.legacydn).distinguishedname}} > c:\stats.csv

It works great, I get all the output I want, but it doesn't get formatted properly for a csv file. It's just clean text, no commas or anything.

Output:

User          Size(KB)          Items          DN
----          --------          -----          --                           
lb1           11498             7              CN=lb1,OU=Brugere,OU=Legep...
lb2           5734              6              CN=lb2,OU=Brugere,OU=Legep...
nb1           11454             7              CN=nb1,OU=Brugere,OU=Legep...

As you can see, it doesn't export the full DN which also annoyes me a lot.

I'm quit new to powershell, so any help appreciated

 

[kokser]

I added the -auto switch, so it's formatted nicer now, and I get the full DN. However, if I export it to a csv file, it's still not comma seperated (like a csv file is supposed to be). Is there a way to replace spaces with commas?

 

[crobin1]

File redirection (>) simply takes whatever you give it and puts it in a file with the name that follows. It can't do any type of conversion. Just putting .CSV at the end of the filename doesn't automatically make it a CSV file.

You'll need to use calculated properties in a Select-Object statement and then use Export-CSV to create the CSV file. Something along these lines:

$output = get-mailbox | get-mailboxstatistics |
 where {$_.ObjectClass -eq "Mailbox"} |
 Select-Object @{name="User";expression={$_.DisplayName}},@{name="Size(KB)";expression={$_.TotalItemSize.Value.ToKB()}}, @{name="Items";expression={$_.ItemCount}}, @{name="DN";expression={(get-mailbox $_.legacydn).distinguishedname}}

$output | export-csv -path c:\stats.csv -encoding ascii -notypeinformation

 

[kokser]

It worked perfectly fine, didn't know you could encode Thanks a ton for the help, I've been working on this all day.

 

[58sniper]

Also, there has got to be a more efficient way of doing this

expression={(get-mailbox $_.legacydn).distinguishedname}

You've already retrieved the mailboxes at the first command. Getting each one again seems overly taxing.

I don't have the time to lab it out right now, but there's certainly got to be room to make that more efficient.

Pat Richard MVP
Plan for performance, and capacity takes care of itself. Plan for capacity, and suffer poor performance.

http://www.ucblogs.net/blogs/exchange/