Compare-Object导出CSV异常：System.Object[]及字符替换问题求助

尝试对比两个PSCustomObject数组，将不匹配的数据整合后导出为CSV文件，但导出的CSV存在两个问题：

1. 部分单元格显示System.Object[]
2. 字符“-”被替换为“?”，即使已指定-Encoding utf8

用户实现代码如下：

对比函数代码 #

function DataCompare {

$NotMatch = @()
#Compares the two arrays together and saves the ones that are different in the InfoFusion into ResultsFromFusion
$ResultsFromFusion = Compare-Object -ReferenceObject $Global:InfoFusion -DifferenceObject $Global:InfoPPMS -Property programmeLevel1, programmeLevel2, programmeLevel3, programmeLevel4 -PassThru | Where-Object SideIndicator -eq '<='

#Loops through the fusion results that dont match
ForEach ($Project in $ResultsFromFusion){
    
    #Gets the matching data from the PPMS data so the NotMatch csv has both the fusion and ppms data
    $PPMSFromDiff = $Global:InfoPPMS -match $Project.ProjectNumber
    $NotMatch += @([pscustomobject]@{ProjectId = $($Project.ProjectId);
                                     ProjectNumber = $($Project.ProjectNumber);
                                     programmeLevelFusion1 = $($Project.programmeLevel1); 
                                     programmeLevelPPMS1 = $($PPMSFromDiff.programmeLevel1); 
                                     programmeLevelFusion2 = $($Project.programmeLevel2); 
                                     programmeLevelPPMS2 = $($PPMSFromDiff.programmeLevel2);
                                     programmeLevelFusion3 = $($Project.programmeLevel3); 
                                     programmeLevelPPMS3 = $($PPMSFromDiff.programmeLevel3); 
                                     programmeLevelFusion4 = $($Project.programmeLevel4); 
                                     programmeLevelPPMS4 = $($PPMSFromDiff.programmeLevel4)})
    }#Loop End
$NotMatch | export-csv -Path $NotMatchPath -NoTypeInformation -Encoding utf8
}#Function End

数组定义代码 #

$Global:InfoPPMS += @([pscustomobject]@{ProjectId = $($Project.ProjectId);
                                                ProjectNumber = $($Project.ProjectNumber);
                                                programmeLevel1 = $($programmeLevel1); 
                                                programmeLevel2 = $($programmeLevel2); 
                                                programmeLevel3 = $($programmeLevel3); 
                                                programmeLevel4 = $($programmeLevel4)})
$Global:InfoFusion += @([pscustomobject]@{ProjectId = $($Project.ProjectId); 
                                                          ProjectNumber = $($Project.ProjectNumber);
                                                          programmeLevel1 = $($RestMethID.programmeLevel1); 
                                                          programmeLevel2 = $($RestMethID.programmeLevel2); 
                                                          programmeLevel3 = $($RestMethID.programmeLevel3); 
                                                          programmeLevel4 = $($RestMethID.programmeLevel4)})

1. 解决System.Object[]问题 #

出现这个问题的核心原因是$PPMSFromDiff = $Global:InfoPPMS -match $Project.ProjectNumber可能返回多个匹配对象，直接访问属性时得到的是数组而非单个值，导出CSV时就会显示System.Object[]。

修复方式： #

• 使用精确匹配代替正则匹配：用Where-Object配合-eq确保只获取单个匹配项（如果ProjectNumber是唯一标识）：

  $PPMSFromDiff = $Global:InfoPPMS | Where-Object { $_.ProjectNumber -eq $Project.ProjectNumber } | Select-Object -First 1
  

• 如果确实存在多个匹配项，将数组转为字符串：

  programmeLevelPPMS1 = $($PPMSFromDiff.programmeLevel1 -join ', ')
  

2. 解决“-”变为“?”的编码问题 #

在Windows PowerShell中，Export-Csv的utf8参数实际导出的是不带BOM的UTF-8，部分软件（如Excel）打开时会识别为ANSI编码，导致特殊字符乱码。

修复方式： #

• 如果使用PowerShell 7+，直接使用-Encoding utf8BOM：

  $NotMatch | Export-Csv -Path $NotMatchPath -NoTypeInformation -Encoding utf8BOM
  

• 如果使用Windows PowerShell，通过.NET类导出带BOM的UTF-8：

  $NotMatch | ConvertTo-Csv -NoTypeInformation | Out-File -Path $NotMatchPath -Encoding ([System.Text.UTF8Encoding]::new($true))
  

function DataCompare {
    $NotMatch = @()
    # 对比两个数组，获取InfoFusion中存在差异的项
    $ResultsFromFusion = Compare-Object -ReferenceObject $Global:InfoFusion -DifferenceObject $Global:InfoPPMS `
        -Property programmeLevel1, programmeLevel2, programmeLevel3, programmeLevel4 -PassThru | 
        Where-Object SideIndicator -eq '<='

    # 遍历差异项，整合两边数据
    foreach ($Project in $ResultsFromFusion) {
        # 精确匹配PPMS中的对应项目，确保只取单个结果
        $PPMSFromDiff = $Global:InfoPPMS | Where-Object { $_.ProjectNumber -eq $Project.ProjectNumber } | Select-Object -First 1
        
        # 处理可能的空值，避免导出空数组
        $ppmsLevel1 = if ($PPMSFromDiff) { $PPMSFromDiff.programmeLevel1 } else { $null }
        $ppmsLevel2 = if ($PPMSFromDiff) { $PPMSFromDiff.programmeLevel2 } else { $null }
        $ppmsLevel3 = if ($PPMSFromDiff) { $PPMSFromDiff.programmeLevel3 } else { $null }
        $ppmsLevel4 = if ($PPMSFromDiff) { $PPMSFromDiff.programmeLevel4 } else { $null }

        $NotMatch += [pscustomobject]@{
            ProjectId               = $Project.ProjectId
            ProjectNumber           = $Project.ProjectNumber
            programmeLevelFusion1   = $Project.programmeLevel1
            programmeLevelPPMS1     = $ppmsLevel1
            programmeLevelFusion2   = $Project.programmeLevel2
            programmeLevelPPMS2     = $ppmsLevel2
            programmeLevelFusion3   = $Project.programmeLevel3
            programmeLevelPPMS3     = $ppmsLevel3
            programmeLevelFusion4   = $Project.programmeLevel4
            programmeLevelPPMS4     = $ppmsLevel4
        }
    }

    # PowerShell 7+ 用下面的行
    $NotMatch | Export-Csv -Path $NotMatchPath -NoTypeInformation -Encoding utf8BOM
    # Windows PowerShell 用下面的行替代上面的导出命令
    # $NotMatch | ConvertTo-Csv -NoTypeInformation | Out-File -Path $NotMatchPath -Encoding ([System.Text.UTF8Encoding]::new($true))
}

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