Powershell – 将XML转换为CSV
发布时间:2020-12-16 23:29:53 所属栏目:百科 来源:网络整理
导读:我可以使用以下代码将 XML转换为CSV: #read from file[xml]$inputFile = Get-Content "c:pstesttest.xml"#export xml as csv$inputFile.Transaction.ChildNodes | Export-Csv "c:pstesttest.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8 如
我可以使用以下代码将
XML转换为CSV:
#read from file [xml]$inputFile = Get-Content "c:pstesttest.xml" #export xml as csv $inputFile.Transaction.ChildNodes | Export-Csv "c:pstesttest.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8 如果文件只包含一个具有一种子节点类型的根节点,则它可以工作,例如: <?xml version="1.0" encoding="UTF-8"?> <Transaction> <TXNDETAIL> <RecordID>02</RecordID> <SequenceNumber>1</SequenceNumber> <TransactionType>01</TransactionType> <ActionCode>01</ActionCode> <TransactionID>17500515552017001</TransactionID> <SellerCode>2200919TRY</SellerCode> <BuyerCode>KOCZER</BuyerCode> <TransactionReference> </TransactionReference> <TransactionDescription1> </TransactionDescription1> <TransactionDescription2> </TransactionDescription2> <DocumentType>01</DocumentType> <DocumentNumber>XXXXXXXXXXX</DocumentNumber> <DocumentDate>20170301</DocumentDate> <DocumentAmount>10000</DocumentAmount> <CurrencyCode>949</CurrencyCode> <TransactionAmount>10000</TransactionAmount> <TransactionDueDate>20170505</TransactionDueDate> <AdditionalInformation1> </AdditionalInformation1> <AdditionalInformation2> </AdditionalInformation2> <HashCode>XXXXXXXX</HashCode> </TXNDETAIL> <TXNDETAIL> <RecordID>02</RecordID> <SequenceNumber>2</SequenceNumber> <TransactionType>01</TransactionType> <ActionCode>01</ActionCode> <TransactionID>17500515622017001</TransactionID> <SellerCode>2200919TRY</SellerCode> <BuyerCode>KOCZER</BuyerCode> <TransactionReference> </TransactionReference> <TransactionDescription1> </TransactionDescription1> <TransactionDescription2> </TransactionDescription2> <DocumentType>01</DocumentType> <DocumentNumber>XXXXXXXXXXX</DocumentNumber> <DocumentDate>20170301</DocumentDate> <DocumentAmount>10000</DocumentAmount> <CurrencyCode>949</CurrencyCode> <TransactionAmount>10000</TransactionAmount> <TransactionDueDate>20170505</TransactionDueDate> <AdditionalInformation1> </AdditionalInformation1> <AdditionalInformation2> </AdditionalInformation2> <HashCode>XXXXXXXX</HashCode> </TXNDETAIL> <TXNDETAIL> <RecordID>02</RecordID> <SequenceNumber>3</SequenceNumber> <TransactionType>01</TransactionType> <ActionCode>01</ActionCode> <TransactionID>17500515972017001</TransactionID> <SellerCode>2200919TRY</SellerCode> <BuyerCode>KOCZER</BuyerCode> <TransactionReference> </TransactionReference> <TransactionDescription1> </TransactionDescription1> <TransactionDescription2> </TransactionDescription2> <DocumentType>01</DocumentType> <DocumentNumber>XXXXXXXXXXX</DocumentNumber> <DocumentDate>20170301</DocumentDate> <DocumentAmount>10000</DocumentAmount> <CurrencyCode>949</CurrencyCode> <TransactionAmount>10000</TransactionAmount> <TransactionDueDate>20170505</TransactionDueDate> <AdditionalInformation1> </AdditionalInformation1> <AdditionalInformation2> </AdditionalInformation2> <HashCode>XXXXXXXX</HashCode> </TXNDETAIL> </Transaction> 输出如下: "RecordID";"SequenceNumber";"TransactionType";"ActionCode";"TransactionID";"SellerCode";"BuyerCode";"TransactionReference";"TransactionDescription1";"TransactionDescription2";"DocumentType";"DocumentNumber";"DocumentDate";"DocumentAmount";"CurrencyCode";"TransactionAmount";"TransactionDueDate";"AdditionalInformation1";"AdditionalInformation2";"HashCode" "02";"1";"01";"01";"17500515552017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX" "02";"2";"01";"01";"17500515622017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX" "02";"3";"01";"01";"17500515972017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX" 哪个好. 但是,输入文件实际上有一个“标题行”信息,即TXNHEAD标记 <?xml version="1.0" encoding="UTF-8"?> <Transaction> <TXNHEAD> <RecordID>01</RecordID> <FileName>001</FileName> <IntermediaryCode>19000033</IntermediaryCode> <ActualizationDate>20170314</ActualizationDate> <SequenceNumber>001</SequenceNumber> <NumberofRecords>3</NumberofRecords> <AmountofRecords>30000</AmountofRecords> </TXNHEAD> <TXNDETAIL> <RecordID>02</RecordID> <SequenceNumber>1</SequenceNumber> <TransactionType>01</TransactionType> <ActionCode>01</ActionCode> <TransactionID>17500515552017001</TransactionID> <SellerCode>2200919TRY</SellerCode> <BuyerCode>KOCZER</BuyerCode> <TransactionReference> </TransactionReference> <TransactionDescription1> </TransactionDescription1> <TransactionDescription2> </TransactionDescription2> <DocumentType>01</DocumentType> <DocumentNumber>XXXXXXXXXXX</DocumentNumber> <DocumentDate>20170301</DocumentDate> <DocumentAmount>10000</DocumentAmount> <CurrencyCode>949</CurrencyCode> <TransactionAmount>10000</TransactionAmount> <TransactionDueDate>20170505</TransactionDueDate> <AdditionalInformation1> </AdditionalInformation1> <AdditionalInformation2> </AdditionalInformation2> <HashCode>XXXXXXXX</HashCode> </TXNDETAIL> <TXNDETAIL> <RecordID>02</RecordID> <SequenceNumber>2</SequenceNumber> <TransactionType>01</TransactionType> <ActionCode>01</ActionCode> <TransactionID>17500515622017001</TransactionID> <SellerCode>2200919TRY</SellerCode> <BuyerCode>KOCZER</BuyerCode> <TransactionReference> </TransactionReference> <TransactionDescription1> </TransactionDescription1> <TransactionDescription2> </TransactionDescription2> <DocumentType>01</DocumentType> <DocumentNumber>XXXXXXXXXXX</DocumentNumber> <DocumentDate>20170301</DocumentDate> <DocumentAmount>10000</DocumentAmount> <CurrencyCode>949</CurrencyCode> <TransactionAmount>10000</TransactionAmount> <TransactionDueDate>20170505</TransactionDueDate> <AdditionalInformation1> </AdditionalInformation1> <AdditionalInformation2> </AdditionalInformation2> <HashCode>XXXXXXXX</HashCode> </TXNDETAIL> <TXNDETAIL> <RecordID>02</RecordID> <SequenceNumber>3</SequenceNumber> <TransactionType>01</TransactionType> <ActionCode>01</ActionCode> <TransactionID>17500515972017001</TransactionID> <SellerCode>2200919TRY</SellerCode> <BuyerCode>KOCZER</BuyerCode> <TransactionReference> </TransactionReference> <TransactionDescription1> </TransactionDescription1> <TransactionDescription2> </TransactionDescription2> <DocumentType>01</DocumentType> <DocumentNumber>XXXXXXXXXXX</DocumentNumber> <DocumentDate>20170301</DocumentDate> <DocumentAmount>10000</DocumentAmount> <CurrencyCode>949</CurrencyCode> <TransactionAmount>10000</TransactionAmount> <TransactionDueDate>20170505</TransactionDueDate> <AdditionalInformation1> </AdditionalInformation1> <AdditionalInformation2> </AdditionalInformation2> <HashCode>XXXXXXXX</HashCode> </TXNDETAIL> </Transaction> 当应用相同的代码时,我得到: "RecordID";"FileName";"IntermediaryCode";"ActualizationDate";"SequenceNumber";"NumberofRecords";"AmountofRecords" "01";"001";"19000033";"20170314";"001";"3";"30000" "02";;;;"1";; "02";;;;"2";; "02";;;;"3";; 当我尝试这个代码而不是只检索头部时: #read from file [xml]$inputFile = Get-Content "c:pstesttest.xml" #export xml as csv $inputFile.Transaction.TXNHEAD.ChildNodes | Export-Csv "c:pstesttest.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8 我明白了: "#text" "01" "001" "19000033" "20170314" "001" "3" "30000" 我想要实现的是这个输出: "RecordID";"FileName";"IntermediaryCode";"ActualizationDate";"SequenceNumber";"NumberofRecords";"AmountofRecords" "01";"001";"19000033";"20170314";"001";"3";"30000" "RecordID";"SequenceNumber";"TransactionType";"ActionCode";"TransactionID";"SellerCode";"BuyerCode";"TransactionReference";"TransactionDescription1";"TransactionDescription2";"DocumentType";"DocumentNumber";"DocumentDate";"DocumentAmount";"CurrencyCode";"TransactionAmount";"TransactionDueDate";"AdditionalInformation1";"AdditionalInformation2";"HashCode" "02";"1";"01";"01";"17500515552017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX" "02";"2";"01";"01";"17500515622017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX" "02";"3";"01";"01";"17500515972017001";"2200919TRY";"KOCZER";"";"";"";"01";"XXXXXXXXXXX";"20170301";"10000";"949";"10000";"20170505";"";"";"XXXXXXXX" 我究竟做错了什么? 解决方法
管道中的第一个对象(或Select-Object等)定义输出的标头,无论它是文件还是控制台输出.
您可以做的是在两轮中将它们转换为csv并将其添加到同一文件中.例如: $inputFile.Transaction.TXNHEAD | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Set-Content -Path "c:pstesttest.csv" -Encoding UTF8 $inputFile.Transaction.TXNDETAIL | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Add-Content -Path "c:pstesttest.csv" -Encoding UTF8 你也可以像这样组合它们: $inputFile.Transaction.TXNHEAD,$x.Transaction.TXNDETAIL | ForEach-Object { $_ | ConvertTo-Csv -NoTypeInformation -Delimiter ";" } | Set-Content -Path "c:pstesttest.csv" -Encoding UTF8 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |