Powershell- Convert Complex XML to CSV

Question

I want to convert the below XML to CSV. Though it has header line information in the XML file.

<?xml version="1.0" encoding="UTF-8"?>
<myfile>
  <updatetime>2019-07-30 08:30:30</updatetime>
  <process code="PRS1234" name="PROCESS1234" />
  <equipment code="EQP1234" name="EQUIPMENT1234" />
  <product type="equipment" planned="300" time="36000" cycletime="20" />
  <shift code="1" timestart="2019-07-30 02:00:00">
    <order index="1" goodproduct="500" defectproduct="5" time="2019-07-30 02:00:00" />
    <order index="2" goodproduct="980" defectproduct="7" time="2019-07-30 03:00:00" />
    <order index="3" goodproduct="1200" defectproduct="12" time="2019-07-30 04:00:00" />
    <order index="4" goodproduct="1800" defectproduct="15" time="2019-07-30 05:00:00" />
    <order index="5" goodproduct="2500" defectproduct="15" time="2019-07-30 06:00:00" />
  <shift>
  <shift code="2" timestart="2019-07-30 07:00:00">
    <order index="1" goodproduct="600" defectproduct="5" time="2019-07-30 07:00:00" />
    <order index="2" goodproduct="980" defectproduct="7" time="2019-07-30 08:00:00" />
    <order index="3" goodproduct="1500" defectproduct="8" time="2019-07-30 09:00:00" />
    <order index="4" goodproduct="1700" defectproduct="11" time="2019-07-30 10:00:00" />
    <order index="5" goodproduct="3000" defectproduct="15" time="2019-07-30 11:00:00" />
  </shift>
</myfile>

I can get values for the desired nodes. This is what I have done.

[xml]$inputFile = Get-Content "Q:\XML\FileComplex.xml"
$inputFile.myfile.product | Select-Object -Property type,planned,time,cycletime | ConvertTo-Csv -NoTypeInformation -Delimiter ";" | Set-Content -Path "Q:\XML\FileComplex.csv" -Encoding UTF8 "

What I'm trying to achieve is to combine all desired information together and to get it as one record of the CSV file, which is like this

updatetime          | code(process) | name(process) | code(equipment) | name(equipment) | type(product) | planned(product) | time(product) | cycletime(product) | goodproduct(shift(code) is 1 and index is max) | defectproduct(shift(code) is 1 and index is max) | goodproduct(shift(code) is 2 and index is max) | defectproduct((shift(code) is 2 where index is max)
2019-07-30 08:30:30 | PRS1234       | PROCESS1234   | EQP1234         | EQUIPMENT1234   | equipment     | 300              | 36000         | 20                 |                               2500             |                                               15 |                                           3000 |                                       15

I really appreciate your support!!

Thanks in advance Natasha

Please post a sample of your actual XML, or fix the errors in the current sample so that it actually becomes loadable. — Tomalak
– Tomalak, Commented Aug 3, 2019 at 9:08

Tomalak · Accepted Answer · 2019-08-03 13:53:57Z

4

I would solve this using SelectSingleNode() and XPath.

$data = New-Object xml;
$data.load(".\myfile.xml")

$record = [pscustomobject]@{
    "updatetime"             = $data.SelectSingleNode("/*/updatetime")."#text"
    "code(process)"          = $data.SelectSingleNode("/*/process").code
    "name(process)"          = $data.SelectSingleNode("/*/process").name
    "code(equipment)"        = $data.SelectSingleNode("/*/equipment").code
    "name(equipment)"        = $data.SelectSingleNode("/*/equipment").name
    "type(product)"          = $data.SelectSingleNode("/*/product").type
    "planned(product)"       = $data.SelectSingleNode("/*/product").planned
    "time(product)"          = $data.SelectSingleNode("/*/product").time
    "cycletime(product)"     = $data.SelectSingleNode("/*/product").cycletime
    "goodproduct(shift 1)"   = $data.SelectSingleNode("/*/shift[@code = 1]/order[not(@index < ../order/@index)]").goodproduct
    "defectproduct(shift 1)" = $data.SelectSingleNode("/*/shift[@code = 1]/order[not(@index < ../order/@index)]").defectproduct
    "goodproduct(shift 2)"   = $data.SelectSingleNode("/*/shift[@code = 2]/order[not(@index < ../order/@index)]").goodproduct
    "defectproduct(shift 2)" = $data.SelectSingleNode("/*/shift[@code = 2]/order[not(@index < ../order/@index)]").defectproduct
}

$record
#$record | ConvertTo-Csv -NoTypeInformation

Explanation of order[not(@index < ../order/@index)]: "Any <order> whose index is not less than the index of any other <order> next to it." - The only <order> for which this condition is true is the one with the max index.

Output is like this (before conversion to CSV)

updatetime             : 2019-07-30 08:30:30
code(process)          : PRS1234
name(process)          : PROCESS1234
code(equipment)        : EQP1234
name(equipment)        : EQUIPMENT1234
type(product)          : equipment
planned(product)       : 300
time(product)          : 36000
cycletime(product)     : 20
goodproduct(shift 1)   : 2500
defectproduct(shift 1) : 15
goodproduct(shift 2)   : 3000
defectproduct(shift 2) : 15

edited Aug 3, 2019 at 13:53

answered Aug 3, 2019 at 12:54

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

2 Comments

Natasha Perera Over a year ago

one more question Can you tell me how can I save this file as--> date(updatetime)-code(equipment).csv which is like 2019-07-30-EQP1234.csv

Tomalak Over a year ago

You can see how updatetime and equipment are selected from the XML in my code. You should be able to build a filename from those two things. Use the Export-Csv cmdlet to write the CSV file.

AlwaysLearning · Accepted Answer · 2019-08-03 12:37:03Z

2

OP's XML doesn't appear to be valid. Assuming the XML is meant to be...

<?xml version="1.0" encoding="UTF-8"?>
<myfile>
    <updatetime>2019-07-30 08:30:30</updatetime>
    <process code="PRS1234" name="PROCESS1234" />
    <equipment code="EQP1234" name="EQUIPMENT1234" />
    <product type="equipment" planned="300" time="36000" cycletime="20" />
    <shift code="1" timestart="2019-07-30 02:00:00">
        <order index="1" goodproduct="500" defectproduct="5" time="2019-07-30 02:00:00" />
        <order index="2" goodproduct="980" defectproduct="7" time="2019-07-30 03:00:00" />
        <order index="3" goodproduct="1200" defectproduct="12" time="2019-07-30 04:00:00" />
        <order index="4" goodproduct="1800" defectproduct="15" time="2019-07-30 05:00:00" />
        <order index="5" goodproduct="2500" defectproduct="15" time="2019-07-30 06:00:00" />
    </shift>
    <shift code="2" timestart="2019-07-30 07:00:00">
        <order index="1" goodproduct="600" defectproduct="5" time="2019-07-30 07:00:00" />
        <order index="2" goodproduct="980" defectproduct="7" time="2019-07-30 08:00:00" />
        <order index="3" goodproduct="1500" defectproduct="8" time="2019-07-30 09:00:00" />
        <order index="4" goodproduct="1700" defectproduct="11" time="2019-07-30 10:00:00" />
        <order index="5" goodproduct="3000" defectproduct="15" time="2019-07-30 11:00:00" />
    </shift>
</myfile>

Because PowerShell's XPath 1.0 doesn't support the max() function the following code assumes that the order elements are arranged in ascending index order and just selects the last one. If you can't guarantee the index order you'll need to come up with your own max() solution...

[xml]$xml = Get-Content "FileComplex.xml"

# PowerShell uses XPath 1.0, which doesn't support max().
# If it did, you could select: //shift[code=1]/order[@id = max(//shift[code=1]/order/@id)]
$order1 = $xml.SelectSingleNode("//shift[@code=1]/order[last()]")
$order2 = $xml.SelectSingleNode("//shift[@code=2]/order[last()]")

# Using Add-Member like this is messy, but guarantees the order of fields on the $result object...
$result = New-Object PSObject -Property $props
$result | Add-Member NoteProperty "updatetime" $xml.SelectSingleNode("//updatetime").InnerText
$result | Add-Member NoteProperty "code(process)" $xml.SelectSingleNode("//process").code
$result | Add-Member NoteProperty "name(process)" $xml.SelectSingleNode("//process").name
$result | Add-Member NoteProperty "code(equipment)" $xml.SelectSingleNode("//equipment").code
$result | Add-Member NoteProperty "name(equipment)" $xml.SelectSingleNode("//equipment").name
$result | Add-Member NoteProperty "type(product)" $xml.SelectSingleNode("//product").type
$result | Add-Member NoteProperty "planned(product)" $xml.SelectSingleNode("//product").planned
$result | Add-Member NoteProperty "time(product)" $xml.SelectSingleNode("//product").time
$result | Add-Member NoteProperty "cycletime(product)" $xml.SelectSingleNode("//product").cycletime
$result | Add-Member NoteProperty "goodproduct(shift(code) is 1 and index is max)" $order1.goodproduct
$result | Add-Member NoteProperty "defectproduct(shift(code) is 1 and index is max)" $order1.defectproduct
$result | Add-Member NoteProperty "goodproduct(shift(code) is 2 and index is max)" $order2.goodproduct
$result | Add-Member NoteProperty "defectproduct(shift(code) is 2 and index is max)" $order2.defectproduct

$result | Export-Csv -Path "FileComplex.csv" -Delimiter ';' -Encoding utf8

answered Aug 3, 2019 at 12:37

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

5 Comments

Tomalak Over a year ago

I don't think this fulfills the "index is max" condition...? It can be done using XPath 1.0, though.

AlwaysLearning Over a year ago

You are right, it is not max(). And I said as much in my answer.

Tomalak Over a year ago

($xml.SelectNodes("//shift[@code=1]/order") | Sort index | Select -Last 1) would have been one of the alternatives.

AlwaysLearning Over a year ago

Yes, that would totally work. I never understood why Microsoft didn't go beyond XPath 1.0 in their XMLDocument implementation. It's really quite limiting.

Tomalak Over a year ago

Me neither. At some point they simply stopped investing into XML.

Natasha Perera · Accepted Answer · 2019-08-04 11:20:23Z

This is what I get finally

$data = New-Object xml;
$data.load("Q:\XML\Myfile.xml")
$record = [pscustomobject]@{
"updatetime"             = $data.SelectSingleNode("/*/updatetime")."#text"
"code(process)"          = $data.SelectSingleNode("/*/process").code
"name(process)"          = $data.SelectSingleNode("/*/process").name
"code(equipment)"        = $data.SelectSingleNode("/*/equipment").code
"name(equipment)"        = $data.SelectSingleNode("/*/equipment").name
"type(product)"          = $data.SelectSingleNode("/*/product").type
"planned(product)"       = $data.SelectSingleNode("/*/product").planned
"time(product)"          = $data.SelectSingleNode("/*/product").time
"cycletime(product)"     = $data.SelectSingleNode("/*/product").cycletime
"goodproduct(shift 1)"   = $data.SelectSingleNode("/*/shift[@code = 1]/order[not(@index < ../order/@index)]").goodproduct
"defectproduct(shift 1)" = $data.SelectSingleNode("/*/shift[@code = 1]/order[not(@index < ../order/@index)]").defectproduct
"goodproduct(shift 2)"   = $data.SelectSingleNode("/*/shift[@code = 2]/order[not(@index < ../order/@index)]").goodproduct
"defectproduct(shift 2)" = $data.SelectSingleNode("/*/shift[@code = 2]/order[not(@index < ../order/@index)]").defectproduct
}
$equipment = $record.'code(equipment)'
$date = Get-Date -Date $record.updatetime
$dateformat = $date.ToString("yyyy-MM-dd")
$record | ConvertTo-Csv -NoTypeInformation -Delimiter ',' | Set-Content -Path "Q:\XML\$( "$equipment" + "_" + "$dateformat").csv" -Encoding utf8

Collectives™ on Stack Overflow

Powershell- Convert Complex XML to CSV

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related