gpt4 book ai didi

xml - Powershell 将 CSV 导出为 XML

转载 作者:行者123 更新时间:2023-12-04 08:07:40 25 4
gpt4 key购买 nike

我需要将一个 csv 文件转换为包含 7 列的 XML:

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
XML 结构应如下所示:
<?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 脚本:
$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"
实际上 XML 输出文件:
<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>
我基于以下主题: Powershell CSV to XML
但不幸的是,我无法针对不同的列进行调整。
目标是拥有不同服务器的多个 SQL 实例的不同 SQL 备份(日志、差异和完整)的状态......
预先感谢您的帮助!

最佳答案

您可以执行以下操作以从 CSV 构建 XML 字符串:

# 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")
请注意,上述方法非常静态,在很大程度上取决于 CSV 文件的架构。这可以变成一个更动态的函数。

关于xml - Powershell 将 CSV 导出为 XML,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/66142558/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com