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

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

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

1 Comment

Leave a comment

Your email address will not be published.