Powershell CSV to XML

Question

So i'm attempting to convert a csv file to XML that contains 5 columns:

UserID;Cert;LastCertDate;ExpireDate;CertNumber
100001;oka;09.09.2018;09.09.2019;100001
100001;pik;10.10.2018;10.10.2019;200001

The XML structure should be as the following:

<Cv>
    <Owner>
        <UserID></UserID>
    </Owner>
    <Content>
        <Certificates>
            <Certificate>
                <Cert>oka</Cert>
                <LastCertDate>09.09.2018</LastCertDate>
                <ExpireDate>09.09.2019</ExpireDate>
                <CertNumber>100001</CertNumber>
            </Certificate>
            <Certificate>
                <Cert>pik</Cert>
                <LastCertDate>10.10.2018</LastCertDate>
                <ExpireDate>10.10.2019</ExpireDate>
                <CertNumber>200001</CertNumber>
            </Certificate>
        </Certificates>
    </Content>
</Cv>

Per now i only manage to get the 2nd Certificate into the XML since its on its on row in the CSV file.

Any idea on how i could merge the UserID and create a list that contains all the certificates on that specific user?

Thanks!

Answer 1

Bacon Bits gave the crucial pointer in a comment: Use Group-Object to group the custom objects created from your CSV rows via Import-Csv by user ID.

Here's a solution that uses string templating to provide the desired XML text output for each user, and writes that output to a file named fro the user ID:

# Per-user template.
# Note how a *literal* (single-quoted) here-string is used, so as to 
# *defer* expansion (interpolation) of the embedded expressions.
$docTemplate = @'
<Cv>
  <Owner>
      <UserID>$($cert.UserID)</UserID>
  </Owner>
  <Content>
      <Certificates>
$($certs -join "`n")
      </Certificates>
  </Content>
</Cv>
'@

# Per-certificate template.
$entryTemplate = @'
          <Certificate>
            <Cert>$($cert.Cert)</Cert>
            <LastCertDate>$($cert.LastCertDate)</LastCertDate>
            <ExpireDate>$($cert.ExpireDate)</ExpireDate>
            <CertNumber>$($cert.CertNumber)</CertNumber>
          </Certificate>
'@

Import-Csv file.csv -Delimiter ';' | Group-Object UserId -ov grp | ForEach-Object {
  # $_.Group contains all certificates associated with the user at hand.
  # Create an XML element for each certificate and collect the results
  # in array.
  $certs = foreach ($cert in $_.Group) {
    # Instantiate the per-certificate template.
    $ExecutionContext.InvokeCommand.ExpandString($entryTemplate)  
  }
  # Instantiate the per-user template, which encompasses
  # the per-certificate elements, and output the result.
  $ExecutionContext.InvokeCommand.ExpandString($docTemplate)
} | # Write the resulting XML document string to a file named for the user ID
  Set-Content -LiteralPath { $grp.Name + '.xml' }

Note how -ov grp (short for: -OutVariable grp) captures Group-Object's output in each pipeline iteration in variable $grp, so that the Set-Content call later in the pipeline can access $grp.Name - the user ID at hand - in the script block passed to Set-Content so as to create an output file named for that user ID (a technique known as delay-bind script blocks).

Note: Without an -Encoding argument, Windows PowerShell will use your system's "ANSI" character encoding (in PowerShell Core, you'd get BOM-less UTF-8).