Automating the world one-liner at a time…
Ken's PowerShell blog has an entry Remote Services and PowerShell where he wrote a function to get services from a remote machine using WMI. He formatted the data using FT (format-table) and then went on to export the data to CSV. He pointed out that the following would NOT work
rget-service remotemachinename netlogon | ft -autosize name,startmode,state,status,startname | export-csv services.csv
Instead you should use:
rget-service remotemachinename netlogon | Select-Object name,startmode,state,status,startname | export-csv services.csv
Let's take a minute to explore why the first command does not work. Let's replace the Export-CSV with a Get-Member and see what we get:
Ps> rget-service remotemachinename netlogon | ft -autosize name,startmode,state,status,startname | Get-Member –MemberType Property TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatStartDataName MemberType Definition---- ---------- ----------autosizeInfo Property Microsoft.PowerShell....ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId...groupingEntry Property Microsoft.PowerShell....pageFooterEntry Property Microsoft.PowerShell....pageHeaderEntry Property Microsoft.PowerShell....shapeInfo Property Microsoft.PowerShell.... TypeName: Microsoft.PowerShell.Commands.Internal.Format.GroupStartDataName MemberType Definition---- ---------- ----------ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId...groupingEntry Property Microsoft.PowerShell....PitnshapeInfo Property Microsoft.PowerShell.... TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatEntryDataName MemberType Definition---- ---------- ----------ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId...formatEntryInfo Property Microsoft.PowerShell....outOfBand Property System.Boolean outOfB...writeErrorStream Property System.Boolean writeE... TypeName: Microsoft.PowerShell.Commands.Internal.Format.GroupEndDataName MemberType Definition---- ---------- ----------ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId...groupingEntry Property Microsoft.PowerShell.... TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatEndDataName MemberType Definition---- ---------- ----------ClassId2e4f51ef21dd47e99d3c952918aff9cd Property System.String ClassId...groupingEntry Property Microsoft.PowerShell....
What you are seeing is that Format-Table transforms the objects into a stream of formatting directives. These are then consumed by one of the OUT- Commands (Out-Host, Out-File, Out-String, Out-Printer). This is why you can't pipe format-table to export-csv.
Enjoy!
Jeffrey Snover [MSFT]Windows PowerShell/MMC ArchitectVisit the Windows PowerShell Team blog at: http://blogs.msdn.com/PowerShellVisit the Windows PowerShell ScriptCenter at: http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx
Very Helpful! Thanks for your insight Jeffrey.
I actually found a similar intriguing behavior earlier as well but Ken beat me to the punch before I get around to blog about it (still :(.
Anyway, the statements I was looking at were (still am if the odd behavior has not already been explained here by Jeffrey):
PS C:\> get-service | sort-object status, name | ft -groupby status | convertto-html | out-file "get-service(bad).html"
PS C:\> get-service | sort-object status, name | convertto-html | out-file "get-service(good).html"
PS C:\temp>
rgds,
When you format the data using Format-table and then try to export it using Export-Csv it doesn't
Great info and thanks for the explanation. This will help us speed up an e2k3 -> e2k7 transition.
Google led me here for my problem, i can't find anything similair somewhere else. I'm also trying to get the output into a csv file but i don't know where to put my export-csv command.
Here's the script(let)
$mbx = get-casmailbox | where {$_.hasactivesyncdevicepartnership -eq $true -and $_.identity -notlike "*CAS_{*"} ; $mbx | foreach {$name = $_.name; $device = get-activesyncdevicestatistics -mailbox $_.identity; $device | foreach {Write-host $mbx.name, $_.identity, $_.devicemodel, $_.devicephonenumber, $_.deviceid, $_.FirstSyncTime, $_.LastSuccessSync } }
I've put it at the end between the curly's everywhere. When export did not work i try'd Out-File Write-File....
Nothing! i don't get it ? How to get my output to a file ?
Regards, Diederik
OK, we ran into this very question this morning in class. The "why" was pretty obvious. But what's the solution?
The solution I normally use is Select-Object. Below a small number of properties (4), Select-Object will display as a table. Sadly, it cannot autosize, but what you send along the pipeline will contain the properties of the original object.
Hope this Helps,
James Brundage [MSFT]
DiederikM,
Not sure if it will help or not but I ran into a similar problem with AD queries I was pulling with powershell. I ended up having to pull the data as [string]$_.name into my custom object to be able to use | export-csv.
Hi Jeff,
This post helped me to complete one of my project based on exchange 2010 and powershell gui.
I was stuck on the issue for 2 days and got this post very useful.
Thank you very much :)
Thanks for the post. I was stuck for a while and this did the trick
I'm gettting this error but I'm not sure how to out put the results to email message. I'm trying to output the results of $svc to $body
Set-ExecutionPolicy RemoteSigned
$svc=gwmi win32_service | where {$_.StartMode -eq “Auto”} |where {$_.State -eq "Stopped"}| select-object DisplayName,name,startname,startmode,state | format-table
$svc
gwmi win32_service | where {$_.StartMode -eq “Auto”} |where {$_.State -eq "Stopped"}| Start-Service | Start-Sleep -s 10 | select DisplayName,name,startname,startmode,state |format-table
$emailFrom = "insert email address here"
$emailTo = "insert email address here"
$subject = "Services"
$body = "insert message body here"
$smtpServer = "relay.golder.com"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom, $emailTo, $subject, $body)
This was exactly what I was looking for. Thanks :)
How about further explaining how we can still use the format-table without the stream of formatting directives ?
I often find myself needing to format objects like the format-table at the same time having the benefits of select-object, but, somehow select-obect only turns automatically into a table objects with no more than 4 properties ( at least this is my experience ).
Thanks