英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论