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