Why Can't I Pipe Format-Table to Export-CSV?

Here's something I see PowerShell newcomers do ALL THE TIME:

Get-WmiObject -Class Win32_OperatingSystem -computername REMOTE |
Format-Table -prop __SERVER,BuildNumber,ServicePackMajorVersion | 
Export-CSV inventory.csv

I know what you're after: You want to create a CSV file that just has those three properties in it. But you're going about it the wrong way. I absolutely understand where the confusion sets in. After all, if you just run something like Get-Process, you get a table of results on the screen, right? And if you run:

Get-Process | Export-CSV procs.csv


Well, that works, right? So if Get-Process produces a table, and you can pipe Get-Process to Export-CSV, then surely Export-CSV can accept tables from elsewhere, yes?

No.

Get-Process doesn't produce a table. When you run Get-Process all by itself, here's what's really happening under the hood:

Get-Process | Out-Default | Format-Table | Out-Host


When PowerShell hits the end of the command-line, it tacks on a call to Out-Default every single time. It's invisible, so you can't see it. Under the hood, the shell's formatting system is kicking in and adding the Format-Table call invisibly, and then directing that to the screen via Out-Host. 

The trick is that a Format cmdlet doesn't produce objects which can be used by most other cmdlets. In fact, Format cmdlets produce a special kind of formatting instruction that can only be consumed by four cmdlets: Out-Host, Out-Printer, Out-File, and Out-String. So once you've used a Format cmdlet, you either need to be at the end of your command-line, or you need to only use one of those four Out cmdlets.

This annoys folks even more when they try to do something like this, using custom columns:

Get-WmiObject -class Win32_LogicalDisk -filter 'DriveType=3' |
Format-Table -prop DeviceID,
                   @{n='Size(GB)';e={$_.Size / 1GB -as [int]}},
                   @{n='Free)MB)';e={$_.FreeSpace / 1MB -as [int]}} |
Export-CSV drives.csv

Still won't work. Again, once you've used a Format cmdlet, you've created formatted text (that's why the cmdlets use the verb "Format," after all). Formatted text needs to go to the screen, a piece of paper, or a simple text file, and that's all it can do. 

But all's not lost. You can still get the CSV file you're after. Whenever you want to add a custom property (which is technically different than a custom column, since columns refer to formatted text) just use Select-Object instead.

Get-WmiObject -Class Win32_OperatingSystem -computername REMOTE |
Select-Object -prop __SERVER,BuildNumber,ServicePackMajorVersion | 
Export-CSV inventory.csv

Get-WmiObject -class Win32_LogicalDisk -filter 'DriveType=3' |
Select-Object -prop DeviceID,
                   @{n='Size(GB)';e={$_.Size / 1GB -as [int]}},
                   @{n='Free)MB)';e={$_.FreeSpace / 1MB -as [int]}} |
Export-CSV drives.csv

Select-Object accepts the same custom property syntax as Format-Table, with three exceptions. In addition to the Name ('n') and Expression ('e') keys, Format-Table also accepts Width, Align, and FormatString keys. Select-Object doesn't accept those latter three, because those deal (again) with formatted text. 

So just follow two simple rules:

  1. If you want to produce formatted text for screen, text file, or paper, use Format-Table.
  2. If you want to continue piping the objects to another cmdlet, such as to convert it or export it, use Select-Object.

Please or Register to post comments.

What's PowerShell with a Purpose Blog?

Don Jones demystifies Windows PowerShell.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×