1

I'm trying to convert the following XML to CSV using PowerShell 2.0, I can get some of the data out but when trying to go through the XML objects I can't seem to get just the data in a format that would allow me to make a good table.

What I have is something like this, with multiple item's

  1. <root type="array">   
      <item type="object">
        <short_version type="string">11</short_version>
        <long_name type="string">Internet Explorer</long_name>
        <api_name type="string">internet explorer</api_name>
        <long_version type="string">11.0.9600.16384.</long_version>
        <latest_stable_version type="string"></latest_stable_version>
        <automation_backend type="string">webdriver</automation_backend>
        <os type="string">Windows 2012 R2</os>
      </item>
      ... 
    </root>
    

Either I end up with the Type or if I try and access the InnerHTML I get only the values, but in a long string.

I'm have so far:

[xml]$convertMe = Get-Content $jsonTemp
$convertMe.SelectNodes("//item") | % { $_.InnerText }

How can I get this in a nice CSV format like:

short_version,long_name,api_name,long_version,latest_stable_version,automation_backend,os 11,Internet Explorer,internet explorer,11.0.9600.16384,,webdriver,Windows 2012 R2

2
  • 1
    what do you want the csv to look like? Commented May 1, 2014 at 14:18
  • Ideally a table where I can view the Item ChildItems in a CSV file, so the short_version, os, api_name and so on are the column headers with the values added for each item in the file. Commented May 1, 2014 at 14:40

3 Answers 3

3

This is a hard-coded and long solution, but it works. :) Try:

$xml = [xml](Get-Content .\test.xml)

$xml.root.item | Select-Object @(
@{l="short_version";e={$_.short_version."#text"}},
@{l="long_name";e={$_.long_name."#text"}},
@{l="api_name";e={$_.api_name."#text"}},
@{l="long_version";e={$_.long_version."#text"}},
@{l="latest_stable_version";e={$_.latest_stable_version."#text"}},
@{l="automation_backend";e={$_.automation_backend."#text"}},
@{l="os";e={$_.os."#text"}}) |
Export-Csv test.csv -NoTypeInformation

test.csv

"short_version","long_name","api_name","long_version","latest_stable_version","automation_backend","os"
"11","Internet Explorer","internet explorer","11.0.9600.16384.",,"webdriver","Windows 2012 R2"

And alternative and probably slower solution:

$xml = [xml](Get-Content .\test.xml)

#Foreach item
$xml.root.item | ForEach-Object {
    $obj = New-Object psobject
    $_.GetEnumerator() | ForEach-Object {
        #Get all properties/elements and values
        $obj | Add-Member -MemberType NoteProperty -Name $_.Name -Value $_.InnerText
    }
    $obj
} |
#Set property order. This also makes sure that all items exports the same properties(which a csv needs)
Select-Object short_version,long_name,api_name,long_version,latest_stable_version,automation_backend,os |
#Export to csv
Export-Csv test.csv -NoTypeInformation
Sign up to request clarification or add additional context in comments.

8 Comments

If you are going to define the properties in the Select is the second option really any more dynamic?
No, I guess not. :P Select-Object was added later to the second solution. It's less messy though :)
Not to put too fine a point on my own ignorance, but what's wrong with your second option without the Select portion? It looks very similar to my answer, and if my answer's no good I'd love to know why that is so I don't mis-lead people in the future.
I thought your solution was good. That's what made me write a similar, though different solution. Select-Object is "required" in such a solution. If not present, then the first item's properties will define the csv-header, and that may cause problems if it's missing one or more properties. :)
Oh my gosh, that totally explains what happened part way through wrapping my head around what I ended up with and had a result with 1 column, 1 value, and a bunch of nulls. Thank you, it all makes sense now.
|
1

It may be a little messy but it gets you exactly what you're looking for.

[xml]$convertMe = Get-Content $jsonTemp
[Array]$MeConverted = $convertMe.GetElementsByTagName('item')
$Collection = @()
ForEach($Record in $MeConverted){
    $Output = new-object psobject
    $Record.selectnodes("*")|%{Add-Member -InputObject $Output -MemberType NoteProperty -Name $_.Name -Value $_.'#text'}
    If($Collection){
        $T2Keys = $Collection|gm|?{$_.MemberType -match "Property"}|Select -ExpandProperty Name
        $T1Keys = $Output|gm|?{$_.MemberType -match "Property"}|Select -ExpandProperty Name
        $KeysToAdd = $T2Keys|?{$T1Keys -notcontains $_}
        $KeysToAdd|%{$Collection|Add-Member $_ ""}
    }
    $Collection += $Output
}
$Collection | Export-CSV file.csv -notype

Edit: Frode's looks a lot cleaner in my opinion, but mine does have the advantage of not having to know all of the child nodes' names.

Edit2: Fixed glaring flaws, obviously there will be more than one item and I totally didn't account for that. That just made my code a lot bigger because I need to account for additional potential properties. Now for each item it checks for additional properties that weren't in the collection before and adds them before adding that record to the collection.

Edit3: Updated the Add-Member command for backwards compatibility. I wasn't aware that -NotePropertyName/Value are v3+ until now.

4 Comments

I think you have a few bugs/typos. What is $TestOut? Also, you're missing a foreach-loop. The sample only has one item, but the real xml has multiple item-elements. :)
@FrodeF. I did indeed. I had changed variable names when I posted the answer to have them make more sense in context, and evidently missed one. Also, the multiple records thing should have been a no brainer, but obviously I need more caffeine today. Updated, and should be functional now. Thank you for pointing those issues out.
Very nice, although like Frode's option the NotePropertyName kept giving me errors, maybe it's not installed on my PSv2 machine.
I updated my script too, I wasn't aware that NotePropertyName and NotePropertyValue are v3+ syntax. You totally should update, Frode's right on that one. Lots of nice little changes since v2.
0

I threw together a little PowerShell function that does this. It assumes a lot about your XML structure, but it worked with the content you posted. You'll just have to pipe it to an Out-File to get your CSV.

Function ConvertFrom-XMLtoCSV {
    [CmdletBinding()]
    <#
    .Synopsis
       Convert a uniform XML file to CSV with element names as headers
    .DESCRIPTION
       Takes a uniformed XML tree and converts it to CSV based on the XPath given.

       For example, assume a structure like this:
       <root>
           <item>
               <element1>Content1</element1>
               <element2>Content2</element2>
           </item>
           <item>
               <element1>Content1</element1>
               <element2>Content2</element2>
           </item>
       <root>

    .PARAMETER Path
        The path to the XML File

    .PARAMETER XPath
        The XPath query to the items that should be converted

    .EXAMPLE
       ConvertFrom-XMLtoCSV -Path .\file.xml -XPath "//item" 
    #>
    Param (
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,Position=0)][String] $Path,
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,Position=1)][String] $XPath
    )
    Begin {
        if (Test-Path $Path) {
            [XML] $XML = Get-Content $Path -Raw
        } else {
            Throw [System.IO.FileNotFoundException] "XML file was not found at the given path"
        }
        $NodeCount = $XML.SelectNodes($XPath).Count
        $FileHeaders = [System.String]::Join(",",$($XML.SelectNodes("$XPath[1]/node()") | ForEach-Object { $_.ToString()}))
        $Content = @()
    }
    Process {
        $Content += $FileHeaders
        For ($i = 1; $i -le $NodeCount; $i++) { 
            $Content += [System.String]::Join(",",$($xml.SelectNodes("$XPath[$i]/node()") | ForEach-Object {$_."#text"}))
        }

        return $Content
    }
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.