如何在powershell中将两个CSV文件与不同的列标题组合起来

Question

如何使用PowerShell将两个具有不同头值的csv文件组合成一个CSV文件

带有标头值的csvf1文件内容

"softwarename","InstalledDate","InstallPath","hostname","softwarestatus"
"Adobe Flash Player 11 ActiveX 64-bit","","","den00ltl","actve"
"VNC Server 5.1.0","20160303","","den00ltl","actve"
"7-Zip 18.05 (x64 edition)","20210803","","den00ltl","actve"
"McAfee Agent","20170822","C:\Program Files (x86)\McAfee\Common Framework\","den00ltl","actve"
"Java(TM) 6 Update 26 (64-bit)","20150717","C:\Program Files\Java\jre6\","den00ltl","actve"
"Python 3.9.6 Documentation (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 Standard Library (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 Utility Scripts (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 Test Suite (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 pip Bootstrap (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 Tcl/Tk Support (64-bit)","20210806","","den00ltl","actve"
"VNC Viewer 5.1.0","20160303","","den00ltl","actve"
"Python 3.9.6 Executables (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 Core Interpreter (64-bit)","20210806","","den00ltl","actve"
"Python 3.9.6 Development Libraries (64-bit)","20210806","","den00ltl","actve"
"Local Administrator Password Solution","20210803","","den00ltl","actve"
"Python 3.9.6 Add to Path (64-bit)","20210806","","den00ltl","actve"

带有标头值的csvf2文件内容

"softwarename","User"
"Python 3.9.6 Add to Path (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 pip Bootstrap (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python Launcher ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Tcl/Tk Support (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Utility Scripts (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Documentation (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Test Suite (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Standard Library (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Executables (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Development Libraries (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Core Interpreter (64-bit) ","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Google Update Helper ","S-1-5-18"
"7-Zip 18.05 (x64 edition) ","S-1-5-21-3494035724-152720622-3187703539-500"

我需要输出，比如如果csv2文件软件与csfv1文件匹配，那么完整的

"softwarename","InstalledDate","hostname","softwarestatus","user" 
"Python 3.9.6 Development Libraries (64-bit)","20210806","den00ltl","actve",S-1-5-21-3138815620-4253048750-3916773603-37297"
"VNC Server 5.1.0","20160303","den00ltl","actve","null" 

请给我一些指导和帮助。

Answer 1

试试这个：

$csvf1 = Import-Csv -Path ./csvf1
$csvf2 = Import-Csv -Path ./csvf2
$csvf3 = $csvf1 | ForEach-Object -Process {
  ($o = $_).PSObject.Properties.Remove('InstallPath')
  $o | Add-Member -MemberType NoteProperty -Name 'User' -Value $null
  $csvf2 | Where-Object -FilterScript {
      $_.softwarename.trim() -eq $o.softwarename.trim()
    } | ForEach-Object -Process { $o.User = $_.User; $o }
  if( $o.User -eq $null ) { $o.User = 'null'; $o }
}
$csvf3 | Export-Csv -Path ./csvf3 -NoTypeInformation
Get-Content -Path ./csvf3

输出：

"softwarename","InstalledDate","hostname","softwarestatus","User"
"Adobe Flash Player 11 ActiveX 64-bit","","den00ltl","actve","null"
"VNC Server 5.1.0","20160303","den00ltl","actve","null"
"7-Zip 18.05 (x64 edition)","20210803","den00ltl","actve","S-1-5-21-3494035724-152720622-3187703539-500"
"McAfee Agent","20170822","den00ltl","actve","null"
"Java(TM) 6 Update 26 (64-bit)","20150717","den00ltl","actve","null"
"Python 3.9.6 Documentation (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Standard Library (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Utility Scripts (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Test Suite (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 pip Bootstrap (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Tcl/Tk Support (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"VNC Viewer 5.1.0","20160303","den00ltl","actve","null"
"Python 3.9.6 Executables (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Core Interpreter (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Python 3.9.6 Development Libraries (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"
"Local Administrator Password Solution","20210803","den00ltl","actve","null"
"Python 3.9.6 Add to Path (64-bit)","20210806","den00ltl","actve","S-1-5-21-3138815620-4253048750-3916773603-37297"

一个使用[System.[Linq.Enumerable]::GroupJoin 方法的版本。

$csvf1 = Import-Csv -LiteralPath .\csvf1
$csvf2 = Import-Csv -LiteralPath .\csvf2
$outerkey = $innerkey = [Func[object,string]]{ $args[0].softwarename.trim() }
$selector = [Func[object,[Collections.Generic.IEnumerable[object]],object]]{
  ($o = $args[0]).PSObject.Properties.Remove('InstallPath')
  $o | Add-Member -MemberType NoteProperty -Name 'User' -Value 'null'
  if( $args[1].count -gt 0 ) {
    $args[1] | ForEach-Object -Process { $o.User = $_.User; $o }
  } else { $o }
}
$csvf3 = [Linq.Enumerable]::GroupJoin($csvf1, $csvf2, $outerkey, $innerkey, $selector)
$csvf3 | Export-Csv -Path .\csvf3 -NoTypeInformation
Get-Content -Path .\csvf3

更多细节这里。