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

 
Keymaster
1 year, 3 months ago.

  • Author
    Posts
  • #73963

    Participant
    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
        
        do{
            $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

    Keymaster
    Points: 7
    Rank: Member

    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.