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




10

CLARK
MILLER
KING



20

SMITH
FORD
ADAMS
SCOTT
JONES



30

ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES





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

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