merge 2 different CSV with different header into CSV in powershell.

Welcome Forums General PowerShell Q&A merge 2 different CSV with different header into CSV in powershell.

Viewing 7 reply threads
  • Author
    Posts
    • #145286
      Participant
      Topics: 4
      Replies: 3
      Points: 43
      Rank: Member

      I just want to mention that there is a similar thread on this site regarding this topic, but I cant run the code written by Olaf because due to Powershell constrainedlanguage mode turned on in my environment. Most resources on Google is mostly on merging CSV with same header or shared header . I managed to combine both CSV into a CSV but they turned into string.

      My scenario as below:

      File 1 CSV:

      A B C
      1 2 3
      4 5 6
      7 8 9

      File 2 CSV:

      D E F
      10 11 12
      13 14 15
      16 17 18

      desired output CSV:

      A B C D E F
      1 2 3 10 11 12
      4 5 6 13 14 15
      7 8 9 16 17 18

      Any help on this is greatly appreciated.

    • #145325
      Moderator
      Topics: 4
      Replies: 234
      Points: 1,276
      Helping Hand
      Rank: Community Hero
      $csv1 = Get-Content C:\Windows\Temp\csv1.csv
      $csv2 = Get-Content C:\Windows\Temp\csv2.csv
      
      0..($csv2.Count – 1) | %{$csv1[$_],$csv2[$_] -join ‘,’} | Out-File C:\Windows\Temp\csv3.csv
      
      
    • #145427
      Participant
      Topics: 4
      Replies: 11
      Points: 92
      Rank: Member

      Great response Kiran.  That definitely builds what nearownkira45 is asking for.

      I’m curious though, nearownkira45, is that what you wanted?  Do you have two correlating CSVs with equal and matching records?

       

      So if the first CSV were like this:

      FirstName, LastName

      Peter, Griffin

      Lois, Griffin

      Meg, Griffin

      And the second CSV were this:

      VoiceActorFirst, VoiceActorLast

      Seth, MacFarlane

      Alex, Borstein

      Mila, Kunis

      You’d end up with:

      FirstName, LastName, VoiceActorFirst, VoiceActorLast

      Peter, Griffin, Seth, MacFarlane

      Lois, Griffin, Alex, Borstein

      Meg, Griffin, Mila, Kunis

       

      Now maybe this is exactly what you wanted.  Maybe not?  Do let us know.

    • #145437
      Participant
      Topics: 5
      Replies: 2413
      Points: 6,215
      Helping Hand
      Rank: Community MVP

      Hmmm … without trying to be picky … but when I take nearownkira45’s example data and use Kirans code I would end up with something like this:

      A B C,D E F
      1 2 3,10 11 12
      4 5 6,13 14 15
      7 8 9,16 17 18

      I’d expect that nearownkira45 wanted to have something more usable. 😉

      You would need to use the same character to join both data sets like you use inside the CSV files.

    • #145448
      Participant
      Topics: 2
      Replies: 1013
      Points: 2,093
      Helping Hand
      Rank: Community Hero

      Hey Kiran, the OP wanted this output.
      A B C D E F
      1 2 3 10 11 12
      4 5 6 13 14 15
      7 8 9 16 17 18

      Your code does that, layout but adds that additional comma.

       0..($csv2.Count – 1) | 
      %{$csv1[$_],$csv2[$_] -join ','}
      
      A B C,D E F
      1 2 3,10 11 12
      4 5 6,13 14 15
      7 8 9,16 17 18
      

      So, we of course need to drop that out.

      0..($csv2.Count – 1) | 
      %{$csv1[$_],$csv2[$_] -join ' '}
      
      A B C D E F
      1 2 3 10 11 12
      4 5 6 13 14 15
      7 8 9 16 17 18
      
      • #145496
        Moderator
        Topics: 4
        Replies: 234
        Points: 1,276
        Helping Hand
        Rank: Community Hero

        Yes that’s true, usually the CSV values are separated by a comma, so I have used. And yes as you said, space works here. Thank you.

    • #145455
      Participant
      Topics: 4
      Replies: 3
      Points: 43
      Rank: Member

      Great response Kiran. That definitely builds what nearownkira45 is asking for.

      I’m curious though, nearownkira45, is that what you wanted? Do you have two correlating CSVs with equal and matching records?

      So if the first CSV were like this:

      FirstName, LastName

      Peter, Griffin

      Lois, Griffin

      Meg, Griffin

      And the second CSV were this:

      VoiceActorFirst, VoiceActorLast

      Seth, MacFarlane

      Alex, Borstein

      Mila, Kunis

      You’d end up with:

      FirstName, LastName, VoiceActorFirst, VoiceActorLast

      Peter, Griffin, Seth, MacFarlane

      Lois, Griffin, Alex, Borstein

      Meg, Griffin, Mila, Kunis

      Now maybe this is exactly what you wanted. Maybe not? Do let us know.

      The solution contributed by the member here so far (THANKS!) is what i generally wanted 90% of the time.

      but there also times of the 10% of what I wanted is as below ( non correlating records as u mentioned):

      I am outputing some of the RSOP file to powershell console, so for example:

      RSOP XML output CSV1 is :

      $xmldoc.rsop.ComputerResults.ExtensionData.Extension.SecurityOptions | Select “SettingNumber” | export-csv $file1

      RSOP XML output CSV2 is :

      $xmldoc.rsop.ComputerResults.ExtensionData.Extension.SecurityOptions.Display | Select “units” ,”DisplayNumber”,”DisplayString”,”DisplayBoolean”,”Name” | export-csv $file2

      will output like below:

      File1 CSV output:
      A
      1
      2
      3
      4
      5
      6

      File 2 CSV output:

      B C D E F
      7 8 9 10 11
      12 13 14 15 16
      17 18 19 20 21
      22 23 24 25 26

      The intended output will be like below:

      A B C D E F
      1 7 8 9 10 11
      2 12 13 14 15 16
      3 17 18 19 20 21
      4 22 23 24 25 26
      5
      6

      There will be 2 empty space as shown above, and the code should be able to process empty field on CSV2 or CSV1 (CSV 2 in this case)

      wait…

      what does 0.. means ? is it a C# code?

      I do not recognize it from the symbol below:

      A Cheat Sheet for All the *{_(%#$] PowerShell Punctuation

    • #145469
      Senior Moderator
      Topics: 9
      Replies: 1263
      Points: 4,571
      Helping Hand
      Rank: Community Hero

      Something like this.

      $Csv1 = Import-Csv -Path c:\Temp\CSV1.csv
      $Count = 0
      Import-Csv -Path c:\Temp\Csv2.csv | ForEach-Object -Process {
          $_ | Add-Member -MemberType NoteProperty -Name A -Value $Csv1.A[$Count] -Force -PassThru
          $Count++
      }
      

      PS: Not a reliable code.

    • #145476
      Participant
      Topics: 5
      Replies: 2413
      Points: 6,215
      Helping Hand
      Rank: Community MVP

      what does 0.. means ? is it a C# code?

      Nope. That’s the range operator of Powershell. You can read more about operators by running Get-Help about_Operators.

Viewing 7 reply threads
  • The topic ‘merge 2 different CSV with different header into CSV in powershell.’ is closed to new replies.