Powershell CSV到XML [英] Powershell CSV to XML

问题描述

因此，我正在尝试将csv文件转换为包含5列的XML:

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

XML结构应如下所示:

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>

现在，我只能设法将第二证书添加到XML中，因为它位于CSV文件的上一行.

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

关于我如何合并UserID并创建一个包含该特定用户上所有证书的列表的想法吗?

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

谢谢！

推荐答案

培根钻头在注释:使用 Group-Object 通过用户ID通过 Import-Csv 对从CSV行创建的自定义对象进行分组.

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.

这是一个使用字符串模板为每个用户提供所需XML文本输出的解决方案，并将该输出写入用户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' }

请注意 -ov grp (缩写为 -OutVariable grp )如何在变量的每个管道迭代中捕获 Group-Object 的输出 $ grp ，以便稍后在管道中调用 Set-Content 可以访问 $ grp.Name -当前的用户ID-脚本块传递给 Set-Content ，以创建一个以用户ID命名的输出文件.

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 the user ID.

注意:如果没有 -Encoding 参数，Windows PowerShell将使用系统的"ANSI"字符编码(在PowerShell Core 中，您将获得无BOM的UTF-8).

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

这篇关于Powershell CSV到XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！