none
PowerShell script to merge 2 csv files as it is (the 2nd csv as additional columns into the 1st csv) RRS feed

  • Question

  • I have 2 csv file that don't have any unique identifier, the number of rows of both files is always the same, I want to merge them as it is (the 2nd csv as additional columns into the 1st csv).

    Content of file1.csv:
    Server,Info
    server1,item1
    server1,item2
    server1,item3
    server2,item1
    server2,item2
    server2,item3
    server3,item1
    server3,item2
    server3,item3

    Content of file2.csv:
    Items,ColumnA,ColumnB,ColumnC
    item#1:,aa,jj,ss
    item#2:,bb,kk,tt
    item#3:,cc,ll,uu
    item#1:,dd,mm,vv
    item#2:,ee,nn,ww
    item#3:,ff,oo,xx
    item#1:,gg,pp,yy
    item#2:,hh,qq,zz
    item#3:,ii,rr,ab

    Expecting output of csv file:
    Server,Info,Items,ColumnA,ColumnB,ColumnC
    server1,item1,item#1:,aa,jj,ss
    server1,item2,item#2:,bb,kk,tt
    server1,item3,item#3:,cc,ll,uu
    server2,item1,item#1:,dd,mm,vv
    server2,item2,item#2:,ee,nn,ww
    server2,item3,item#3:,ff,oo,xx
    server3,item1,item#1:,gg,pp,yy
    server3,item2,item#2:,hh,qq,zz
    server3,item3,item#3:,ii,rr,ab

    I have search intensively in the net, but couldn't find any solution... Would really appreciate if someone can provide me some answers here... Thank you in advance :)

    Thursday, July 21, 2016 6:43 AM

Answers

  • $file1 = Get-Content file1.csv
    $file2 = Get-Content file2.csv
    
    $csv = for($i=0;$i -lt $file1.Count;$i++) {
        $file1[$i], $file2[$i] -join ','
    }
    
    Out-File -InputObject $csv -FilePath file3.csv

    Thursday, July 21, 2016 9:05 AM
  • Hi Leif-Arne,

    Your solution works, but need to add the below line to make the output csv file readable:
    Import-Csv -Path .\file3.csv | Export-Csv -Path .\result.csv -Delimiter "," -NoTypeInformation
    Thank you so so much :)
    • Marked as answer by edwinskyline Friday, July 22, 2016 1:44 AM
    Friday, July 22, 2016 1:42 AM

All replies

  • $file1 = Get-Content file1.csv
    $file2 = Get-Content file2.csv
    
    $csv = for($i=0;$i -lt $file1.Count;$i++) {
        $file1[$i], $file2[$i] -join ','
    }
    
    Out-File -InputObject $csv -FilePath file3.csv

    Thursday, July 21, 2016 9:05 AM
  • If the two files do not line up perfectly then Leif's method will work.  If not you will have to use a join to select the matching records.  This can be done with a simple text query.

    \_(ツ)_/

    Thursday, July 21, 2016 11:14 AM
  • Hi Leif-Arne,

    Your solution works, but need to add the below line to make the output csv file readable:
    Import-Csv -Path .\file3.csv | Export-Csv -Path .\result.csv -Delimiter "," -NoTypeInformation
    Thank you so so much :)
    • Marked as answer by edwinskyline Friday, July 22, 2016 1:44 AM
    Friday, July 22, 2016 1:42 AM