CSV to XML in Powershell

Question

I have a problem with writing a CSV file to XML.

CSV File

[Folder];;;;;
[Folder\Subfolder1];;;;;
[Folder\Subfolder1\Subfolder2];;;;;
"Descriptive stuff";"its a password";"user or something";"a URL";"if anyone has a comment"
[Folder\Subfolder1\Subfolder3];;;;;
"Descriptive stuff";"its a password";"user or something";"a URL";"if anyone has a comment"

XML Output

    <?xml version="1.0" encoding="utf-8"?>
<PASSWORDFILE xmlns="http://www.password-depot.de/schemas/passwordfile/6.0/passwordfile.xsd">
    <PASSWORDS>
        <GROUP NAME="Folder">
            <GROUP NAME="Subfolder1">
                <GROUP NAME="Subfolder2">
                    <ITEM>
                        <DESCRIPTION>Descriptive stuff</DESCRIPTION>
                        <PASSWORD>its a password</PASSWORD>
                        <USERNAME>user or something</USERNAME>
                        <URL>a URL</URL>
                        <COMMENT>if anyone has a comment</COMMENT>
                    </ITEM>
                </GROUP>
                <GROUP NAME="Subfolder3">
                    <ITEM>
                        <DESCRIPTION>Descriptive stuff</DESCRIPTION>
                        <PASSWORD>its a password</PASSWORD>
                        <USERNAME>user or something</USERNAME>
                        <URL>a URL</URL>
                        <COMMENT>if anyone has a comment</COMMENT>
                    </ITEM>
                </GROUP>
            </GROUP>
        </GROUP>
    </PASSWORDS>
</PASSWORDFILE>

My problem is solely, that I don't know how to close the GROUP Tag after I have used it. At the moment I just close the GROUP Tag at the very end of the XML making a huge massive tree.

Answer 1

First of all your text is not CSV however if you are fixed with this format you sure can convert to XML by writing custom DLL. You just need to understand the CSV file details and then use XElement/XAttribute to create an XML.

I just use the following code to convert into a DLL and then save as XML(Be noted that the XML creation code is bind the the CSV file which is provided in the link below:

http://msdn.microsoft.com/en-us/library/bb387090.aspx

Here is my custom DLL Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml.Linq;

namespace CSVtoXML
{
public class Actions
 {
    public Actions()
    {

    }

    public static void convertCSVtoXML(string csvPath)
    {

        string[] source = File.ReadAllLines(csvPath);
        XElement cust = new XElement("Root",
            from str in source
            let fields = str.Split(',')
            select new XElement("Customer",
                new XAttribute("CustomerID", fields[0]),
                new XElement("CompanyName", fields[1]),
                new XElement("ContactName", fields[2]),
                new XElement("ContactTitle", fields[3]),
                new XElement("Phone", fields[4]),
                new XElement("FullAddress",
                    new XElement("Address", fields[5]),
                    new XElement("City", fields[6]),
                    new XElement("Region", fields[7]),
                    new XElement("PostalCode", fields[8]),
                    new XElement("Country", fields[9])
                )
            )
        ); 
        cust.Save("result.xml");
    }
 }
}

Here is how I used this DLL in powershell:

• PS> [Reflection.Assembly]::LoadFile(“CSVtoXML.dll”)
• PS> [CSVtoXML.Actions]::convertCSVtoXML("data.csv")

This will generate the "Result.xml"