Powershell Invoke-RestMethod - Formatting response for .csv Export

Question

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

The desired result would be

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

score 0 · Accepted Answer · answered Dec 08 '22 at 15:47

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

Powershell Invoke-RestMethod - Formatting response for .csv Export

1 Answers1