See Why PowerShell Can't Export Some Properties to CSV

Anonymous — Tue, 15 Nov 2011 06:00:00 GMT

Summary: Learn why Windows PowerShell cannot export certain properties to a CSV file and what to do about it.

Microsoft Scripting Guy, Ed Wilson, is here. The other day, Terry Dow posted a comment on my Use PowerShell to Work with CSV Formatted Text blog post. Basically, Terry is asking about exporting a multivalue property via Export-CSV file. I can illustrate the problem by using the Get-Process cmdlet to retrieve information about my currently running Outlook process. In this command, I pipe the results of the Get-Process cmdlet to the Format-List cmdlet. Here is the command:

Get-Process outlook | format-list *

The output is really extensive, and I am going to point out that one line appears like the one seen here:

Threads : {7148, 8120, 5320, 3080...}

I am now going to select the name and the threads properties, and pipe them to the Format-List cmdlet:

Get-Process outlook | format-list name, threads

The output does not contain all of the thread information. In fact, it only has a few numbers, followed by an ellipsis. I try several combinations, but nothing other than a few numbers and ellipses ever appear. This is shown in the following figure.

This behavior carries over when piping information to Export-CSV. In the following command I use the Get-Process cmdlet to retrieve the Outlook process. I pipe the resultant Process object to the Select-Object cmdlet (select is an alias) and retrieve the name and the threads, and pipe to Export-CSV to create a CSV file. I then open the file in Microsoft Excel.

Get-Process outlook | Select name, threads | export-CSV c:\fso\olproc.csv; c:\fso\olproc.csv

The resultant Microsoft Excel spreadsheet appears in the following figure (I highlighted the threads collection manually).

So, we have confirmed that for certain types of properties, Windows PowerShell does not display all the information. We seem to be a bit better off when using Format-Table or Format-List (but that is just an illusion).

The first thing we need to do is to see which type of object we are dealing with. To do this, I use the Get-Member cmdlet. This command is shown here:

PS C:\> Get-Process outlook | Get-Member threads

TypeName: System.Diagnostics.Process

Name MemberType Definition

Threads Property System.Diagnostics.ProcessThreadCollection Threads {get;}

The threads property returns a ProcessThreadCollection. This class is documented on MSDN, but I do not need to really look at that article because I can find out what I need via the Get-Member cmdlet. The big thing to keep in mind is that a ProcessThreadCollection class is a collection of something else. This something else is a ProcessThread. The ProcessThread class is also documented on MSDN, and it is more interesting than the ProcessThreadCollection. I can find the information about the ProcessThread class by using the following command (gm is an alias for the Get-Member cmdlet):

(Get-Process outlook).threads | gm

The command and associated output are shown here:

PS C:\> (Get-Process outlook).threads | gm

TypeName: System.Diagnostics.ProcessThread

Name MemberType Definition

Disposed Event System.EventHandler Disposed(System.Object, System.EventArgs)

CreateObjRef Method System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)

Dispose Method System.Void Dispose()

Equals Method bool Equals(System.Object obj)

GetHashCode Method int GetHashCode()

GetLifetimeService Method System.Object GetLifetimeService()

GetType Method type GetType()

InitializeLifetimeService Method System.Object InitializeLifetimeService()

ResetIdealProcessor Method System.Void ResetIdealProcessor()

ToString Method string ToString()

BasePriority Property System.Int32 BasePriority {get;}

Container Property System.ComponentModel.IContainer Container {get;}

CurrentPriority Property System.Int32 CurrentPriority {get;}

Id Property System.Int32 Id {get;}

IdealProcessor Property System.Int32 IdealProcessor {set;}

PriorityBoostEnabled Property System.Boolean PriorityBoostEnabled {get;set;}

PriorityLevel Property System.Diagnostics.ThreadPriorityLevel PriorityLevel {get;set;}

PrivilegedProcessorTime Property System.TimeSpan PrivilegedProcessorTime {get;}

ProcessorAffinity Property System.IntPtr ProcessorAffinity {set;}

Site Property System.ComponentModel.ISite Site {get;set;}

StartAddress Property System.IntPtr StartAddress {get;}

StartTime Property System.DateTime StartTime {get;}

ThreadState Property System.Diagnostics.ThreadState ThreadState {get;}

TotalProcessorTime Property System.TimeSpan TotalProcessorTime {get;}

UserProcessorTime Property System.TimeSpan UserProcessorTime {get;}

WaitReason Property System.Diagnostics.ThreadWaitReason WaitReason {get;}

I can write the threads to a CSV file. To do this, I can use the Select-Object cmdlet and pipe the results to the Export-CSV file. This command is shown here (I use Select as an alias for the Select-Object cmdlet, and I open the CSV in Microsoft Excel by using the path to the newly created CSV file):

Get-Process outlook | Select -ExpandProperty threads | Export-CSV c:\fso\OlThread.csv;c:\fso\olthread.csv

The newly created CSV file opens easily in Microsoft Excel. This allows me to examine the various threads in Microsoft Outlook (they are all waiting by the way).

So the solution to having an object name showing up in the CSV file is to use the Select-Object cmdlet to expand the object contained inside the property. Keep in mind that this will not enable you to be able to have the process name, processID, and threads in the same CSV file because of the way Select-Object works when expanding processes.

Use Export-CliXML to maintain fidelity to the original object. The following code illustrates this technique:

Get-Process outlook | Export-Clixml c:\fso\olXML.xml

To work with the object, use the Import-Clixml cmdlet as illustrated here where I import the XML file, and access the name and the process ID.

$a = Import-Clixml C:\fso\olXML.xml

$a.Name

$a.Id

I get all of the thread information by accessing the threads property. In the following example, I truncate the output:

PS C:\> $a.threads

BasePriority : 8

CurrentPriority : 13

Id : 7148

PriorityBoostEnabled : True

PriorityLevel : Normal

<***OUTPUT TRUNCATED***>

Remember back at the beginning of the article when I was talking about the ProcessThreadCollection class? I said the ProcessThreadCollection class was not so interesting. Well, there is one interesting property, and it is the count property. I can use dotted notation to access it, as illustrated here:

PS C:\> $a.threads.count

Knowing the number of threads a process uses is valuable information. In fact, using the technique above, I can add it to an object by using the following technique (gps is an alias for the Get-Process cmdlet and select is an alias for Select-Object):

gps | select name, @{LABEL='number of threads';EXPRESSION= {$_.threads.count}}

This code works, but the number of threads is spread out everywhere (not sorted). To sort the number of threads, I pipe the command to the Sort-Object cmdlet. My label, number of threads, is the actual property name in my newly created custom object. This command is shown here:

gps | select name, @{LABEL='number of threads';EXPRESSION= {$_.threads.count}} | sort 'number of threads'

This works great, but now the information spreads to both sides of the Windows PowerShell console window. Now, I will use the Format-Table cmdlet (ft is an alias and a is short for autosize).

gps | select name, @{LABEL='number of threads';EXPRESSION= {$_.threads.count}} | sort 'number of threads' | ft –a

The command and associated output are shown in the following figure.

Of course, it would be possible to pipe this type of expression to Export-CSV. I hope you enjoyed today’s excursions into more fun with CSV files.

Join me tomorrow for more cool Windows PowerShell stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Search results for 'app:weblogs' matching tags 'scripting techniques', 'XML', and 'CSV and other delimited files'

See Why PowerShell Can't Export Some Properties to CSV