All geek questions in one place

Powershell - Convert XML to CSV

I was able to convert XML to CSV using the following code:

#read from file [xml]$inputFile = Get-Content "c:\pstest\test.xml" #export xml as csv $inputFile.Transaction.ChildNodes | Export-Csv "c:\pstest\test.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8

It works if the files contain only one root node with one type of child nodes, for example:

 <?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>

The output will look like this:

  "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"

It's great.

However, the input file actually has "header" information, the TXNHEAD tag

  <?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>

When applying the same code, I get:

  "RecordID";"FileName";"IntermediaryCode";"ActualizationDate";"SequenceNumber";"NumberofRecords";"AmountofRecords" "01";"001";"19000033";"20170314";"001";"3";"30000" "02";;;;"1";; "02";;;;"2";; "02";;;;"3";;

When I try to use this code instead of extracting only the head:

 #read from file [xml]$inputFile = Get-Content "c:\pstest\test.xml" #export xml as csv $inputFile.Transaction.TXNHEAD.ChildNodes | Export-Csv "c:\pstest\test.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8

I get:

 "#text" "01" "001" "19000033" "20170314" "001" "3" "30000"

What I'm trying to achieve is the output:

 "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"

What am I doing wrong?

+5
source share
1 answer

The first object (or Select-Object , etc.) in the pipeline defines the title for the output, regardless of whether it is displayed on a file or console.

What you can do is convert them to csv in two rounds and add to one file. Example:

 $inputFile.Transaction.TXNHEAD | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Set-Content -Path "c:\pstest\test.csv" -Encoding UTF8 $inputFile.Transaction.TXNDETAIL | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Add-Content -Path "c:\pstest\test.csv" -Encoding UTF8

You can also combine them as follows:

 $inputFile.Transaction.TXNHEAD, $x.Transaction.TXNDETAIL | ForEach-Object { $_ | ConvertTo-Csv -NoTypeInformation -Delimiter ";" } | Set-Content -Path "c:\pstest\test.csv" -Encoding UTF8
+1
source

Source: https://habr.com/ru/post/1266692/

More articles:


All Articles