How to combine 2 csv's

This topic contains 6 replies, has 3 voices, and was last updated by  Olaf Soyk 5 months ago.

  • Author
    Posts
  • #66916

    Rishabh Verma
    Participant

    CSV 1 has columns a,b,c and csv 2 has columns d,e.
    I want to append columns d and e in csv 1.
    output would be like
    a,b,c,d,e in csv 1

  • #66919

    Olaf Soyk
    Participant

    You can use Import-CSV to get the content of both CSVs and combine them with to foreach loops in a custom object. Then you're able to output it to another CSV. 😉

    You can use the search function of this forum to search for some examples ... there are many

    • #66924

      Rishabh Verma
      Participant

      i am doing this :

      function Merge-CSVFiles { 
      [cmdletbinding()] 
      param( 
          [string[]]$CSVFiles, 
          [string]$OutputFile = "c:\merged.csv" 
      ) 
      $Output = @(); 
      foreach($CSV in $CSVFiles) { 
          if(Test-Path $CSV) { 
               
              $FileName = [System.IO.Path]::GetFileName($CSV) 
              $temp = Import-CSV -Path $CSV | select *, @{Expression={$FileName};Label="FileName"} 
              $Output += $temp 
       
          } else { 
              Write-Warning "$CSV : No such file found" 
          } 
       
      } 
      $Output | Export-Csv -Path $OutputFile -NoTypeInformation 
      Write-Output "$OutputFile successfully created" 
      }
      
          $file1 = "G:\csv1.txt"
      
          # get content of text files from Directory
          $content = Get-Content "$file1"
      
          # Remove first two lines of header.
          $finalData = $content[2..($content.Count-1)]
      
          # For Creating CSV File in same subFolder
          $finalData | Out-File -FilePath "$file1" -Encoding "UTF8"
      
          $file2 = "G:\csv2.txt"
      
          # get content of text files from Directory
          $content1 = Get-Content "$file2"
      
          # Remove first two lines of header.
          $finalData1 = $content1[1..($content1.Count-1)]
      
          # For Creating CSV File in same subFolder
          $finalData1 | Out-File -FilePath "$file2" -Encoding "UTF8"
      
          $fileInput = Import-Csv -Delimiter "," -Header @("a","b","c") -Path "G:\csv1.txt"
          $fileInput | select a,b,c,d | Export-Csv -Path "$file1" -NoTypeInformation -Force -Encoding "UTF8"
          $fileInput2 = Import-Csv -Delimiter "," -Header @("x","y") -Path "G:\csv2.txt"
          $fileInput2 | select account,ifsc | Export-Csv -Path "$file2" -NoTypeInformation -Force -Encoding "UTF8"
          
       
          Merge-CSVFiles -CSVFiles "$fileInput","$fileInput2" -OutputFile "G:\output.txt"
      
      but its not working!
  • #66927

    Olaf Soyk
    Participant

    hmmmm ... lets say your csv1 (C:\sample\sample1.csv) looks like this:

    a,b,c,d
    aa1,bb1,cc1,dd1
    aa2,bb2,cc2,dd2

    ... and your csv2 (C:\sample\sample2.csv) looks like this:

    d,e,f
    dd1,ee1,ff1
    dd2,ee2,ff2

    then your ps1 could look like this:

    $CSV1 = Import-Csv -Path 'C:\sample\sample1.csv' -Delimiter ','
    $CSV2 = Import-Csv -Path 'C:\sample\sample2.csv' -Delimiter ','
    
    $Result = Foreach($Item1 in $CSV1){
        Foreach($item2 in $CSV2){
            If($Item1.d -eq $item2.d){
                [PSCustomObject]@{
    		        a = $Item1.a
    		        b = $Item1.b
    		        c = $Item1.c
    		        d = $Item1.d
    		        e = $Item2.e
    		        f = $Item2.f
    	        } 
            }
        }
    }
    $Result
    $Result | Export-Csv -Path 'C:\sample\sample2.csv' -NoTypeInformation 
  • #66945

    Sam Boutros
    Participant

    Of concern her is the issue of data fidelity. You're assuming that record 1 of CSV1 corresponds to record 1 of CSV2. If any of the CSVs is sorted, the record order is changed and now the merged data becomes total trash, where you'll end up with record 1 of the combined CSV being aa1,bb1,cc1,dd3,dd4 instead of aa1,bb1,cc1,dd1,ee1 for example.

    We typically guard against that by having one common column is each of the 2 CSVs to be used to join the 2 CSVs (not unlike joining 2 tables in a database)

    • #66948

      Olaf Soyk
      Participant

      @Sam,

      I showed that for him in my code example and he already approved it. Thanks.

You must be logged in to reply to this topic.