时间戳

首页 > 解决方案 > 当标题行包含空值或空值时，如何浏览 csv 文件？

windows - 当标题行包含空值或空值时，如何浏览 csv 文件？

问题描述

我有一个包含 10 列的 A.csv 文件，除了最后一个之外，所有列都有一个名称。
我想生成一个带有 headers 的新 D.csv 文件"ID", "Type", "Name", "Documentation"。
刚开始工作时，我给这个空列起了个名字，因为我不知道如何读取 powershell 中空列的值。我不能为类型做"TechnologyEvent"请，你能帮帮我吗？

更复杂的 A.Csv

Jobstream,Jobstream Description,Op num, Job,Script or expected file(s), Server, user,location,Job Description,  
PAXCWEBX965H,"RMNF - xWEBX1","9","","technical","","","","Begin Of JobStream","05h00"
PAXCWEBX965H,"RMNF - xWEBX1","40","PWEBX0GJ","PWEBX-965H-005S.KSH","PRAXCWBXLBDD01","svc_bddo_user","F+WEBX-027","Lancement Sauv. RMAN","05h00"

Fichier D.csv

ID,"Type","Name","Documentation"
79570056-ab4f-6969-8c58-a5bd5847a895,"TechnologyInteraction","WEBX965H","RMNF - xWEBX1"
b57a5fff-9dd2-4cm1-9141-04c042f49498,"TechnologyService","WEBX965H-005S","Lancement Sauv. RMAN"
id-bdca3a1b39ed49cd80ae41deaa88094c,"TechnologyEvent","Lancement Sauv. RMAN  05:00",""

function newElements{

    process{
    
        # import data 
    $data = Import-csv -Path $env:USERPROFILE\A.csv -Delimiter ';'

        $NewExtract_AGRe_TWS_ALL_20200925= ForEach($Entry in $ElementCsv){
  

 
    # -or [string]::IsNullOrEmpty($Entry."Script or expected file(s)")
    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'
    }

        $Entry
    }
        # Export Extract_AGRe_TWS_ALL_20211005.csv  in new B.csv file 
        $NewExtract_AGRe_TWS_ALL_20200925  | Export-Csv $env:USERPROFILE\B.csv -NoTypeInformation -Encoding UTF8
    
        $NewExtract_AGRe_TWS_ALL_20200925Jobstream= Import-Csv $env:USERPROFILE\B.csv | Where { $oldElementsCsv.Name -notcontains $_.Jobstream}| Export-Csv $env:USERPROFILE\C.csv -NoTypeInformation  -Encoding UTF8 
        $ImportCsv=Import-Csv $env:USERPROFILE\C.csv
        #Output progress:
        $output= @()
        #Jobstream, Jobstream Description,Op num, Job,Script or expected file(s),Server, user, location, Job Description
        ForEach ($column in $ImportCsv){
        $pattern='^(.*)-'
        $pattern2='^(.*)_'
    if($column.Jobstream  -notmatch $pattern){

        $output1= New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyInteraction"; "Name"= $column.Jobstream; "Documentation"=$column."Job Description" + " `r`nSever: $($column.Server)  `r`nuser: $($column.user) " ;  "Planification"= $column.Planification }   
        Write-Warning "Found new Jobstream : $($column.Jobstream)"
        Write-Warning "Found new Jobstream Description : $($column."Job Description")"
        $output= $output + $output1
    }  

  
          elseif($column.Jobstream  -match $pattern2) 
    {

        $output2= New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyService"; "Name"=$column.Jobstream; "Documentation"= $column."Job Description"+ " `r`nSever: $($column.Server)  `r`nuser: $($column.user) " ; "Planification"= $column.Planification   } 
        Write-Warning "New Jobstream : $($column.Jobstream)"
        Write-Warning "New Jobstream Description : $($column."Job Description")"
        $output= $output + $output2

    } 
   elseif (![string]::IsNullOrEmpty($column.Planification) ){

        $output4= New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyEvent"; "Name"= $column."Job Description" + " " + $column.Planification ; "Documentation"= ""  } 

        Write-Warning "New Type TechnologyEvent : $( $column."Job Description")"
        Write-Warning "New Type TechnologyEvent Description : $($column."Job Description")"
        $output= $output + $output4

    }
    else{

        $output3= New-Object PsObject -Property @{"ID"=[guid]::NewGuid().ToString(); "Type"="TechnologyService"; "Name"= $column.Jobstream; "Documentation"= $column."Job Description" + " " + $column.Planification + " `r`nSever: $($column.Server)  `r`nuser: $($column.user)" } 

        Write-Warning "New Jobstream : $($column.Jobstream)"
        Write-Warning "New Jobstream Description : $($column."Job Description")"
        $output= $output + $output3
    }

 

    }
    $output |Select-Object -Property "ID","Type","Name","Documentation" -Unique| Export-Csv $path\D.csv -NoTypeInformation -Encoding UTF8 

    #open new File:
    notepad $path\D.csv
    Write-Host "Ending newElements "
    }
}newElements

标签： windowspowershellcsv

解决方案

您可以在导入 CSV 时指定标题-Headers：

# I set the 10th column name to "Schedule".
# You need to skip the first row if the file contains a header already:
$header = ('Jobstream','StreamDescription','OpNum','Job','Script','Server','user','location','JobDescription','Schedule')
$data = Import-csv -Path $env:USERPROFILE\A.csv -Delimiter ';' -Header $header |
  Select -Skip 1

要创建自定义 csv，请尝试使用以下内容：

$output = ForEach ($row in $ImportCsv) {
  [PSCustomObject][ordered]@{
    "ID"=[guid]::NewGuid().ToString(); 
    "Type"="TechnologyInteraction"; 
    "Name"= $row.Jobstream; 
    "Documentation"="$($row.JobDescription) `r`nServer: $($row.Server)  `r`nnuser: $($row.user) " ;  
  }
}

$output | Export-Csv $path\D.csv -NoTypeInformation -Encoding UTF8

列表格式的输出如下所示：

ID            : 85bf5f43-03fe-47af-9b04-84de8fe59f1c
Type          : TechnologyInteraction
Name          : PAXCWEBX965H
Documentation : Begin Of JobStream 
                Server:   
                nuser:  

ID            : 70e17ea9-2219-4836-b17f-9c9013a7ed6d
Type          : TechnologyInteraction
Name          : PAXCWEBX965H
Documentation : Lancement Sauv. RMAN 
                Server: PRAXCWBXLBDD01  
                nuser: svc_bddo_user

推荐阅读