CODEWITHSUNDEEP
xmlpowershell
rarpal
rarpal

Reputation: 183

How to export XML Powershell system.object[] arrays to CSV file

I have the following XML received from a web service in a PowerShell variable $res

<GRID>
  <DATA>
    <R>
      <D>2645</D>
      <D>[email protected]</D>
      <D>2019-09-27 10:17:36.0</D>
      <D>114041</D>
      <D>Awaiting Planning</D>
      <D>Work Planned</D>
    </R>
    <R>
      <D>2649</D>
      <D>[email protected]</D>
      <D>2019-09-27 10:33:24.0</D>
      <D>114043</D>
      <D>Awaiting Release</D>
      <D>Awaiting Planning</D>
    </R>
    <R>
      <D>2652</D>
      <D>[email protected]</D>
      <D>2019-09-27 10:36:53.0</D>
      <D>114041</D>
      <D>Awaiting Planning</D>
      <D>Work Planned</D>
    </R>
  </DATA>
</GRID>

I want to export the R nodes as rows and D node values as columns into a CSV file in PowerShell. I did the following:

$res.GRID.DATA.R | Export-Csv .\GRID1.csv

The result is the correct number of rows, but columns do not appear, instead I get the object type like so:

"D"
"System.Object[]"
"System.Object[]"
"System.Object[]"

What is the quickest way to get the columns converted to comma separated values ?

Many Thanks

Upvotes: 1

Views: 794

Answers (1)

mklement0
mklement0

Reputation: 439672

$res.GRID.DATA.R outputs System.Xml.XmlElement instances, which Export-Csv doesn't (meaningfully) support.

You have to transform these elements into [pscustomobject] instances first.

The challenge is that there are no distinct property names, given that that the child elements of the input elements are all named D.

The following solution therefore constructs property names C1, C2, ... to allow the transformation into a [pscustomobject].

$x.GRID.DATA.R | ForEach-Object { 
  $oht = [ordered] @{}
  $i = 1
  foreach ($val in $_.GetEnumerator().InnerText) {
    $oht[('C' + $i++)] = $val
  }
  [pscustomobject] $oht
} | Export-Csv .\GRID1.csv

With your sample XML file, the above yields:

"C1","C2","C3","C4","C5","C6"
"2645","[email protected]","2019-09-27 10:17:36.0","114041","Awaiting Planning","Work Planned"
"2649","[email protected]","2019-09-27 10:33:24.0","114043","Awaiting Release","Awaiting Planning"
"2652","[email protected]","2019-09-27 10:36:53.0","114041","Awaiting Planning","Work Planned"

Upvotes: 1

Related Questions