Revisiting Exporting Data

Sup PSHomies!

I’ve been playing with different formats lately. I’d like to share a few thoughts on the subject if I may… For demo purposes I’ll be using the following cmdlets: Export-Csv, Export-Clixml and ConvertTo-Json!

Export-Csv

I’ve talked about my love for exporting to csv in the past. Here’s thing, exporting to CSV treats everything as a string. For reporting purposes this might not be an issue. When it comes to nested objects… Yeah… Then you’re better off exporting to XML. Incidentally Jeff Hicks has a great blog on this topic, you should definitely check it out! CSV is still my goto format because of reporting in Excel, although, I’ve been using Doug Finke’s ImportExcel module more and more! Doug’s module cuts out the middle man and can export to Excel without having to export as a CSV first. It does a whole lot more! Worth looking into!

Export-Clixml

Exporting a nested object is pretty straightforward using Export-Clixml. The structure isn’t pretty though. That was the main reason I didn’t use the cmdlet. Export-Clixml is great when used in combination with Import-Clixml, it restores your nested object without a hitch! You can export your results, send the file and import elsewhere for further processing if needed. When I think of xml, I immediately conjure up ideas of html reporting. The xml tags are too cryptic for any css style, I wouldn’t even know where to begin. I recently discovered PScribo (Thanks to the PowerShell Summit in Stockholm), a module by Ian Brighton! This made html reporting a breeze! All I did was import my XML file back into PowerShell to retrieve my nested object and I did the rest in PowerShell! That was awesome!

ConvertTo-Json

The ConvertTo-Json cmdlet has been introduced in PowerShell version 3.0. Back then I was a stickler for XML so I briefly looked at it and forgot all about it… That is until Azure Resource Manager came along. If you’re doing anything with Azure Resource Manager then Json should be on your radar. If you’re not convinced just look at the ARM Templates out there. Json is a lot easier on the eyes for sure. Still not conviced? Just google Json vs XML.

Ok here’s some code you can play with to get a general idea of what the possibilities are when exporting to different formats. Have a look at the Json and Xml, which would you prefer? That was rhetorical… 😉

	<#
	Author: I.C.A Strachan
	Version:
	Version History:
	Purpose: Demo exporting/importing to/from csv, xml and json format
	#>
	[CmdletBinding()]
	#region Nested Object for Demo purposes
	$Win32_ComputerSystem = @(
	'DNSHostName'
	'Model'
	'Manufacturer'
	'Domain'
	'DomainRole'
	'PartOfDomain'
	'SystemType'
	)
	$Win32_OperatingSystem = @(
	'Version'
	'BuildNumber'
	'BuildType'
	'OSLanguage'
	'OSType'
	'OSArchitecture'
	'MUILanguages'
	'OperatingSystemSKU'
	'Organization'
	'ProductType'
	'ServicePackMajorVersion'
	'ServicePackMinorVersion'
	'SizeStoredInPagingFiles'
	'SystemDevice'
	'SystemDirectory'
	'WindowsDirectory'
	)
	$Win32_BIOS = @(
	'SMBIOSBIOSVersion'
	'Manufacturer'
	'Name'
	'SerialNumber'
	'Version'
	'ReleaseDate'
	)
	function Get-WMIData {
	[cmdletbinding()]
	param(
	[string]
	[ValidateNotNullOrEmpty()]
	$WMIClass,
	[string[]]
	[ValidateNotNullOrEmpty()]
	$ComputerName,
	[string[]]
	[ValidateNotNullOrEmpty()]
	$WMIProperties
	)
	$queryProperties =''
	foreach ($property in $WMIProperties){
	$queryProperties += "$property,"
	}
	#Remove last character from $queryProperties
	$queryProperties = $queryProperties -replace ".$"
	Get-wmiObject –Query "SELECT $queryProperties FROM $WMIClass" –ComputerName $ComputerName |
	Select-Object $WMIProperties
	}
	$Inventory = @{}
	$Inventory.ComputerSystem = Get-WMIData –WMIClass Win32_ComputerSystem –ComputerName LocalHost –WMIProperties $Win32_ComputerSystem
	$Inventory.OperatingSystem = Get-WMIData –WMIClass Win32_OperatingSystem –ComputerName LocalHost –WMIProperties $Win32_OperatingSystem
	$Inventory.BIOS = Get-WMIData –WMIClass Win32_BIOS –ComputerName LocalHost –WMIProperties $Win32_BIOS
	#endregion
	#region Export-Csv
	$exportSplat = @{
	NoTypeInformation = $true
	Encoding = 'UTF8'
	Delimiter = ';'
	}
	#Export each key to seperate csv File
	Foreach ($key in $Inventory.Keys) {
	if ( $Inventory.$key.Count -ne 0 ){
	$exportSplat.Path = "$PSSCriptRoot\$($key).csv"
	$Inventory.$key |
	Export-Csv @exportSplat
	}
	}
	#Import Csv for verfication
	$importCsv = @{}
	#Get csv Files
	$csvFiles = Get-ChildItem –Filter *.csv –Path $PSSCriptRoot –File
	$importSplat =@{
	Delimiter = ';'
	Encoding = 'UTF8'
	}
	Foreach ($file in $csvFiles) {
	$importCsv.$($file.BaseName) = Import-Csv –Path $file.FullName @importSplat
	}
	#endregion
	#region Export-Clixml
	$Inventory |
	Export-Clixml –Path "$PSSCriptRoot\Inventory.xml" –Encoding UTF8
	#Import Xml for verfication
	$importXml = Import-Clixml –Path "$PSSCriptRoot\Inventory.xml"
	#endregion
	#region Export to Json Format using ConvertTo-Json
	$Inventory |
	ConvertTo-Json |
	Out-File "$PSSCriptRoot\Inventory.json" –Encoding utf8
	#Import Json for verification
	$importJson = Get-Content –Path "$PSSCriptRoot\Inventory.json" |
	ConvertFrom-Json
	#endregion
	#region Compare the membertypes of Csv, Xml & Json
	$importCsv.ComputerSystem | Get-Member #Everything is a string
	$importXml.ComputerSystem | Get-Member #Typecast is preserved
	$importJson.ComputerSystem | Get-Member #Typecast is preserved
	if ($psISE) {
	psedit "$PSSCriptRoot\Inventory.json"
	psedit "$PSSCriptRoot\Inventory.xml"
	}
	else {
	notepad.exe "$PSSCriptRoot\Inventory.json"
	notepad.exe "$PSSCriptRoot\Inventory.xml"
	}
	#endregion

view raw

Demo-ExportFormats.ps1

hosted with ❤ by GitHub

Bottom line

Export-Csv is best when you need to report  anything in Excel workbooks and you’re not worried about type. Everyone gets Excel.

Export-Clixml isn’t pretty but excellent when it comes to keeping the data metadata in tact. You can always import preserving the metadata and process further in PowerShell.

Use Json if you want to have a structured data set à la XML. Json is a lot friendlier than XML. I was also surprised that the cmdlet interpreted values to it’s best avail. False became Boolean as you would expect. Json is growing on me…

Hope it’s worth something to you…

Ttyl,

Urv

Advertisement