英文:
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,ORD089234TEZ,BBC1256,橙色,否,20,4378923,ORD089234TEZ,ACD1349,绿色,否,10,4378923,ORD089234
The SQL query $SKUs returns this as datarows:
项目号 值TYP-5063 ABC1234TYP-5037 BBC1256TYP-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 RefTEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234TEZ,BBC1256,Orange,No,20,4378923,ORD089234TEZ,ACD1349,Green,No,10,4378923,ORD089234
The SQL query $SKUs returns this as datarows:
ITEMNO VALUETYP-5063 ABC1234TYP-5037 BBC1256TYP-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 RefTEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234TEZ,BBC1256,Orange,No,20,4378923,ORD089234TEZ,ACD1349,Green,No,10,4378923,ORD089234"@| ConvertFrom-Csv$skus = @"ITEMNO,VALUETYP-5063,ABC1234TYP-5037,BBC1256TYP-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"。
以上代码的输出是：
$resultsPART_NO DESC OUR_SKU------- ---- -------ABC1234 Dark Blue TYP-5063BBC1256 Orange TYP-5037ACD1349 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 RefTEZ,ABC1234,Dark Blue,No,50,4378923,ORD089234TEZ,BBC1256,Orange,No,20,4378923,ORD089234TEZ,ACD1349,Green,No,10,4378923,ORD089234"@ | ConvertFrom-Csv$skus = @"ITEMNO,VALUETYP-5063,ABC1234TYP-5037,BBC1256TYP-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:
$resultsPART_NO DESC OUR_SKU------- ---- -------ABC1234 Dark Blue TYP-5063BBC1256 Orange TYP-5037ACD1349 Green TYP-8069
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论