Use PowerShell to Work with CSV Formatted Text

re: Use PowerShell to Work with CSV Formatted Text

Jeffrey S. Patton — Wed, 16 Nov 2011 17:17:30 GMT

Ok, so maybe someone can answer this question for me. What is the difference between these two commands? Because the output is COMPLETELY different.

Export-Csv -InputObject $NewReport -Path .\sample.csv -Force

$NewReport |Export-Csv -Path .\sample.csv -Force

The first yields a csv of apparently the object, and the second yields a csv with the actual data in it.

re: Use PowerShell to Work with CSV Formatted Text

Jeffrey S. Patton — Mon, 14 Nov 2011 22:58:23 GMT

Ok, I've run into something like this and I'm stumped. Here is what I have, code + output.

$Events = Get-WinEvent -ErrorAction Stop -FilterHashtable @{LogName='ForwardedEvents';ID=4733}

if ($Events.Count)

{

$Events = $Events[0]

}

$XMLEvent = [XML]$Events.ToXML()

$tempReport = $XMLEvent.Event.EventData.Data

$NewReport = @()

foreach ($line in $tempReport)

{

[string]$EventField = [string]$Line.Name.ToString()

[string]$EventData = [string]$Line."#text".ToString()

$LineItem = New-Object -TypeName PSObject -Property @{

EventField = $EventField

EventData = $EventData

}

$NewReport += $LineItem

}

$NewReport

ConvertTo-Csv -InputObject $NewReport

EventField EventData

---------- ---------

MemberName -

MemberSid S-1-5-21-1248838550-3875098049-756341321-...

TargetUserName Administrators

TargetDomainName Builtin

TargetSid S-1-5-32-544

SubjectUserSid S-1-5-21-1248838550-3875098049-756341321-500

SubjectUserName Administrator

SubjectDomainName COMPANY

SubjectLogonId 0x23a98

PrivilegeList -

#TYPE System.Object[]

"Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"

"10","10","10","1","System.Object[]","False","True","False"

Originally I was doing this.

$tempReport += $XMLEvent.Event.EventData.Data `

|Select-Object -Property @{Label='EventField';Expression="Name"}, @{Label='EventData';Expression="#text"}

I thought perhaps because I was tossing XML data into the array, and then later adding more EventData and EventField items it was cause this problem. This seemed obvious because when I would ConvertTo-CSV the additional field items, they would convert over just fine.

If anyone could shed light I would really appreciate it.

Thanks!

re: Use PowerShell to Work with CSV Formatted Text

Ed Wilson — Mon, 07 Nov 2011 21:42:01 GMT

@Terry E. Dow The problem occurs because the "property" that you are seeing, contains another object that is rather rich. This is not just with Get-Service, it exists on MANY cmdlets, such as Get-Process. The way to view the rich data is to pipeline to Select-Object and use the ExpandProperty parameter to expand the property. You could then pipeline that to ExportCSV if you wish. There are problems with CSV formats because it is essentially two dimensional, and there is not way to handle another object in a property. XML can do this. I am going to write a Hey Scripting Guy blog article about this. See November 15, 2011 article.

re: Use PowerShell to Work with CSV Formatted Text

Terry E Dow — Mon, 07 Nov 2011 19:27:37 GMT

What's the most elegant way to use EXPORT-CSV command to handle mulit-value elements? GET-SERVICE | FORMAT-LIST vs. GET-SERVICE | EXPORT-CSV Service.csv. The latter generates a column called DependentServices with its type (System.ServiceProcess.ServiceController[]) instead of the array values. Yet FORMAT-LIST and practically all other format/export commands expands these. Without writing code specific for this one column from this one command, how can I get EXPORT-CSV to expand array values in general?

re: Use PowerShell to Work with CSV Formatted Text

Ed Wilson — Mon, 26 Sep 2011 22:44:33 GMT

@camlost, @Craig you are absolutely correct that the -encoding parameter is excellent to use. Keep in mind that Windows PowerShell defaults to unicode, but there are certainly times you need ASCII. For those cases, use -encoding ASCII

@Q thanks. I am glad you liked the article.

re: Use PowerShell to Work with CSV Formatted Text

Craig Lussier — Sun, 25 Sep 2011 00:06:34 GMT

Ed - Thanks for this article. A lot of folks encounter the issues that you propose a solution for in this post. As indicated by 'camlost', a useful (in my opinion given the appropriate scenario is an important) parameter of Export-CSV cmdlet is the -Encoding parameter. I have encountered many scenarios where this parameter makes all the difference between frustration and success with regards to the expected csv output. As a suggestion - could you write a 'gotcha' post for the community on the usage of the -Encoding parameter and plausible scenarios where this parameter makes a difference? Cheers. Craig.

re: Use PowerShell to Work with CSV Formatted Text

Fri, 23 Sep 2011 10:30:12 GMT

Thanks for the article.

re: Use PowerShell to Work with CSV Formatted Text

camlost — Fri, 23 Sep 2011 10:28:25 GMT

Another useful parameter is -Encoding.

Example:

Get-Mailbox mbox_* | Select-Object DisplayName, Name, Database, ProhibitSendReceiveQuota | Export-CSV \mbox.csv -NoTypeInformation -Delimiter ";" -Encoding utf8; start \mbox.csv