Moving from CSV to XML Part 4

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 from the beginning was to come up with a tool to convert or transform a CSV file into an XML file, complete with type information so that I can import the XML back into PowerShell as typed objects. Let’s walk through the final steps.

First, here’s the CSV test file I’ve been working with.

I think you can look at that and 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 I showed you earlier.

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

070315 2121 MovingfromC2

But everything is typed.

070315 2121 MovingfromC3

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

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

Well, perhaps not meaningful but at least different. Once renamed I can save the XML document to disk.

070315 2121 MovingfromC4

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

Perfect. Of course, I don’t’ want to have to manually type the few 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.

MODULE

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.

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

070315 2121 MovingfromC6

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).

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

070315 2121 MovingfromC7

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:

070315 2121 MovingfromC8

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