Using the Export-CSV and ConvertTo-CSV cmdlets in PowerShell, you can convert XML file to CSV file.
XML (Extensible Markup Language) is a markup language similar to HTML, where you can define your own tags to store and transport data. It’s human-readable and machine-readable and can be easily parsed.
CSV (Comma-Separated Values) file stores the data in a tabular format where each value is separated by a delimiter comma, semi-column, or any other characters.
In this article, we will discuss how to convert XML to CSV file using Export-CSV and ConvertTo-CSV cmdlets.
PowerShell – XML to CSV using Export-CSV
Export-CSV cmdlet in PowerShell creates a CSV file from the object’s input to it and exports the CSV file to the specified location.
Let’s consider an example to convert XML to CSV in PowerShell.
Get-Content -Path D:\LogTest\FTP-02\PowerShell_Books.xml
In the above PowerShell script, we have used the Get-Content cmdlet to read the XML file content and display it.
The output of the above command is:
PS D:\> Get-Content -Path D:\LogTest\FTP-02\PowerShell_Books.xml <?xml version="1.0"?>
<catalog>
<book id="b101">
<author>ShellGeek</author>
<title>PowerShell Guide</title>
<genre>Programming</genre>
<price>50</price>
<publish_date>2022-01-01</publish_date>
<description>Master the PowerShell in 30 days</description>
</book>
<book id="b102">
<author>Microsoft</author>
<title>Windows PowerShell</title>
<genre>Programming</genre>
<price>25</price>
<publish_date>2021-12-16</publish_date>
<description>Step by Step Windows PowerShell</description>
</book>
</catalog>
PS D:\> Stores the XML file content in a variable $xmlFile. In the XML, every element in the XML file is an element node.
To convert the XML file to a CSV file, we need to traverse it to XML child nodes and pass it as input to the Export-CSV command in PowerShell.
Export-CSV command takes XML nodes as input to create a CSV file from the XML child nodes and export them to a CSV file with a delimiter comma.
$xmlFile = Get-Content -Path D:\LogTest\FTP-02\PowerShell_Books.xml
$xmlFile.catalog.ChildNodes | Export-Csv -Path D:\LogTest\FTP-02\PowerShell_Books.csv -NoTypeInformation -Delimiter:","
The output of the above PowerShell script for XML output to a CSV file using the Export-CSV cmdlet is:
In the above output, it exports XML to a CSV file, and using the Get-Content command, we read the CSV file and display information on the PowerShell terminal.
Cool Tip: How to split the large file into smaller files using PowerShell!
PowerShell XML ConvertTo-CSV file
Using the ConvertTo-CSV cmdlet in PowerShell, you can convert the XML file to a CSV file.
We will refer to the PowerShell_Books.xml file used in the above example. This XML file contains information about Books.
To convert XML to CSV file for Books XML nodes, use the ConvertTo-CSV command as given below.
$xmlFile = Get-Content -Path D:\LogTest\FTP-02\PowerShell_Books.xml $xmlFile.catalog.ChildNodes | ConvertTo-Csv -NoTypeInformation -Delimiter:"," | Set-Content -Path D:\LogTest\FTP-02\PowerShell_Books_2.csv Get-Content -Path D:\LogTest\FTP-02\PowerShell_Books_2.csv
In the above PowerShell script, the Get-Content command read the XML file content and stores it in the $xmlFile variable.
This $xmlFile variable contains entire XML nodes and passes them as input to the ConvertTo-CSV command which uses the Delimeter character comma for the CSV file. It then passes the CSV file content to Set-Content to write it on the specified file path.
The output of the above PowerShell script to export XML nodes to CSV using the ConvertTo-CSV command is:
Cool Tip: How to export CSV with date in file name using PowerShell!
Conclusion
I hope the above article on how to convert XML to CSV file using the Export-CSV and ConvertTo-CSV file is helpful to you.
You can find more topics about PowerShell Active Directory commands and PowerShell basics on the ShellGeek home page.