CSV to XML with PowerShell: Finishing Touches for a Custom Tool

Posted on July 9, 2015 by Jeff Hicks in PowerShell with 0 Comments

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.

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.

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.

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

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.

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.

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.

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.

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.

Sponsored

Tagged with ,