How to combine two CSV files with different column headers in powershell

Question

How to combine 2 csv files with different header values into one single CSV file using PowerShell

csvf1 file content with header values

"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 file content with header values

"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"

I need output like if csv2 file software matches with csfv1 file then the complete

"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"

can some please guide and help on it

Most googles point at this custom Join-Object cmdlet stackoverflow.com/questions/1848821/… — Nick.Mc
– Nick.Mc, Commented Aug 17, 2021 at 22:54
Using this Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?): $csvf3 = $csvf1 |FullJoin $csvf2 -on softwarename — iRon
– iRon, Commented Aug 18, 2021 at 9:21

Andrei Odegov · Accepted Answer · 2021-08-27 06:33:29Z

Try this:

$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

Output:

"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"

A version using [System.[Linq.Enumerable]::GroupJoin method.

$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

More details here.

Collectives™ on Stack Overflow

How to combine two CSV files with different column headers in powershell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related