PowerShell中通过CSV字段匹配SQL数据生成OUR_SKU列的问题

问题场景 #

我正在导入CSV文件，使用PSCustomObject的计算属性修改数据。目前卡在一个计算环节：需要通过CSV中的“Part No”字段，从SQL查询返回的$SKUs数据行对象中查找匹配的自有零件号（ITEMNO），将其作为OUR_SKU列输出到新CSV中。

CSV内容示例 #

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

SQL查询$SKUs返回的数据行 #

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

现有代码问题 #

现有代码中，静态值匹配的OUR_SKU字段可正常工作，但尝试使用$_.'PART_NO'动态匹配的OUR_SKU_X字段返回空值。$SKUs和$_.'PART_NO'均有数据，怀疑是语法问题或脚本块中无法使用该变量。

现有代码片段：

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

问题原因 #

在OUR_SKU_X的计算属性脚本块中，嵌套的Where-Object会改变$_的作用域：内层Where-Object里的$_指向的是$SKUs集合中的每一行对象，而非CSV文件的当前行。因此$_.'PART_NO'实际上是在访问$SKUs行的PART_NO属性（不存在），导致匹配失败返回空值。

解决方法 #

方法1：保存当前行变量到局部变量 #

在计算属性的脚本块开头，将CSV当前行的PART_NO赋值给一个局部变量，避免作用域冲突：

@{n='OUR_SKU_X'; e={ 
    $currentPartNo = $_.'PART_NO'
    $SKUs | Where-Object {$_.VALUE -eq $currentPartNo} | Select-Object -ExpandProperty ITEMNO
}}

方法2：转换为哈希表提升查找效率 #

如果$SKUs数据量较大，每次遍历查找会影响性能，建议将$SKUs转换为哈希表（键为VALUE，值为ITEMNO），直接通过键值对查找：

# 先将$SKUs转换为哈希表
$skuMap = @{}
foreach ($sku in $SKUs) {
    $skuMap[$sku.VALUE] = $sku.ITEMNO
}

# 然后在计算属性中直接查找
@{n='OUR_SKU_X'; e={ $skuMap[$_.'PART_NO'] }}

修正后的完整代码 #

$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 <>''"

# 转换为哈希表提升查找效率
$skuMap = @{}
foreach ($sku in $SKUs) {
    $skuMap[$sku.VALUE] = $sku.ITEMNO
}

foreach ($file in $Files) {
    $Content = Import-Csv -Path (Join-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_X'; e={ $skuMap[$_.'PART_NO'] }}

    if (-not $Content) {
        Remove-Item (Join-Path $ProcessingPath $file.Name)
    } else {
        $Content | Export-Csv -Path (Join-Path $ProcessingPath $file.Name) -NoTypeInformation -Force
    }
}

注：代码中替换了($ProcessingPath + $file.Name)为Join-Path，这是PowerShell中拼接路径的更安全规范的方式；同时修正了Export-Csv的参数错误。

内容的提问来源于stack exchange，提问作者ColinA