Dynamic XML to CSV conversion feedback

Welcome Forums General PowerShell Q&A Dynamic XML to CSV conversion feedback

This topic contains 1 reply, has 2 voices, and was last updated by

1 year, 10 months ago.

  • Author
  • #73963

    Topics: 1
    Replies: 1
    Points: 0
    Rank: Member

    Hey All,

    I am looking for some feedback/alternatives for converting xml data to csv, specifically pulling an ultipro report which is base64 encoded XML data and converting that into csv data, while dynamically creating headers based on xml attributes.

    Here is my code snippet. it works, although runs sliiiiiighty slower that i would like it to, i think due to injecting data in the psobject object? I haven't run measures against it though.

    #decode the xml data
    $base64decode = [xml][System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($retrievereportresult.Envelope.Body.StreamReportResponse.ReportStream))
    #Capture the xml schema
    $reportschema = $base64decode.dataset.ChildNodes[1].ChildNodes
    #Capture the XML "rows"
    $reportrows = $base64decode.dataset.data.ChildNodes 
    #some filtering
    $validreportrows = $reportrows | ?{$_.value[0].nil -ne $true}
    #Number of columns based on schema 
    $numfields = $reportschema.count
    $Create variables that hold the header data
    $i = 0 
    foreach($field in $reportschema){
        New-Variable -Name "Attribute$($i)" -Value $field.name
        $i ++
    $csvdata = @()
    #Actual XML item to CSV row conversion
    foreach($row in $validreportrows){
        $i = 0
        $rowdata = New-Object -TypeName psobject
            $varname = $(Get-Variable -Name attribute$($i)).Value
            $varvalue = $row.value[$i]
            #Some data cleanup
            if($row.value[$i].GetType().name -eq "String"){
                $varvalue =  $row.value[$i].trim()
            if($row.value[$i].nil -eq $True){
                $varvalue = $null
            Add-Member -InputObject $rowdata -NotePropertyName $varname -NotePropertyValue $varvalue        
            $i ++
        until($i -eq $numfields-1)   
        $csvdata += $rowdata
    $csvdata | Export-Csv -NoTypeInformation -Encoding UTF8 -Path C:\scripts\UltiPro\cuttent.csv
  • #74066

    Topics: 13
    Replies: 4872
    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    I mean, if it works, then it's great. PowerShell isn't necessarily meant to be a speed demon, so if it's acceptably slow, then good on you.

The topic ‘Dynamic XML to CSV conversion feedback’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort