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

  • Question

  • 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
    Friday, January 24, 2014 2:19 AM

Answers

  • 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
    Friday, January 24, 2014 2:54 AM

All replies

  • 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.
    Friday, January 24, 2014 2:27 AM
  • 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>


    Friday, January 24, 2014 2:43 AM
  • 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
    Friday, January 24, 2014 2:54 AM
  • 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;}
    

    Friday, January 24, 2014 3:27 AM