Convert XML to CSV in PowerShell

We will discuss these two PowerShell utilities for converting XML to CSV in this article:

ConvertTo-Csv
Export-Csv

We will also see how to run the commands discussed in a PowerShell script (.ps1).

Using ConvertTo-Csv cmdlet to Convert XML to CSV

The ConvertTo-Csv cmdlet converts the object submitted to it into character-separated values (CSV) string. Instead of specifying the input object explicitly, you can also pipe objects to ConvertTo-Csv.

The cmdlet can be used to convert XML into CSV by following these steps:

Step 0 (Optional): View the contents of the XML file (marks2.xml) using the Get-Content command

1	Get-Content -Path <path\to\XML\file>

For Example,

1	Get-Content -Path 'C:\Users\kipro\Desktop\marks2.xml'

Output (contents of marks2.xml):

<?xml version="1.0" encoding="windows-1252" standalone="yes"?>
<Records>
  <Record>
	<Row
  	A="mark1"
  	B="mark2"
  	C="mark3"
  	D="gender"
	/>
  </Record>
  <Record>
	<Row
  	A="12"
  	B="15"
  	C="15"
  	D="M"
	/>
  </Record>
  <Record>
	<Row
  	A="15"
  	B="15"
  	C="16"
  	D="M"
	/>
  </Record>
</Records>

Note: If you want to follow along, save the above output as marks2.xml.

Step 1: Load the contents of the XML file to a variable

We use the Get-Content command to read the XML file content and store it in the $inputFile variable.

1	[XML]$inputFile = Get-Content -Path <path\to\the\XML\file>

For Example, the following line loads marks2.xml stored on the Desktop and saves it as $inputFile:

1	[XML]$inputFile = Get-Content -Path 'C:\Users\kipro\Desktop\marks2.xml'

Step 2: Actual conversion of XML to CSV

1	$InputFile.<locate_the_rows_on_the_tree>.ChildNodes \| ConvertTo-Csv -NoTypeInformation -Delimiter:',' \| Set-Content -Path <path\to\the\output\CSV>

The line above chains three commands. First, we need to locate the element containing the rows of our CSV in the XML tree.

Secondly, we pipe all the rows as child nodes into the ConvertTo-Csv cmdlet for conversion. With this command, you can choose the Delimiter (by default, it uses comma (,)) and issue -NoTypeInformation to remove the #TYPE information header from the output.

Lastly, we need to pipe the output of ConvertTo-Csv into a file using the Set-Content command. This step is necessary because ConvertTo-Csv does not write the returned CSV strings into a file by default.

Here is an example command to use for our marks2.xml.

1	$InputFile.Records.Record.ChildNodes \| ConvertTo-Csv -NoTypeInformation -Delimiter:',' \| Set-Content -Path 'C:\Users\kipro\Desktop\marks2.csv'

Note: We used $InputFile.Records.Record.ChildNodes because Records is the root element and the Record element contains items we need as rows for the CSV.

Step 3 (Optional): Checking the output

Let’s get the content of the output (marks2.csv) to verify that the conversion happened as expected.

1	Get-Content -Path 'C:\Users\kipro\Desktop\marks2.csv'

Output:

"A","B","C","D"
"mark1","mark2","mark3","gender"
"12","15","15","M"
"15","15","16","M"

Converting XML to CSV Using Export-Csv

Export-Csv is similar to ConvertTo-Csv, except that it saves the CSV strings to a file. As an example, let’s use Export-Csv to convert another XML file called books.xml.

Step 0: (Optional): View the contents of the XML file

1	Get-Content "C:\Users\kipro\Desktop\books.xml"

Output:

<?xml version="1.0"?>
<catalog>
   <book id="bk111">
  	<author>O'Brien, Tim</author>
  	<title>MSXML3: A Comprehensive Guide</title>
  	<genre>Computer</genre>
  	<price>36.95</price>
  	<publish_date>2000-12-01</publish_date>
  	<description>The Microsoft MSXML3 parser is covered in
  	detail, with attention to XML DOM interfaces, XSLT processing,
  	SAX and more.</description>
   </book>
   <book id="bk112">
  	<author>Galos, Mike</author>
  	<title>Visual Studio 7: A Comprehensive Guide</title>
  	<genre>Computer</genre>
  	<price>49.95</price>
  	<publish_date>2001-04-16</publish_date>
  	<description>Microsoft Visual Studio 7 is explored in depth,
  	looking at how Visual Basic, Visual C++, C#, and ASP+ are
  	integrated into a comprehensive development
  	environment.</description>
   </book>
</catalog>

Step 1: Save the contents of the XML file into a variable

1	[xml]$inputFile = Get-Content "C:\Users\kipro\Desktop\books.xml"

Step 2: Parse XML content to a CSV file

1	$inputFile.catalog.ChildNodes \| Export-Csv "C:\Users\kipro\Desktop\books.csv" -NoTypeInformation -Delimiter:","

The elements we want to use as rows in the CSV file are inside the catalog, the root element, which is why we use $inputFile.catalog.ChildNodes

Step 3 (Optional): Confirm that conversion happened as expected by viewing the content of the output

Execute the following on PowerShell:

1	Get-Content "C:\Users\kipro\Desktop\books.csv"

Output (truncated):

"id","author","title","genre","price","publish_date","description"
"bk111","O'Brien, Tim","MSXML3: A Comprehensive Guide","Computer","36.95","2000-12-01","The Microsoft MSXML3 parser is covered in
  	detail, with attention to XML DOM interfaces, XSLT processing,
  	SAX and more."
…

Putting it All Together in a PowerShell Script

First, save the following content into a PowerShell script called convert_xml_to_csv.ps1 (or any other name, really).

# Load the content of books.xml into a variable $inputFile

[XML]$inputFile = Get-Content -Path 'C:\Users\kipro\Desktop\ books.xml'

# Get the required data, convert that into CSV strings and save the results to a file

$InputFile.Records.Record.ChildNodes | ConvertTo-Csv -NoTypeInformation -Delimiter:',' | Set-Content -Path 'C:\Users\kipro\Desktop\marks2.csv'

# Get the content of the output file to confirm that the conversion happened

Get-Content -Path 'C:\Users\kipro\Desktop\marks2.csv'

Then execute the script by running the following command on PowerShell

1	cd <path_to_directory_containing_the_script> ; .\script.ps1