Converting CSV FileS to XML with Powershell

Powershell is a pretty cool tool for many things including working with data. It’s just such a great time saver if you have to deal with multiple files or need to change them into different formats. Here’s how easy it is to turn a csv file into well-formed xml.

# csv file to convert
$csv = "C:\Users\Rhys\Desktop\csv\file1.csv";
# xml file to create
$xml = "C:\Users\Rhys\Desktop\csv\file1.xml";
Import-Csv -Path $csv | Export-Clixml -Path $xml;

This will produce xml looking something like this.

<?XML:NAMESPACE PREFIX = [default] NS = "" /><?XML:NAMESPACE PREFIX = [default] NS = "" /><objs xmlns="" version="">
<obj refid="0">
<tn refid="0">
<s n="FirstName">Rhys</s>
<s n="LastName">Campbell</s>
<s n="Age">29</s>
<obj refid="1">
<tnref refid="0"></tnref>
<s n="FirstName">Joe</s>
<s n="LastName">Bloggs</s>
<s n="Age">40</s>
<obj refid="2">
<tnref refid="0"></tnref>
<s n="FirstName">Steve</s>
<s n="LastName">Smith</s>
<s n="Age">35</s>

With the inclusion of the Get-ChildItem cmdlet we can merge multiple csv files into a single xml file with just a few lines of code.

# Folder of csv files
$csvFiles = Get-ChildItem -Path "C:\Users\Rhys\Desktop\csv\*" -Include *.csv;
# Process each csv file. Need to be the same structure
foreach($file in $csvFiles)
	$csvContent += Import-Csv -Path $file;
# Export the imported data as one xml file
$csvContent | Export-Clixml -Path C:\Users\Rhys\Desktop\csv\merged.xml;

Turning this back into a csv file is a simple Powershell one-liner!

# Turn this back into csv
Import-Clixml -Path C:\Users\Rhys\Desktop\csv\merged.xml | Export-Csv -Path C:\Users\Rhys\Desktop\csv\BackToCsv.csv -NoTypeInformation;

Check out my other data related Powershell posts; splitting csv files with Powershell, merging csv files with Powershell and trimming whitespace with Powershell.

Leave a Reply