Home > Back-end >  Convert xml to csv file in Powershell
Convert xml to csv file in Powershell

Time:12-23

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

CodePudding user response:

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
  • Related