Powershell – 具有计算属性的PSCustomObject

huangapple go评论59阅读模式
英文:

Powershell - PSCustomObject with Calculated Property

问题

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:

供应商代码,零件号,零件描述,保税,数量,采购订单号,供应商参考
TEZ,ABC1234,深蓝色,否,50,4378923,ORD089234
TEZ,BBC1256,橙色,否,20,4378923,ORD089234
TEZ,ACD1349,绿色,否,10,4378923,ORD089234

The SQL query $SKUs returns this as datarows:

项目号 值
TYP-5063 ABC1234
TYP-5037 BBC1256
TYP-8069 ACD1349

So I'm looking to use the '零件号' field from the .csv file to run a lookup against $SKUs.值 and return the matching $SKUs.项目号.

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

英文:

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

答案1

得分: 2

根据注释,在以下代码行中的where-object脚本块内:

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

自动变量$_关联到从$SKUs管道传入的各个项目,这会隐藏外部$_$Content = (Import-Csv ...) | Select-Object ...中。

如果您想要能够在where-object内访问外部$_,您需要将其捕获到一个临时变量中,就像这样:

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

以下是一个简化的示例:

$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} }
)

注意在第三个计算属性的定义中的$part = $_;$_.VALUE -eq $part."Part No"

以上代码的输出是:

$results

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

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

huangapple
  • 本文由 发表于 2023年6月1日 20:25:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/76381843.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定