Dynamic XML to CSV conversion feedback

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 3 weeks, 5 days ago.

  • Author
    Posts
  • #73963
    Profile photo of Ryan Voice
    Ryan Voice
    Participant

    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
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.