Cool powershell: From Xml to Csv in two lines

With one-level xml you can convert xml to csv in two lines of powershell:

Create file Stsadm-EnumSites.xml:

cmd > stsadm -o enumsites -url http://teams > Stsadm-EnumSites.xml

<Sites Count="2">
<Site Url="http://teams/" Owner="CONTOSO\XANDH" SecondaryOwner="CONTOSO\rinkr" ContentDatabase="WSS_Content_Teams" StorageUsedMB="0.6" StorageWarningMB="400" StorageMaxMB="500" />
<Site Url="http://teams/commercial/AALT" Owner="CONTOSO\ECPD" ContentDatabase="WSS_Content_Teams" StorageUsedMB="77.4" StorageWarningMB="400" StorageMaxMB="500"/>
</Sites>

Your converter code:

#read from file
[xml]$inputFile = Get-Content "Stsadm-EnumSites.xml"
#export xml as csv
$inputFile.Sites.ChildNodes | Export-Csv "Stsadm-EnumSites.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8

Output: Stsadm-EnumSites.csv

"Url";"Owner";"SecondaryOwner";"ContentDatabase";"StorageUsedMB";"StorageWarningMB";"StorageMaxMB"
"http://teams";"CONTOSO\XANDH";"CONTOSO\rinkr";"WSS_Content_Teams";"0.6";"400";"500"
"http://teams/commercial/AALT";"CONTOSO\ECPD";;"WSS_Content_Teams";"77.4";"400";"500"

Cool :-)

Advertisements

4 Comments

Add yours →

  1. HI Rasor, I need to convert XML file into CSV file using powershell. Is your script can be used to convert the xml file into CSV file?
    Thanks.
    Cheers,
    Badal

    • Certainly – I see WordPress at some point converted “<" to "& lt;" in the Xml in the post. I have converted it back, so you easy can see the input xml and the output csv.

  2. Thanks mate for your reply.

    Script i ran as below:
    #read from file
    [xml]$inputFile = Get-Content “PMIS.xml”
    $inputFile
    #export xml as csv
    $inputFile.Sites.ChildNodes | Export-Csv “Stsadm-EnumSites.csv” -NoTypeInformation -Delimiter:”;” -Encoding:UTF8

    I tried above script but getting below error: Any suggestion to proceed further on this error mate

    xml DocumentRoot
    — ————
    version=”1.0″ {, DocumentRoot}
    Export-Csv : Cannot bind argument to parameter ‘InputObject’ because it is null.
    At line:14 char:41
    + $inputFile.Sites.ChildNodes | Export-Csv <<<< "Stsadm-EnumSites.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8
    + CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand

    Cheers,
    Badal

    • Hi Badal.

      Yes, I have an idea – and this is one of the places, where you will love Powershell:
      Notice this:
      $inputFile.Sites.ChildNodes
      Powershell has read my xml and knows that I called my root node for Sites. Since you (probably) didn’t call the root node in your xml for Sites, then you get an error.


      [badalsxml]
      [row id="1"/]
      [row id="2"/]
      [/badalsxml]

      For above xml you would write:
      $inputFile.badalsxml.ChildNodes
      (sorry – again wordpress don’t like less-than and greater-than)

      Have fun mate :-)

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: