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

Question

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

mklement0 · Accepted Answer · 2019-11-18 23:21:34Z

$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"

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related