PowerShell读取表头含空值的CSV文件并生成指定格式D.csv的问题

核心问题排查 #

• 无法读取最后一列无表头数据：原有代码Import-Csv没有手动指定表头，且错误使用分号作为分隔符，实际你的A.csv分隔符为逗号
• 无法生成TechnologyEvent类型条目：原有判断逻辑顺序错误，前两个匹配规则已经覆盖了所有行，永远走不到第三个TechnologyEvent的判断分支

修复方案 #

1. 正确读取无表头最后一列 #

通过Import-Csv的-Header参数手动指定所有10列的表头，前9列沿用原有表头，最后一列直接命名为代码中用到的Planification即可，无需手动修改原CSV文件。

完整修复后代码 #

function newElements{
    process{
        # 配置输出路径
        $path = $env:USERPROFILE
        # 手动指定10列表头，跳过原有表头行读取数据
        $headers = "Jobstream","Jobstream Description","Op num","Job","Script or expected file(s)","Server","user","location","Job Description","Planification"
        $ImportCsv = Import-Csv -Path "$path\A.csv" -Delimiter ',' -Header $headers | Select-Object -Skip 1

        $output = @()
        $pattern='^(.*)-'
        $pattern2='^(.*)_'

        ForEach ($Entry in $ImportCsv){
            # 格式化Jobstream字段
            if ( ($Entry."Script or expected file(s)" -ilike 'technical') -or [string]::IsNullOrEmpty($Entry."Script or expected file(s)")  ) {
                $Entry.Jobstream = $Entry.Jobstream -replace '^(?:PAXC)?(.+?)','$1' 
            }
            else {
                $Entry.Jobstream = $Entry.'Script or expected file(s)' -replace '^(?:P|A|X|C)?(.+?)(\.(?:BAT|KSH)|$)','$1' -replace '^(.{4})-','$1'
            }

            # 调整判断顺序：优先处理TechnologyEvent，避免被其他分支拦截
            if (![string]::IsNullOrEmpty($Entry.Planification) ){
                # 将05h00格式转换为目标要求的05:00格式
                $planTime = $Entry.Planification -replace 'h',':'
                $eventObj = New-Object PsObject -Property @{
                    "ID"=[guid]::NewGuid().ToString()
                    "Type"="TechnologyEvent"
                    "Name"= $Entry."Job Description" + " " + $planTime
                    "Documentation"= ""  
                } 
                Write-Warning "New Type TechnologyEvent : $( $Entry."Job Description")"
                $output += $eventObj
            }

            # 处理其他类型条目
            if($Entry.Jobstream  -notmatch $pattern){
                $interObj = New-Object PsObject -Property @{
                    "ID"=[guid]::NewGuid().ToString()
                    "Type"="TechnologyInteraction"
                    "Name"= $Entry.Jobstream
                    "Documentation"=$Entry."Jobstream Description" + " `r`nServer: $($Entry.Server)  `r`nuser: $($Entry.user) "
                }   
                Write-Warning "Found new TechnologyInteraction : $($Entry.Jobstream)"
                $output += $interObj
            }  
            elseif($Entry.Jobstream  -match $pattern2) {
                $serviceObj = New-Object PsObject -Property @{
                    "ID"=[guid]::NewGuid().ToString()
                    "Type"="TechnologyService"
                    "Name"=$Entry.Jobstream
                    "Documentation"= $Entry."Job Description"+ " `r`nServer: $($Entry.Server)  `r`nuser: $($Entry.user) "
                } 
                Write-Warning "New TechnologyService : $($Entry.Jobstream)"
                $output += $serviceObj
            }
            else{
                $serviceObj2 = New-Object PsObject -Property @{
                    "ID"=[guid]::NewGuid().ToString()
                    "Type"="TechnologyService"
                    "Name"= $Entry.Jobstream
                    "Documentation"= $Entry."Job Description" + " `r`nServer: $($Entry.Server)  `r`nuser: $($Entry.user)"
                } 
                Write-Warning "New TechnologyService : $($Entry.Jobstream)"
                $output += $serviceObj2
            }
        }
        # 导出目标D.csv
        $output | Select-Object -Property "ID","Type","Name","Documentation" -Unique | Export-Csv "$path\D.csv" -NoTypeInformation -Encoding UTF8 
        # 打开生成的文件
        notepad "$path\D.csv"
        Write-Host "处理完成"
    }
}
newElements

可选补充 #

如果你需要保留原有过滤已存在元素的逻辑，提前给$oldElementsCsv变量赋值后，在读取$ImportCsv后添加Where { $oldElementsCsv.Name -notcontains $_.Jobstream}过滤即可。

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