Powershell Export CSV to XML

Question

I need to convert a csv file to XML that contains 7 columns:

network;server;instance;type;date;time;state
toto;titi;APPLINT1;LOG;10/02/2021;13:00:10 - During in min : 1;Succeeded
toto;titi;APPLINT1;VIDEO;12/02/2021;13:20:10 - During in min : 1;Succeeded
toto;tutu;SPTPROD1;DIFF;10/02/2021;14:30:10 - During in min : 1;Succeeded
toto;tutu;TOOL;DIFF;12/02/2021;14:00:10 - During in min : 1;Succeeded

The XML structure should be as the following:

<?xml version="1.0" encoding="utf-8"?>
<xml>
        <network name='toto'>
                <server name='titi'>
                        <instance name='APPLINT1'>
                            <type name='LOG'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                            <type name='DIFF'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                            <type name='FULL'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                        </instance>
                        <instance name='VIDEO'>
                            <type name='LOG'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                        </instance>
                </server>
                <server name='tutu'>
                        <instance name='SPTPROD1'>
                            <type name='LOG'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                            <type name='DIFF'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                            <type name='FULL'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                        </instance>
                        <instance name='VIDEO'>
                            <type name='LOG'>
                                <date name="10/02/2021">
                                    <time name="13:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                    <time name="14:00:10 - During in min : 1">
                                        <state name="Succeeded"></state>
                                    </time>
                                </date>
                            </type>
                        </instance>
                </server>
</xml>

Powershell script :

$docTemplate = @'
<xml>
    <network name=$($backup.network)>
        <server name=$($backup.server)>
            <instance name=$($backup.instance)>
                <type name=$($backup.type)>
$($backups -join "`n")
                </type>
            </instance>
        </server>
    </network>
</xml>
'@

# Per-backup template.
$entryTemplate = @'
                    <date name=$($backup.date)>
                        <time name=$($backup.time)>
                            <state name=$($backup.state)></state>
                        </time>
                    </date>
'@

Import-Csv backup_sql.csv -Delimiter ';' | Group-Object instance -ov grp | ForEach-Object {
    $backups = foreach ($backup in $_.Group) {
        $ExecutionContext.InvokeCommand.ExpandString($entryTemplate)  
    }
    $ExecutionContext.InvokeCommand.ExpandString($docTemplate)
} | Out-File "backup_sql.xml"

Actually XML output file :

<xml>
    <network name=toto>
        <server name=titi>
            <instance name=APPLINT1>
                <type name=LOG>
                    <date name=10/02/2021>
                        <time name=13:00:10 - During in min : 1>
                            <state name=Succeeded></state>
                        </time>
                    </date>
                    <date name=10/02/2021>
                        <time name=13:00:10 - During in min : 1>
                            <state name=Succeeded></state>
                        </time>
                    </date>
                </type>
            </instance>
        </server>
    </network>
</xml>
<xml>
    <network name=toto>
        <server name=titi>
            <instance name=SPTPROD1>
                <type name=DIFF>
                    <date name=10/02/2021>
                        <time name=14:30:10 - During in min : 1>
                            <state name=Succeeded></state>
                        </time>
                    </date>
                </type>
            </instance>
        </server>
    </network>
</xml>
<xml>
    <network name=toto>
        <server name=tutu>
            <instance name=TOOL>
                <type name=DIFF>
                    <date name=10/02/2021>
                        <time name=14:00:10 - During in min : 1>
                            <state name=Succeeded></state>
                        </time>
                    </date>
                </type>
            </instance>
        </server>
    </network>
</xml>
<xml>
    <network name=toto>
        <server name=tutu>
            <instance name=SKYPE>
                <type name=LOG>
                    <date name=10/02/2021>
                        <time name=23:00:10 - During in min : 1>
                            <state name=Failed></state>
                        </time>
                    </date>
                </type>
            </instance>
        </server>
    </network>
</xml>

I have based myself on the subject below : Powershell CSV to XML

But unfortunately I cannot adapt it for the different columns.

The goal is to have the state of the different SQL backups (logs, diff and full) of the multiple SQL instances of the different servers ...

thank you in advance for your help!

Answer 1

You may do the following to build your XML strings from the CSV:

# Create XML document object
$xml = [xml]::New()

# Create root xml node    
$xml.AppendChild($xml.CreateNode([System.Xml.XmlNodeType]::Element,'xml',$null))

# Deserialize csv contents
$csv = Import-Csv backup_sql.csv -Delimiter ';'

# Group based on Network value
$csv | Group Network | Foreach-Object {
    # Create a Network node for each Network value
    $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'Network',$null)
    # Create a name attribute with the Network value
    $node.SetAttribute('name',$_.Name)
    # Add Network node to the xml node
    $currentNode = $xml.SelectSingleNode("//xml").AppendChild($node)

    # Group Current Network group by Server value
    $_.Group | Group Server | Foreach-Object {
        # Create a Server node for each Server Value
        $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'Server',$null)
        $node.SetAttribute('name',$_.Name)
        $ServerNode = $currentNode.AppendChild($node)

        # Group current Server group by Instance value
        $_.Group | Group Instance | Foreach-Object {
        # Create Instance node for each Instance value
            $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'Instance',$null)
            $node.SetAttribute('name',$_.Name)
            $InstanceNode = $ServerNode.AppendChild($node)

            # Group current Instance group by Type value
            $_.Group | Group Type | Foreach-Object {
                # Create Type node for each Type value
                $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'Type',$null)
                $node.SetAttribute('name',$_.Name)
                $TypeNode = $InstanceNode.AppendChild($node)

                # Group each Type group by Date value
                $_.Group | Group Date | Foreach-Object {
                    # Create Date node for each Date value
                    $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'Date',$null)
                    $node.SetAttribute('name',$_.Name)
                    $DateNode = $TypeNode.AppendChild($node)

                    $_.Group | Foreach-Object {
                        # Create Time node for each Time value
                        $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'Time',$null)
                        $node.SetAttribute('name',$_.Time)
                        $TimeNode = $DateNode.AppendChild($node)

                        # Create State node for each State value
                        $node = $xml.CreateNode([System.Xml.XmlNodeType]::Element,'State',$null)
                        $node.SetAttribute('name',$_.State)
                        $StateNode = $TimeNode.AppendChild($node)
                    }
                }
            }
        }
    }
}
$xml.Save("$pwd\backup_sql.xml")

Note that the approach above is very static, depending heavily on the schema of the CSV file. This could be made into a more dynamic function.