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… 😉

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

Advertisements

One thought on “Revisiting Exporting Data

  1. Pingback: File servers configuration report | pshirwin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s