Exchange Server Forums

export-csv results are messed up

Users viewing this topic: none

Logged in as: Guest

Printable Version

All Forums >> [Microsoft Exchange 2007] >> Management >> export-csv results are messed up

Page: [1]

Message

<< Older Topic Newer Topic >>

Limited time MSExchange.org offer! -- 1.Sep.2008 1:00:00 PM

TechGenix and SolarWinds have partnered to provide free copies of SolarWinds Exchange Monitor to all visitors who join the MSExchange.org Forums. SolarWinds Exchange Monitor is a handy desktop dashboard that continuously monitors Microsoft Exchange to deliver real-time insight into Exchange services, mail queue sizes, and host server health. Learn more about Exchange Monitor and the free offer!

export-csv results are messed up - 20.Feb.2008 4:36:08 AM

synn

Posts: 2
Joined: 20.Feb.2008
Status: offline

Hi,

I've just received a blog update regarding Get-MailboxStatistics and how you can script and email the results for reporting purposes.

I was on already on my way doing this myself and came across a few issues and wondered if anyone had any ideas on how to get round them.

I can run get-mailboxstatistics | export-csv c:\results.csv and will receive all the information but this includes columns that I don't want the results on (and badly formatted columns such as Total Item Size). With some manual editting in excel I can get the results how I want them but this is labour intensive.

What I wanted to do was similar to the article. I wanted to be able to select the specific columns that I wanted to report on, relabel some of the columns (and convert the Item Size from bytes to megabytes) and export the results to CSV. If I do any conversion or labelling then the output in CSV is just a lot of strings of letters and numbers even though on-screen it displays properly.

This is the output I get

#TYPE Microsoft.PowerShell.Commands.Internal.Format.FormatStartData

ClassId2e4f51ef21dd47e99d3c952918aff9cd

033ecb2bc07a4d43b5ef94ed5a35d280

9e210fe47d09416682b841769c78b8a3

27c87ef9bbda4f709f6b4002fa4af63c

4ec4f0187cb04f4cb6973460dfe252df

cf522b78d86c486691226b40aa69e95c

However, if I use the > filename.txt or out-file to text, it only displays exactly what's on screen (and then if the columns on screen aren't wide enough you get display names that are cut off and replaced with dots at the end). It also means that when I try and import the data into Excel to manipulate then I have issues as our display names have comma's in them and it splits some columns into two.

Has anyone else seen this type of behaviour before?

Thanks
Justin

Post #: 1

Featured Links*

RE: export-csv results are messed up - 26.Feb.2008 1:27:28 PM

jimthemcp

Posts: 43
Joined: 19.Sep.2007
Status: offline

You need to include the Select-Object to select what you want exported:
Get-MailboxStatistics | Select-Object DisplayName, AssociatedItemCount, TotalItemSize, DeletedItemCount, TotalDeletedItemSize | Export-Csv Mailbox-Stats.csv

(in reply to synn)

Post #: 2

RE: export-csv results are messed up - 27.Feb.2008 3:03:56 AM

synn

Posts: 2
Joined: 20.Feb.2008
Status: offline

Thanks for that, but that leaves me in the same place I already was. I was already selecting the objects either using | ft or | select-object.

What I wanted to be able to do was to convert the values of TotalItemSize from Bytes to MB from within the script rather than having to export it into Excel as a value ending in B, having a calculation cell that dropped the B from the end of the string and then converting the value into MB in another cell. If I add @{label="TotalItemSize(KB)";expression={$_.TotalItemSize.Value.ToKB()}}into my powershell script, it displays correctly on screen, but when you export to csv you get the output I posed earlier.

I guess it looks like I'll have to do all the work in Excel

Thanks
Justin

(in reply to jimthemcp)

Post #: 3