xml-text-hero-img

Over the last several articles, I’ve been documenting my journey of transitioning from CSV files to XML. I encourage you to get caught up on past articles, otherwise today’s content won’t make much sense. My ultimate goal with this article series is to create a tool to convert a CSV file into an XML file. Additionally, this tool must be complete with type information, so that I can import the XML back into PowerShell as typed objects. Let’s walk through the final steps.

Here’s the CSV test file I’ve been working with.

I think you can figure out what type is necessary for each property if I import the CSV file. In fact, I’m going to import it with the Import-MyCSV function that I showed you earlier.

$  imp = Import-MyCSV -Path C:\scripts\testdata.csv

I will get prompted to provide a type for each property.

PowerShell is promoting us to add a type for each property. (Image Credit: Jeff Hicks)

PowerShell is promoting us to add a type for each property. (Image Credit: Jeff Hicks)

But everything is typed.

Now that I am satisfied with the data, I can convert it to an XML document.

[xml]$  xml = $  imp | ConvertTo-Xml

And for the sake of my demonstration, I’ll use another function that I showed in an earlier article to rename the Objects and Object nodes to something more meaningful.

$  xml | Rename-MyXML -NewCollection Things -NewItem Thing

Once renamed, I can save the XML document to disk.

$  xml.save("c:\work\things.xml")
Resulting XML. (Image Credit: Jeff Hicks)

Resulting XML. (Image Credit: Jeff Hicks)

If I want to complete the transformation, I can re-import the XML using my Import-MyXML function.

$  things = Import-MyXML -Path c:\work\things.xml $  things | where Test | sort size | format-table
Reimporting XML with the Import-MyXML function. (Image Credit: Jeff Hicks)

Reimporting XML with the Import-MyXML function. (Image Credit: Jeff Hicks)

Sponsored

Perfect. Of course, I don’t want to manually type the steps to convert a CSV file to XML, so I wrote a function. Because my Convert-CsvToXml function relies on the other new commands I’ve developed, I copied all of the functions into a single script file and created a PowerShell module I call CsvXmlTools.psm1. Here’s the entire module.

#requires -version 4.0  <# CsvXMLTools.psm1 functions for converting CSV files to XML  Learn more about PowerShell: http://jdhitsolutions.com/blog/essential-powershell-resources/    ****************************************************************   * DO NOT USE IN A PRODUCTION ENVIRONMENT UNTIL YOU HAVE TESTED *   * THOROUGHLY IN A LAB ENVIRONMENT. USE AT YOUR OWN RISK.  IF   *   * YOU DO NOT UNDERSTAND WHAT THIS SCRIPT DOES OR HOW IT WORKS, *   * DO NOT USE IT OUTSIDE OF A SECURE, TEST SETTING.             *   ****************************************************************  #>  Function Convert-MyCSV {  [cmdletbinding()] Param( [Parameter(Position=0,ValueFromPipeline)] [object]$  Inputobject, [Parameter(Mandatory,HelpMessage = "Enter an ordered hashtable of property names and types")] [Alias("map")] [System.Collections.Specialized.OrderedDictionary]$  PropertyMap )  Begin {     Write-Verbose "Starting $  ($  MyInvocation.Mycommand)"       Write-Verbose "Using property map $  ($  PropertyMap | out-string)" } #begin  Process { $  PropertyMap.GetEnumerator() | ForEach -begin {  $  hash = [ordered]@{} } -process {    $  property = $  _.key    write-Verbose "$  property = $  ($  InputObject.$  property)"    #only process if there is a value    if ($  InputObject.$  property) {        Switch ($  _.Value) {        "datetime" { $  value = [convert]::ToDateTime($  InputObject.$  property) }        "decimal"  { $  value = [convert]::ToDecimal($  InputObject.$  property) }        "char"     { $  value = [convert]::ToChar($  InputObject.$  property) }        "string"   { $  value = [convert]::ToString($  InputObject.$  property) }        "int"      { $  value = [convert]::ToInt($  InputObject.$  property) }        "int32"    { $  value = [convert]::ToInt32($  InputObject.$  property) }        "int64"    { $  value = [convert]::ToInt64($  InputObject.$  property) }        "uint32"   { $  value = [convert]::ToUInt32($  InputObject.$  property) }        "uint64"   { $  value = [convert]::ToUInt64($  InputObject.$  property) }        "boolean"  { $  value = [convert]::ToBoolean($  InputObject.$  property) }        "double"   { $  value = [convert]::ToDouble($  InputObject.$  property) }        Default    { $  value = $  InputObject.$  property }        } #switch    } #if there is a property value    else {     #set the value to null     $  Value = $  Null    }    #add to the hashtable    $  hash.Add($  property,$  value)  } -end {    #write a custom object to the pipeline   New-Object -TypeName PSObject -Property $  hash  }   } #process  End {     Write-Verbose "Ending $  ($  MyInvocation.Mycommand)" } #end } #end Convert-MyCSV function  Function Import-MyCSV {  [cmdletbinding()] Param( [Parameter(Position=0,Mandatory,HelpMessage="Enter the path to your CSV file")] [ValidateScript({ if (Test-Path $  _) {    $  True } else {    Throw "Cannot validate path $  _" } })]      [string]$  Path, [ValidateNotNullorEmpty()] [string]$  Delimiter="," )   Write-Verbose "Starting $  ($  MyInvocation.Mycommand)"   Write-Verbose "Creating a property map" $  PropertyMap = New-CSVMap -Path $  Path $  imported = Import-Csv -Path $  path -Delimiter $  delimiter  foreach ($  csv in $  imported) { $  PropertyMap.GetEnumerator() | ForEach -begin {  $  hash = [ordered]@{} } -process {    $  property = $  _.key    write-Verbose "$  property = $  ($  CSV.$  property)"    #only process if there is a value    if ($  CSV.$  property) {        Switch -Regex ($  _.Value) {        "datetime" { $  value = [convert]::ToDateTime($  CSV.$  property) }        "decimal"  { $  value = [convert]::ToDecimal($  CSV.$  property) }        "char"     { $  value = [convert]::ToChar($  CSV.$  property) }        "string"   { $  value = [convert]::ToString($  CSV.$  property) }        "int"      { $  value = [convert]::ToInt32($  CSV.$  property) }        "int16"    { $  value = [convert]::ToInt16($  CSV.$  property) }        "int32"    { $  value = [convert]::ToInt32($  CSV.$  property) }        "int64"    { $  value = [convert]::ToInt64($  CSV.$  property) }        "uint32"   { $  value = [convert]::ToUInt32($  CSV.$  property) }        "uint64"   { $  value = [convert]::ToUInt64($  CSV.$  property) }        "boolean"  { $  value = [convert]::ToBoolean($  CSV.$  property) }        "double"   { $  value = [convert]::ToDouble($  CSV.$  property) }        Default    { $  value = $  CSV.$  property }        } #switch    } #if there is a property value    else {     #set the value to null     $  Value = $  Null    }    #add to the hashtable    $  hash.Add($  property,$  value)  } -end {    #write a custom object to the pipeline   New-Object -TypeName PSObject -Property $  hash  }  } #foreach csv item Write-Verbose "Ending $  ($  MyInvocation.Mycommand)"  } #end Import-MyCSV function  Function New-CSVMap { [cmdletbinding()] Param( [Parameter(Position=0,Mandatory,HelpMessage="Enter the path to the CSV file")] [ValidateScript({ if (Test-Path $  _) {    $  True } else {    Throw "Cannot validate path $  _" } })]       [string]$  Path, [ValidateNotNullorEmpty()] [string]$  Delimiter = ",", [ValidateSet("ASCII","Unicode","UTF7","UTF8","UTF32","BigEndianUnicode","Default","OEM")] [string]$  Encoding = "ASCII" )  $  headings = (Get-Content -path $  path -head 1) -split $  Delimiter  #temporarily import data $  temp = Import-CSV -Path $  path -Delimiter $  Delimiter -Encoding $  encoding  $  headings | foreach -Begin {   #initialize an ordered hashtable  $  map=[ordered]@{}  } -process {  #strip off any quotes  $  property = $  _.replace('"',"")  #get sample value from the first imported object that has a value  $  sample = $  temp.where({$  _.$  property})| Select -first 1 -ExpandProperty $  Property  #prompt for object type  $  type = Read-Host "What type is $  property [ $  sample ]"  #add to the hashtable  $  map.Add($  property,$  type) } -end {     #Write the result to the pipeline     $  map }  } #end New-CSVMap function  Function Add-XMLType {  [cmdletbinding()] Param( [Parameter(Position=1,Mandatory,HelpMessage="Specify an XML document object",ValueFromPipeline)] [XML]$  Document, [Parameter(HelpMessage="What it the node type? It is case sensitive")] [ValidateNotNullorEmpty()] [Alias("Type")] [string]$  NodeType = "Object",  [Parameter(Mandatory,HelpMessage="Specify a mapping ordered hash table")] [ValidateNotNullorEmpty()] [System.Collections.Specialized.OrderedDictionary]$  Map )  Begin {     Write-Verbose "Starting $  ($  MyInvocation.Mycommand)"   } #begin  Process {  $  nodes = $  Document.SelectNodes("//$  NodeType") foreach ($  node in $  nodes.childnodes) {  Write-Verbose "Processing $  ($  node.name) [$  ($  node.GetAttribute('Type'))]"  if ($  Map.Contains($  node.name)) {    $  newType = $  Map.item($  node.name)     Write-Verbose "Setting new type: $  newType"     $  node.SetAttribute("Type",$  newType)  } }  } #process  End {     Write-Verbose "Ending $  ($  MyInvocation.Mycommand)" } #end } #end Add-XMLType function  Function Import-MyXML { [cmdletbinding()] Param( [Parameter(Position=0,Mandatory,HelpMessage="Enter the path to an XML file")] [ValidateScript({ if (Test-Path $  _) {    $  True } else {    Throw "Cannot validate path $  _" } })]      [String]$  Path ) Begin {     Write-Verbose "Starting $  ($  MyInvocation.Mycommand)"   } #begin  Process {  [xml]$  xml = Get-Content -Path $  Path  $  name = $  xml.LastChild.FirstChild.Name Write-Verbose "Processing nodes: $  Name" $  nodes = $  xml.SelectNodes("//$  Name") foreach ($  node in $  nodes) { $  node.GetEnumerator() | foreach -begin {  $  objHash = [ordered]@{}  } -process {     $  text = $  _.InnerText     $  Property = $  _.Name     Try {     Switch ($  _.type) {         "System.Boolean"  { $  value = [convert]::ToBoolean($  Text)}         "System.Byte"     { $  value = [convert]::ToByte($  Text)}            "System.Char"     { $  value = [convert]::ToChar($  Text)}                    "System.DateTime" { $  value = [convert]::ToDateTime($  Text)}                "System.Decimal"  { $  value = [convert]::ToDecimal($  Text)}                 "System.Double"   { $  value = [convert]::ToDouble($  Text)}                  "System.Int16"    { $  value = [convert]::ToInt16($  Text)}                   "System.Int32"    { $  value = [convert]::ToInt32($  Text)}          "System.Int64"    { $  value = [convert]::ToInt64($  Text)}         "System.SByte"    { $  value = [convert]::ToSByte($  Text)}         "System.Single"   { $  value = [convert]::ToSingle($  Text)}         "System.String"   { $  value = [convert]::ToString($  Text)}         "System.UInt16"   { $  value = [convert]::ToUInt16($  Text)}         "System.Uint32"   { $  value = [convert]::ToUInt32($  Text)}         "System.uInt64"   { $  value = [convert]::ToUInt64($  Text)}         Default           {                              Write-Warning "No type detected for $  Property. It may be null."                             $  value = $  Text                            }      } #switch     }     Catch {         Write-Warning "No type detected for $  Property. It may be null."        $  value = $  Text     }     Finally {         $  objHash.Add($  Property,$  Value)     }  } -end {   New-Object -TypeName PSObject -Property $  objHash  } } #foreach node  } #process  End {     Write-Verbose "Ending $  ($  MyInvocation.Mycommand)" } #end } #end Import-MyXML function  Function Rename-MyXML {  [cmdletbinding()] Param( [Parameter(Position=0,Mandatory,HelpMessage="Enter an XML document",ValueFromPipeline)] [ValidateNotNullorEmpty()] [xml]$  XML, [Parameter(Mandatory,HelpMessage="What do you want to call the top level instead of Objects")] [ValidateNotNullorEmpty()] [string]$  NewCollection, [Parameter(Mandatory,HelpMessage="What do you want to call each object")] [ValidateNotNullorEmpty()] [string]$  NewItem, [ValidateNotNullorEmpty()] [string]$  OldCollection = "Objects", [ValidateNotNullorEmpty()] [string]$  OldItem = "Object"  )  Begin {     Write-Verbose "Starting $  ($  MyInvocation.Mycommand)"   } #begin  Process { #rename child nodes $  Nodes = $  xml.SelectNodes("//$  OldCollection") Write-Verbose "Renaming $  OldItem to $  NewItem" foreach ($  Node in $  Nodes.ChildNodes) {     #create new (renamed) node     $  newNode = $  xml.CreateElement($  NewItem)      #copy existing Object children     $  newNode.InnerXml = $  Node.InnerXml      #copy Type attribute - case sensitive     $  newNode.SetAttribute("Type",$  node.GetAttribute("Type")) | Out-Null      #replace existing Object node with newly renamed node     $  Node.ParentNode.InsertBefore($  newNode,$  Node) | Out-Null     $  Node.ParentNode.RemoveChild($  Node) | Out-Null }   #rename top level write-Verbose "Renaming $  OldCollection to $  NewCollection" $  top = $  xml.SelectSingleNode($  OldCollection) #create new (renamed) node $  newNode = $  xml.CreateElement($  NewCollection) #copy existing Objects children $  newNode.InnerXml = $  top.InnerXml #copy Type $  newNode.SetAttribute("Type",$  top.GetAttribute("Type"))   #remove the old node $  xml.RemoveChild($  top) | Out-Null #add the new node $  xml.AppendChild($  newNode) | Out-Null  } #process  End {     #write updated xml back to pipeline     #$  xml     Write-Verbose "Ending $  ($  MyInvocation.Mycommand)" } #end } #end Rename-MyXML function  Function Convert-CSVtoXML { [cmdletbinding()]  Param( [Parameter(Position=0,Mandatory,HelpMessage="Enter the path to a CSV file")] [ValidateScript({ if (Test-Path $  _) {    $  True } else {    Throw "Cannot validate path $  _" } })]      [String]$  Path, [ValidateNotNullorEmpty()] [string]$  Delimiter=",", [ValidateNotNullorEmpty()] #take path and replace .csv with .xml [string]$  XMLPath =  (Convert-Path $  ($  PSBoundParameters["path"] -replace "\.\w*$  ",".xml")), [string]$  NewCollection, [string]$  NewItem, [switch]$  Passthru  )  Begin {     Write-Verbose "Starting $  ($  MyInvocation.Mycommand)"       Write-Verbose "Converting $  path to $  xmlpath" } #begin  Process { Write-Verbose "Importing my CSV and converting to XML" [xml]$  xml = Import-MyCSV -Path $  Path | ConvertTo-Xml  if ($  NewCollection -AND $  NewItem) {     Write-Verbose "Renaming $  OldCollection to $  NewCollection"          $  xml | Rename-MyXML -NewCollection $  NewCollection -NewItem $  NewItem } #if  Write-Verbose "Saving XML to $  XMLPath" $  xml.save($  XMLPath)  #write XML file object to pipeline if -Passthru if ($  Passthru) {     Get-Item -Path $  XMLPath }  } #process  End {     Write-Verbose "Ending $  ($  MyInvocation.Mycommand)" } #end } #end Convert-CSVtoXML function  Export-ModuleMember -Function *

You should create a folder called CsvXmlTools under C:\Users\<YOU>\Documents\WindowsPowerShell\Modules and place the .psm1 file in it. If you decide to call it something else, just remember the folder name must match the name of the .psm1 file. I did not create a manifest, but you are welcome to do so.

The Convert-CSVtoXML function brings everything together, including the option to rename nodes.

The Convert-CSVtoXML function. (Image Credit: Jeff Hicks)

The Convert-CSVtoXML function. (Image Credit: Jeff Hicks)

The NewCollection parameter reflects the top level or Objects node. The NewItem parameter is used to rename Object to something else. Because I’m running this end-to-end I know that the interim XML will be using Objects and Object.

Now, I can simply run my Convert-CsvToXml function, assuming the module is in a known location.

Convert-CSVtoXML c:\scripts\testdata.csv -NewCollection TestItems -NewItem TestItem -XMLPath c:\work\TestItems.xml

I will be prompted to provide a type name for each property.

You don’t need to enter the type as System.String. Just ‘String’ will suffice because my Import-MyCSV function is now using the –Regex option for the Switch statement.

$  property = $  _.key write-Verbose "$  property = $  ($  CSV.$  property)" #only process if there is a value if ($  CSV.$  property) {  Switch -Regex ($  _.Value) {   "datetime" { $  value = [convert]::ToDateTime($  CSV.$  property) }   "decimal" { $  value = [convert]::ToDecimal($  CSV.$  property) }   "char" { $  value = [convert]::ToChar($  CSV.$  property) }   "string" { $  value = [convert]::ToString($  CSV.$  property) }   "int" { $  value = [convert]::ToInt32($  CSV.$  property) }   "int16" { $  value = [convert]::ToInt16($  CSV.$  property) }   "int32" { $  value = [convert]::ToInt32($  CSV.$  property) }   "int64" { $  value = [convert]::ToInt64($  CSV.$  property) }   "uint32" { $  value = [convert]::ToUInt32($  CSV.$  property) }   "uint64" { $  value = [convert]::ToUInt64($  CSV.$  property) }   "boolean" { $  value = [convert]::ToBoolean($  CSV.$  property) }   "double" { $  value = [convert]::ToDouble($  CSV.$  property) }   Default { $  value = $  CSV.$  property }  } #switch } #if there is a property value else {  #set the value to null  $  Value = $  Null } #add to the hashtable $  hash.Add($  property,$  value)

That one-line command converted the original CSV file into this XML:

Sponsored

As far as I’m concerned, this is mission accomplished. I hope you found this a useful experience and learned a thing or two about using CSV or XML files in PowerShell. Comments are always welcome.

The post CSV to XML with PowerShell: Finishing Touches for a Custom Tool appeared first on Petri.


Petri IT Knowledgebase

Menu