Search code examples
powershellapicsvinvoke-restmethod

Powershell Invoke-RestMethod - Formatting response for .csv Export

I'm writing a script to pull order details from an API using Invoke-RestMethod. I have the response which PS has converted json into a PSCustomObject. What I'm attempting to do is produce rows in a .csv file where nested array objects are split out over each row and the non nested items are duplicated on those rows. So for example we have order header and order detail data. Order detail can contain more than one product. Imagine a simple SQL join.

Currently I'm doing this which is giving me one order per row with multiple skus on the same row.

$result = $OrdDetailResponse | Select-Object -Expand Orders |
Select-Object NumOrderID,ReferenceNum,SKU,
@{n='Order Id';e={$_.NumOrderID -join ','}},
@{n='Reference Number';e={$_.GeneralInfo.ReferenceNum -join ','}},
@{n='SKU';e={$_.Items.SKU -join ','}} -Exclude NumOrderID, ReferenceNum,SKU |
Export-Csv -Path "D:\Exports\Test\export.csv" -NOT

Current Output

The desired result would be

Desired Result

I think I need to loop through each record in the response and expand the array objects to split the SKU's onto each row but not sure how to effectively duplicate the OrderID and Reference Number on to those rows.

Any help greatly appreciated

Solution

  • You would only need to split on comma on the .Items.SKU property and then create a new object per enumerated SKU. To split on on commas I'm using the .Split Method. I've also changed your Select-Object statement for [pscustomobject], much more readable in my opinion.

    $OrdDetailResponse.Orders | ForEach-Object {
    foreach($sku in $_.Items.SKU.Split(',')) {
        [pscustomobject]@{
            'Order Id' = $_.NumOrderID -join ','
            'Reference Number' = $_.GeneralInfo.ReferenceNum -join ','
            'SKU' = $sku
        }
    }
} | Export-Csv -Path "D:\Exports\Test\export.csv" -NOT