[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

Exporting Powershell to formatted CSV file


Running Powershell v2. I have the following command that pulls mailbox name and sizes for a list of users.

But when it exports to CSV, the data is not formatted properly. What I'd like to do is have Display Name and Mailbox Size in seperate columns on the CSV file but this doesn't seem possible? When the data is exported it all appears in the same column?

Get-content c:\LIST.txt | Get-mailbox | Foreach-object {get-mailboxstatistics -identity $_} | fl displayname, @{ expression={$_.TotalItemSize.Value.ToMB()}} > c:\export.csv

3 Solutions
Hendrik WieseCommented:
Have you tried it like this:

Get-content c:\LIST.txt | Get-mailbox | Foreach-object {get-mailboxstatistics -identity $_} | ft displayname, @{ expression={$_.TotalItemSize.Value.ToMB()}} > c:\export.csv

Open in new window

You can use the export-csv cmdlets like this

Foreach ($User in (get-content c:\list.txt)){
    Get-mailbox $User | get-mailboxstatistics | Select displayname, @{n = 'TotalSize'; e={$_.TotalItemSize.Value.ToMB()}} | export-csv c:\temp\export.csv -notype}

Open in new window

QlemoC++ DeveloperCommented:
Don't use any formatting cmdlet (format-*) if you export into CSV. Just use export-csv - it is exactly for that purpose:
Get-content c:\LIST.txt | Get-mailbox | Foreach-object {get-mailboxstatistics -identity $_}  | select displayname, @{n="Size (MB)"; e={$_.TotalItemSize.Value.ToMB()}} | export-csv c:\export.csv

Open in new window

bruce_77Author Commented:

So what is the difference between using "| fl displayname" and "| select displayname"?
QlemoC++ DeveloperCommented:
fl = format-list: formatting will be   Property: Value   with the properties displayed on separate lines. The result is a stream of text lines, and there are no properties anymore. The same applies to all formatting cmdlets (format-*), as they all result in a stream of text lines.
format-list is usually only applied to console output. format-table can be used for text files, too, but only if you do not need further processing.

select = select-object: Creates new objects containing only those properties you selected (plus the default Object class properties, like name, type, aso.). That is, the result is a collection of objects again, and hence can be processed further. In particular, property names, types and data length are retained. So if put into export-csv, the cmdlet knows where to put delimiters.

Featured Post

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now