Convert xml to csv file in Powershell

Question

I started learning Powershell only few days ago so this is all new for me. The problem is I need this done asap. So I have 2 files that need to be integrated - a .csv and a .xml. My thought was to convert one of them so I'd have 2 files in the same format first. I read that .csv files are easier to work with so decided to convert the .xml file to .csv. However, everything that I've tried failed. But I also couldn't find an example with an .xml file similar to mine. My file looks like this (some ids are empty):

<Objects>
<Object>
<Property Name="id"/>
<Property Name="username">JLOCK0</Property>
<Property Name="phoneType">phone1</Property>
<Property Name="value">346-209-9609</Property>
</Object>
<Object>
<Property Name="id">vjWJem2qcU+cxPT2qIvqsw==</Property>
<Property Name="username">CSELWYN1</Property>
<Property Name="phoneType">phone1</Property>
<Property Name="value">562-981-5379</Property>
</Object>
</Objects>

All the code that I've tried either show only id, username, phoneType and value without the values or just the values. Moving with nodes up or down doesn't help. The closest I've got is this:

    [xml]$xml_file = Get-Content ".\phone.xml"
    $xml_file.ChildNodes.ChildNodes | ForEach-Object { $_.ChildNodes[1], $_.ChildNodes[0], $_.ChildNodes[2], 
$_.ChildNodes[3]}| ConvertTo-Csv | Out-File ".\phone.csv"

But with this code I get only 2 headers: Name and #text. username, id, phoneType, value show up in the 1st column and their values in the 2nd. And the code below adds only the headers:

[xml]$data = Get-Content ".\phone.xml"
$result = New-Object psobject -Property $props
$result | Add-Member NoteProperty "id" $data.SelectSingleNode("//Property").id
$result | Add-Member NoteProperty "username" $data.SelectSingleNode("//Property").username
$result | Add-Member NoteProperty "phoneType" $data.SelectSingleNode("//Property").phoneType
$result | Add-Member NoteProperty "value" $data.SelectSingleNode("//Property").value
$result | Export-Csv ".\newphone.csv" -Force -Delimiter ';' -Encoding utf8

Santiago Squarzon · Accepted Answer · 2021-12-22 00:05:04Z

2

There is probably a better way but for now this should help you to construct the object[] which can be converted to CSV after:

$result = foreach($i in $xml.Objects.Object)
{
    $out = [ordered]@{}
    foreach($prop in $i.Property)
    {
        $out[$prop.Name] = $prop.'#text'
    }
    [pscustomobject]$out
}

$result:

id                       username phoneType value
--                       -------- --------- -----
                         JLOCK0   phone1    346-209-9609
vjWJem2qcU+cxPT2qIvqsw== CSELWYN1 phone1    562-981-5379

answered Dec 22, 2021 at 0:05

Santiago Squarzon

65.9k5 gold badges26 silver badges60 bronze badges

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

4 Comments

mklement0 Over a year ago

That's probably as good as it gets - nicely done.

Santiago Squarzon Over a year ago

@mklement0 thank you! I thought there would be a better way using the xml methods but I have close to none experience using them :P

momp Over a year ago

Thanks very much, it worked!

Santiago Squarzon Over a year ago

@momp happy to help, glad it worked!

Collectives™ on Stack Overflow

Convert xml to csv file in Powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related