xml to csv in powershell

Powershell is very strong with XML, to convert an XML document to something flat like a CSV file, it is incredibly powerfull.

Let’s take a file called emp.xml


<EMPTABLE>
  <DEPT>
    <DEPTNO>10</DEPTNO>            
    <EMPLIST>
      <ENAME>CLARK</ENAME>
      <ENAME>MILLER</ENAME>
      <ENAME>KING</ENAME>
    </EMPLIST>
  </DEPT>
  <DEPT>
    <DEPTNO>20</DEPTNO>
    <EMPLIST>
       <ENAME>SMITH</ENAME>
       <ENAME>FORD</ENAME>
       <ENAME>ADAMS</ENAME>
       <ENAME>SCOTT</ENAME>
       <ENAME>JONES</ENAME>
    </EMPLIST>
  </DEPT>
  <DEPT>
    <DEPTNO>30</DEPTNO>     
    <EMPLIST>
       <ENAME>ALLEN</ENAME>
       <ENAME>WARD</ENAME>
       <ENAME>MARTIN</ENAME>
       <ENAME>BLAKE</ENAME>
       <ENAME>TURNER</ENAME>
       <ENAME>JAMES</ENAME>
    </EMPLIST>
  </DEPT>
</EMPTABLE>

To get all employees, it is awfully easy
([xml](gc emp.xml)).EMPTABLE.DEPT.EMPLIST.ENAME


CLARK
MILLER
KING
SMITH
FORD
ADAMS
SCOTT
JONES
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

Now I actually want to have each employee together with his department. I create an object for each department add the ename and the deptno


$d=([xml](gc emp.xml)).EMPTABLE.DEPT | % { 
  foreach ($i in $_.EMPLIST.ENAME) {
    $o = New-Object Object
    Add-Member -InputObject $o -MemberType NoteProperty -Name DEPTNO -Value $_.DEPTNO
    Add-Member -InputObject $o -MemberType NoteProperty -Name ENAME -Value $i
    $o
  }
}
$d


DEPTNO     ENAME
------     -----
10         CLARK
10         MILLER
10         KING
20         SMITH
20         FORD
20         ADAMS
20         SCOTT
20         JONES
30         ALLEN
30         WARD
30         MARTIN
30         BLAKE
30         TURNER
30         JAMES

This could be convert to multiple format.

HTML
$d|ConvertTo-HTML

DEPTNO ENAME
10 CLARK
10 MILLER
10 KING
20 SMITH
20 FORD
20 ADAMS
20 SCOTT
20 JONES
30 ALLEN
30 WARD
30 MARTIN
30 BLAKE
30 TURNER
30 JAMES

CSV
$d|ConvertTo-CSV


"DEPTNO","ENAME"
"10","CLARK"
"10","MILLER"
"10","KING"
"20","SMITH"
"20","FORD"
"20","ADAMS"
"20","SCOTT"
"20","JONES"
"30","ALLEN"
"30","WARD"
"30","MARTIN"
"30","BLAKE"
"30","TURNER"
"30","JAMES"

JSON
$d|ConvertTo-JSon


[
    {
        "DEPTNO":  "10",
        "ENAME":  "CLARK"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "MILLER"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "KING"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SMITH"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "FORD"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "ADAMS"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SCOTT"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "JONES"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "ALLEN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "WARD"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "MARTIN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "BLAKE"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "TURNER"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "JAMES"
    }
]

Or even to xml with ($d|ConvertTo-XML).OuterXml

It is so lightening fast that you could process pretty large files (millions of lines) in just a few seconds

One Reply to “xml to csv in powershell”

Leave a Reply