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!

Import-Csv $File -Delimiter ';' | Group-Object -Property UserID should aggregate all your rows by the User ID. — Bacon Bits
– Bacon Bits, Commented Oct 24, 2018 at 13:18

mklement0 · Accepted Answer · 2021-08-23 13:57:03Z

2

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).

edited Aug 23, 2021 at 13:57

answered Oct 24, 2018 at 21:31

mklement0

452k68 gold badges728 silver badges988 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alecbalec Over a year ago

Thank you very much! It indeed created files for each UserId but not all got the .xml file format and also received a blank file, another problem was that it didn't contain the right UserId within the xml code and it could only print out one certificate. But after changing { $grp.Name + '.xml' } to { $Cert.UserID+ '.xml' } it produces .xml for every UserId and contains all the certificates for the specific UserId. I really appreciate your effort! Cheers :)

mklement0 Over a year ago

My pleasure, @Alecbalec; I'm glad to hear the answer was helpful. I don't quite understand why $grp.Name + '.xml' didn't work (other than CSV rows without a user ID causing problems), but I guess we don't need to solve that mystery.

Collectives™ on Stack Overflow

Powershell CSV to XML

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related