Sometimes I’m amazed by how some tasks are easy to carry out using PowerShell. One such task is converting common data types such as CSV, JSON and XML. PowerShell has built-in support for all these types.
Convert CSV to JSON
For example, let’s say we have the following customer data in a CSV file named customers.csv
Id,FirstName,LastName,Country,Status 1,James,Monroe,USA,Active 2,Diane,Wheatley,UK,Active 3,Sara,Bailey,UK,Suspended
If we want to convert this data into JSON, we can run the following command in PowerShell:
Import-Csv ./customers.csv | ConvertTo-Json | Out-File ./customers.json
we get the following JSON output:
[
{
"Id": "1",
"FirstName": "James",
"LastName": "Monroe",
"Country": "USA",
"Status": "Active"
},
{
"Id": "2",
"FirstName": "Diane",
"LastName": "Wheatley",
"Country": "UK",
"Status": "Active"
},
{
"Id": "3",
"FirstName": "Sara",
"LastName": "Bailey",
"Country": "UK",
"Status": "Suspended"
}
]
The output JSON is indented by default, so very easy to read as well.
Convert JSON to CSV
If we want to reverse the process and create a CSV file from a JSON input, we can run the following:
(Get-Content -Path ./customers.json -Raw | ConvertFrom-Json) | Export-CSV ./customers.csv
and get a CSV that looks like:
"Id","FirstName","LastName","Country","Status" "1","James","Monroe","USA","Active" "2","Diane","Wheatley","UK","Active" "3","Sara","Bailey","UK","Suspended"
I used the Export-CSV cmdlet in this example because it saves the output to a file. We can also use ConvertTo-Csv cmdlet
For example, the following snippet print the results to the console:
(Get-Content -Path ./customers.json -Raw | ConvertFrom-Json) | ConvertTo-Csv | Write-Host
Convert JSON to XML
Similar to JSON, we can use the ConvertTo-XML cmdlet to create an XML file out of our sample JSON:
Export-Clixml -Depth 3 -InputObject ((Get-Content -Path ./customers.json -Raw) | ConvertFrom-Json) -Path ./customers.xml
and the output looks like this:
<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
<Obj RefId="0">
<TN RefId="0">
<T>System.Object[]</T>
<T>System.Array</T>
<T>System.Object</T>
</TN>
<LST>
<Obj RefId="1">
<TN RefId="1">
<T>System.Management.Automation.PSCustomObject</T>
<T>System.Object</T>
</TN>
<MS>
<S N="Id">1</S>
<S N="FirstName">James</S>
<S N="LastName">Monroe</S>
<S N="Country">USA</S>
<S N="Status">Active</S>
</MS>
</Obj>
<Obj RefId="2">
<TNRef RefId="1" />
<MS>
<S N="Id">2</S>
<S N="FirstName">Diane</S>
<S N="LastName">Wheatley</S>
<S N="Country">UK</S>
<S N="Status">Active</S>
</MS>
</Obj>
<Obj RefId="3">
<TNRef RefId="1" />
<MS>
<S N="Id">3</S>
<S N="FirstName">Sara</S>
<S N="LastName">Bailey</S>
<S N="Country">UK</S>
<S N="Status">Suspended</S>
</MS>
</Obj>
</LST>
</Obj>
</Objs>
A bit noisy, but the data is there.
Conclusion
Using PowerShell’s built-in cmdlets, we can easily convert between common data types such as CSV, JSON and XML. For a complete list of supported cmdlets, please check the link in the resources section.
