CODEWITHSUNDEEP
powershell
jdids
jdids

Reputation: 581

Powershell results are grouping where they shouldn't

I have a process that reads json and returns a result set. If there are errors in a sheet, I want them returned along with the sheet name.

My code works but it ends up grouping all of the sheet names together, giving me inconsistent results

    $jsonFilePath = '{
    "stores": [
        {
            "storename": "Target",
            "files": [
                {
                    "sheets": [
                        {
                            "name": "INVENTORY",
                            "errors": [
                                "Multiple header rows found on sheet: INVENTORY", "Invalid data in field name: STARBUCKS"
                            ],
                            "locations": []
                        },
                        {
                            "name": "SITE ADD",
                            "locations": []
                        }
                    ],
                    "name": "TARGET INVENTORY.xlsx",
                    "fileId": "ff2087de-52eb-4d5a-be16-3dfa53598766",
                    "status": "failed"
                }
            ]
        }
    ]
}'



$json = $jsonFilePath | ConvertFrom-Json 

<#$error_result = #>
$json.stores | foreach-Object {
    foreach ($File in $_.files)
    {
        [PSCustomObject]@{
            storename = $_.storename
            SheetErrors = ($File.sheets.errors -join ' | ')
            SheetName = $File.sheets.name
            fileId = $File.fileId} | Where-Object {$File.sheets.errors -ne $null}
    }
  }

Which returns this result. The SITE ADD sheet name shouldn't be showing since there are no errors on that sheet:

storename SheetErrors                                                                               SheetName             fileId                              
--------- -----------                                                                               ---------             ------                              
Target    Multiple header rows found on sheet: INVENTORY | Invalid data in field name: STARBUCKS |  {INVENTORY, SITE ADD} ff2087de-52eb-4d5a-be16-3dfa53598766

This is what I'm looking for:

storename SheetErrors                                                                               SheetName             fileId                              
--------- -----------                                                                               ---------             ------                              
Target    Multiple header rows found on sheet: INVENTORY | Invalid data in field name: STARBUCKS |  INVENTORY             ff2087de-52eb-4d5a-be16-3dfa53598766

I'm only looking to return any sheet names that have errors. What am I missing to make this work?

Upvotes: 1

Views: 56

Answers (1)

mklement0
mklement0

Reputation: 438153

The problem is that $File.sheets.name unconditionally enumerates all sheet names, whereas you only want the names of those sheets that had errors.

Therefore, replace:

SheetName = $File.sheets.name

with:

SheetName = ($File.sheets | Where-Object errors).Name

Note the use of implicit to-Boolean conversion to detect the presence of a (non-empty) .errors property.

In the context of your code, with an additional optimization applied that avoids accessing $File.sheets.errors twice:

$json.stores | Foreach-Object {
  foreach ($File in $_.files) {
    if ($errs = $File.sheets.errors) {
      [PSCustomObject]@{
        storename   = $_.storename
        SheetErrors = $errs -join ' | '
        SheetName   = ($File.sheets | Where-Object errors).Name
        fileId      = $File.fileId
      }
    }
  }
}

Upvotes: 1

Related Questions