Merge 2 csv files in powershell [duplicate]

Question

I have 2 csv files, I want to add a new column from file A to file B to a new file. Atm, It doesn't take the values from A.

File A.csv

ID    Name

1     Peter

2     Dalas

File B.CSV

Class

Math

Physic

New file will be:

ID    Name   Class

1     Peter  Math

2     Dalas  Physics

Both files have the same number of rows.

As the following the code I'm using, I would like to now how to take the values from file A and put it in file B.

$CSV1 = Import-Csv ".\A.csv"
$CSV2 = Import-Csv ".\B.csv"


$CSV1 | ForEach-Object {
  $Value= $_
  $CSV2 | Select-Object *, @{Name='Information';Expression={ $Value}}

} | Export-Csv "C.csv" -NoTypeInformation

Although the title suggest a duplicate the content of the question differs as it concerns joining objects (rather than files) based on their index. Consider $CSV1 | Join $CSV2 {$LeftIndex -eq $RightIndex), undocumented feature of: stackoverflow.com/questions/1848821/… — iRon
Updated the Join-Object cmdlet to better support a join on index by omitting the -On parameter. The command to achieve the above is now simply: $CSV1 | Join $CSV2 — iRon

Mark Wragg Mark Wragg · Accepted Answer · 2017-07-14T08:21:05

Assuming that your two CSV files are correctly aligned (e.g you want to merge the data based on their row numbers and aren't linking by any other key) I suggest the following:

$CSV1 = Import-Csv ".\A.csv"
$CSV2 = Import-Csv ".\B.csv"

$CSV1 | ForEach-Object -Begin {$i = 0} {  
    $_ | Add-Member -MemberType NoteProperty -Name 'Class' -Value $CSV2[$i++].Class -PassThru 
} | Export-Csv "C.csv" -NoTypeInformation

Explanation:

Uses a -Begin script block to set a counter to 0 (you could do this before the ForEach-Object but using -Begin nicely links its purpose to the code block).
Uses Add-Member to add the 'Class' property to each line in CSV1, using the Array index of the line in CSV2 (and incrementing that index as it does it with ++).
Uses the -PassThru switch to return the object to the pipeline.

If you want to do it the other way around (B > A) you could take the same approach but would need to do it like this:

$CSV2 | ForEach-Object -Begin {$i = 0} {
    $CSV1[$i++] | Add-Member -MemberType NoteProperty -Name 'Class' -Value $_.Class -PassThru 
} | Export-Csv "C.csv" -NoTypeInformation

I'm actually surprised $_.Class still works as its the other side of a new pipeline but it seems to.

You can also use a calculated expression like you originally planned to, but then you do need to use an extra variable to store $Class due to the extra pipeline:

$CSV2 | ForEach-Object -Begin {$i = 0} {
    $Class = $_.Class
    $CSV1[$i++] | Select @{Name='Class';Expression={$Class}},*
} | Export-Csv "C.csv" -NoTypeInformation

Merge 2 csv files in powershell [duplicate]

1 Answers