PowerShell Export-CSV When Source Object Contains Both Strings And Collections

My goal is to process the data inside a bunch of XML log files.  I would like to get them into CSV format so that end users can easily view them in Excel.

First, I store all the data in a variable called $log.  This works without a hitch

[xml]$log = Get-Content file.xml    #Store the XML data in an object in memory

Let's look at what we've got:

PS U:\> $log

xml                                                         log
---                                                         ---
version="1.0" encoding="utf-8"                              log

There was not much there, so let's drill down another level:

PS U:\> $log.log

entry
-----
{entry, entry, entry, entry...}

Still not what we're looking for, so we'll dig deeper.  Eureka!  Well, almost...  

PS U:\> $log.log.entry

log_time     : 20111009-01:15:32
description  : description
service      : HTTP
sessionid    : 1234567890
type         : 1
severity     : 0
user         : username
host         : subdomain.domain.tld
lstnconnaddr : 192.168.0.17:443
cliconnaddr  : 8.8.8.8:17251
cmd          : Download
params       : params
absfile      : absfile
filesize     : 1024
transtime    : 13001
sguid        : ABC-123-blahblahblah

Here we see the first symptoms of the problem.  Look at the three parameters that don't show their values:  description, params, and absfile.  Why is that?  Here's why:

PS U:\> $log.log.entry.description | Get-Member

   TypeName: System.Xml.XmlElement

Instead of a string as expected, we have a collection of XmlElements.  If I try to export the relevant data to CSV, each of those three columns (description, params, and absfile) is full of repeated entries showing "System.Xml.XmlElement" instead of actually showing me the data.

$log.log.entry | Export-CSV log.csv

The ExpandProperty parameter for Select-Object does what I need, but it only allows Select-Object to work with that one property, so I lose all my other data which makes it useless:

$log.log.entry | Select * -ExpandProperty Description | Export-CSV log.csv

OK, alright, everyone on the Internet is saying to create a custom property to deal with this.  The Internet doesn't lie, right?  So instead I try it like this:

$log.log.entry | Select log_time,service,sessionid,type,severity,user,host,lstnconnaddr,cliconnaddr,cmd,errnum,sguid,@{n="description";e={($_ | Select-Object -ExpandProperty description) -join " "}} | Export-CSV log.csv"

Instead of the intended results, the Description column in the CSV is now filled with repetitions of the word "description" instead of "System.Xml.XmlElement".  It still does not contain the actual descriptions.

I know the actual descriptions do exist, and here is how:

$log.log.entry.description

The line of code above will print out all the descriptions to the console.

So, I am completely lost and a tad frustrated with PowerShell right now.  Can anyone tell me what I'm missing?  How the heck do I get all this information into my CSV file?

Hope this question makes sense, let me know what I can do to improve it.




  • Edited by Fëanor Friday, January 24, 2014 2:35 AM
January 24th, 2014 5:19am

You're on the right track; you have to flatten your XML's structure to go into a CSV file. If you post some of the actual XML code, that should provide enough information for us to suggest a fix.
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 5:27am

Awesome, thanks for the help!  Here is a complete sample entry from the XML, starting from line 1 in the XML:

<?xml version="1.0" encoding="utf-8" ?>
<log>
  <entry>
    <log_time>20111009-01:15:32</log_time>
    <description><![CDATA[]]></description>
    <service>HTTP</service>
    <sessionid>1234567890</sessionid>
    <type>1</type>    <severity>0</severity>
    <user>username</user>
    <host>subdomain.domain.tld</host>
    <lstnconnaddr>192.168.0.17:443</lstnconnaddr>
    <cliconnaddr>8.8.8.8:17251</cliconnaddr>
    <cmd>Download</cmd>
    <params><![CDATA[HereIsTheFileNameIWanted.pdf]]></params>
    <absfile><![CDATA[/folder/subfolder/filepath/HereIsTheFileNameIWanted.pdf]]></absfile>
    <filesize>1024</filesize>
    <transtime>13001</transtime>
    <sguid>ABC-123-blahblahblah</sguid>
  </entry>


January 24th, 2014 5:43am

Your issue seems to correlate with your CDATA fields. You can access said fields with:

$log.log.entry.description.InnerText

or

$log.log.entry.description."#cdata-section"


Then use

.get_FirstChild().get_Data()

To access it.
  • Edited by Dave Lasley Friday, January 24, 2014 2:56 AM
  • Marked as answer by Fëanor Friday, January 24, 2014 3:12 AM
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 5:54am

Thanks!  That did the trick (using either the the .InnerText property or the '#cdata-section' property).  Here is my final line of code to create the CSV:

$log.log.entry | Select log_time,service,sessionid,type,severity,user,host,lstnconnaddr,cliconnaddr,cmd,errnum,sguid,@{n="description";e={$_.description.InnerText}},@{n="params";e={$_.params.InnerText}},@{n="absfile";e={$_.absfile.InnerText}} | Export-CSV ($filepath + "log.csv")

Part of me wonders why InnerText didn't appear when I ran the code below.  The other part of me wonders why I didn't use the '#cdata-section' property which was there, plain as day.  DOH!

PS U:\> $log.log.entry.description | gm


   TypeName: System.Xml.XmlElement

Name                 MemberType            Definition
----                 ----------            ----------
ToString             CodeMethod            static string XmlNode(psobject instance)
AppendChild          Method                System.Xml.XmlNode AppendChild(System.Xml.XmlNode newChild)
Clone                Method                System.Xml.XmlNode Clone(), System.Object ICloneable.Clone()
CloneNode            Method                System.Xml.XmlNode CloneNode(bool deep)
CreateNavigator      Method                System.Xml.XPath.XPathNavigator CreateNavigator(), System.Xml.XPath.XPath...
Equals               Method                bool Equals(System.Object obj)
GetAttribute         Method                string GetAttribute(string name), string GetAttribute(string localName, s...
GetAttributeNode     Method                System.Xml.XmlAttribute GetAttributeNode(string name), System.Xml.XmlAttr...
GetElementsByTagName Method                System.Xml.XmlNodeList GetElementsByTagName(string name), System.Xml.XmlN...
GetEnumerator        Method                System.Collections.IEnumerator GetEnumerator(), System.Collections.IEnume...
GetHashCode          Method                int GetHashCode()
GetNamespaceOfPrefix Method                string GetNamespaceOfPrefix(string prefix)
GetPrefixOfNamespace Method                string GetPrefixOfNamespace(string namespaceURI)
GetType              Method                type GetType()
HasAttribute         Method                bool HasAttribute(string name), bool HasAttribute(string localName, strin...
InsertAfter          Method                System.Xml.XmlNode InsertAfter(System.Xml.XmlNode newChild, System.Xml.Xm...
InsertBefore         Method                System.Xml.XmlNode InsertBefore(System.Xml.XmlNode newChild, System.Xml.X...
Normalize            Method                void Normalize()
PrependChild         Method                System.Xml.XmlNode PrependChild(System.Xml.XmlNode newChild)
RemoveAll            Method                void RemoveAll()
RemoveAllAttributes  Method                void RemoveAllAttributes()
RemoveAttribute      Method                void RemoveAttribute(string name), void RemoveAttribute(string localName,...
RemoveAttributeAt    Method                System.Xml.XmlNode RemoveAttributeAt(int i)
RemoveAttributeNode  Method                System.Xml.XmlAttribute RemoveAttributeNode(System.Xml.XmlAttribute oldAt...
RemoveChild          Method                System.Xml.XmlNode RemoveChild(System.Xml.XmlNode oldChild)
ReplaceChild         Method                System.Xml.XmlNode ReplaceChild(System.Xml.XmlNode newChild, System.Xml.X...
SelectNodes          Method                System.Xml.XmlNodeList SelectNodes(string xpath), System.Xml.XmlNodeList ...
SelectSingleNode     Method                System.Xml.XmlNode SelectSingleNode(string xpath), System.Xml.XmlNode Sel...
SetAttribute         Method                void SetAttribute(string name, string value), string SetAttribute(string ...
SetAttributeNode     Method                System.Xml.XmlAttribute SetAttributeNode(System.Xml.XmlAttribute newAttr)...
Supports             Method                bool Supports(string feature, string version)
WriteContentTo       Method                void WriteContentTo(System.Xml.XmlWriter w)
WriteTo              Method                void WriteTo(System.Xml.XmlWriter w)
Item                 ParameterizedProperty System.Xml.XmlElement Item(string name) {get;}, System.Xml.XmlElement Ite...
#cdata-section       Property              string #cdata-section {get;set;}

January 24th, 2014 6:27am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics