如何浏览csv文件，当标题行包含空值或空值?

Question

我有一个有10列的a.v csv文件，除了最后一个列外，所有列都有一个名称。
我想生成一个标题为"ID", "Type", "Name", "Documentation"的新D.csv文件。
首先工作，我给这个空列一个名字，因为我不知道如何读取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"

文件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:USERPROFILEA.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:USERPROFILEB.csv -NoTypeInformation -Encoding UTF8

$NewExtract_AGRe_TWS_ALL_20200925Jobstream= Import-Csv $env:USERPROFILEB.csv | Where { $oldElementsCsv.Name -notcontains $_.Jobstream}| Export-Csv $env:USERPROFILEC.csv -NoTypeInformation  -Encoding UTF8 
$ImportCsv=Import-Csv $env:USERPROFILEC.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 $pathD.csv -NoTypeInformation -Encoding UTF8 
#open new File:
notepad $pathD.csv
Write-Host "Ending newElements "
}
}newElements

Answer 1

您可以指定标题而导入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:USERPROFILEA.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 $pathD.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