Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET
PowerShell

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.

Sponsored Content

Say Goodbye to Traditional PC Lifecycle Management

Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.

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

But everything is typed.

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.

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.

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.

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:

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.

Related Topics:

BECOME A PETRI MEMBER:

Don't have a login but want to join the conversation? Sign up for a Petri Account

Register
Comments (0)

Leave a Reply

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: