Powershell - PSCustomObject with Calculated Property

Question

I'm importing a .csv file which I'm then modifying using calculated properties in a PSCustomObject. I'm stuck on one calculation where I'm attempting to lookup a value from a datarow object using one of the .csv values. We receive data with the supplier Part No and I need to lookup our corresponding Part No. Would you be able to suggest how best to do this please?

The csv content looks like this:

Vendor Code,Part No,Part Description,Bonded,Quantity,PO No,Vendor Ref
TEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234
TEZ,BBC1256,Orange,No,20,4378923,ORD089234
TEZ,ACD1349,Green,No,10,4378923,ORD089234

The SQL query $SKUs returns this as datarows:

ITEMNO VALUE
TYP-5063 ABC1234
TYP-5037 BBC1256
TYP-8069 ACD1349

So I'm looking to use the 'Part No' field from the .csv file to run a lookup against $SKUs.VALUE and return the matching $SKUs.ITEMNO.

The output .csv will then include a column called 'OUR_SKU' containing the $SKUs.ITEMNO value.

Here is my code so far:

$Files = Get-ChildItem -Path "D:\Imports\Test\INVENTORY_HUB_RECEIPTS"
$ProcessingPath = "D:\Imports\Test\INVENTORY_HUB_RECEIPTS\Processing\"
$UKEntity = "TESTTRG"
$HUB_ID = "TEST"
$SKUs = Invoke-Sqlcmd -ServerInstance "localhost" -Database "XXXX" -Query "SELECT RTRIM(ITEMNO) AS ITEMNO, RTRIM(VALUE) AS VALUE FROM [XXXX].[dbo].[ICITEMO] WHERE OPTFIELD = 'CUSTITMNO' AND VALUE <>''"

foreach ($file in $Files) {

    
    $Content = (Import-Csv -path ($ProcessingPath + $file.Name)) |
    Select-Object @{n='HUB_ID'; e={ $HUB_ID }},
    @{e={$_.'Part No'}; l='PART_NO'},
    @{e={$_.Quantity}; l='QTY_RECEIVED'},
    DATE,
    @{n='ENTITY'; e={ $UKEntity }},
    @{e={$_.'Vendor Ref'.Substring($_.'Vendor Ref'.Length -8)}; l='ORDER_ID'},

    @{n='OUR_SKU'; e={ $SKUs | Where-Object {$($_.VALUE) -eq '123ABC'} | Select-Object -ExpandProperty ITEMNO}},
    @{n='OUR_SKU_X'; e={ $SKUs | Where-Object {$($_.VALUE) -eq $_.'PART_NO'} | Select-Object -ExpandProperty ITEMNO}}

    if ($Content.Count -eq 0) {Remove-Item ($ProcessingPath + $file.Name)} else {$Content | Export-Csv -Path ($ProcessingPath + $file.Name) -Not -Force}
    }

I've tried two examples for the new property 'OUR_SKU' this works but is obviously a static value. The property 'OUR_SKU_X' is my attempt to use the supplied $.'PART_NO' and this currently returns a blank field. The variable $SKUs does contain data and so does $.'PART_NO'. I'm thinking it's either a simple syntax error or it's not possible to use $_.'PART_NO' in the script block?

Thanks

Colin

Select-Object parameters should be separated by semicolons and are between open and ending curly brackets. There is a closing curly bracket after e={ $HUB_ID } which need to be moved. Then commas need to be replaced with semicolons. — jdweng, Jun 01 '23 at 12:51
We can't see where `$Files` gets its content, nor do we see what `$SKUs` receives from the sql command, and we have no example of the csv file you're importing.. Please [edit] your question and provide more details, especially about `$SKUs` and the exact content of the csv file — Theo, Jun 01 '23 at 13:39
Inside ```$SKUs | Where-Object {$($_.VALUE) -eq $_.'PART_NO'}```, the automatic variable ```$_``` relates to the individual items from```$SKUs``` and *hides* the outer ```$_``` from the ``` (Import-Csv …) | Select-Object ...```. You probably need to capture the outer ```$_``` into a temporary variable like this: ```e={ $tmp = $_; $SKUs | Where-Object { $_.VALUE -eq $tmp.PARTNO } | Select-Object -ExpandProperty ITEMNO}}``` — mclayton, Jun 01 '23 at 14:10
@mclayton That makes sense. And this does work. Thank You! I did also have to revert back to using 'Part No' when referencing the original Select-Object statement as the changed label of 'PART_NO' was not being picked up. — ColinA, Jun 01 '23 at 14:21
@jdweng, your syntax recommendations are incorrect, and, perhaps more importantly, irrelevant, given that the syntax of the `Select-Object` call in the question is correct. Please consider deleting your comment. — mklement0, Jun 01 '23 at 15:29

mclayton · Accepted Answer · 2023-06-01T15:16:23.213

Per comments, inside the where-object scriptblock on the line:

$SKUs | Where-Object {$($_.VALUE) -eq $_.'PART_NO'}

the automatic variable $_ relates to the individual items piped in from $SKUs, which hides the outer $_ from the $Content = (Import-Csv ...) | Select-Object ...

If you want to be able to access the outer $_ inside the where-object you'll need to capture it into a temporary variable like this:

e={ $tmp = $_; $SKUs | Where-Object { $_.VALUE -eq $tmp.PARTNO } | Select-Object -ExpandProperty ITEMNO}}

Here's a cut-down example:

$parts = @"
Vendor Code,Part No,Part Description,Bonded,Quantity,PO No,Vendor Ref
TEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234
TEZ,BBC1256,Orange,No,20,4378923,ORD089234
TEZ,ACD1349,Green,No,10,4378923,ORD089234
"@ | ConvertFrom-Csv

$skus = @"
ITEMNO,VALUE
TYP-5063,ABC1234
TYP-5037,BBC1256
TYP-8069,ACD1349
"@ | ConvertFrom-Csv

$results = $parts | select-object @(
    @{l="PART_NO";  e={ $_."Part No" } },
    @{l="DESC";     e={ $_."Part Description" } },
    @{n='OUR_SKU';  e={ $part = $_; $skus | where-object { $_.VALUE -eq $part."Part No" } | Select-Object -ExpandProperty ITEMNO} }
)

Note the $part = $_; and $_.VALUE -eq $part."Part No" inside the definition of the third calculated property.

The output from the above is:

$results

PART_NO DESC      OUR_SKU
------- ----      -------
ABC1234 Dark Blue TYP-5063
BBC1256 Orange    TYP-5037
ACD1349 Green     TYP-8069

Powershell - PSCustomObject with Calculated Property

1 Answers1