Welcome Forums General PowerShell Q&A How to combine 2 csv's

Viewing 3 reply threads
  • Author
    Posts
    • #66916
      Participant
      Topics: 7
      Replies: 10
      Points: 0
      Rank: Member

      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
      Participant
      Topics: 2
      Replies: 1752
      Points: 3,621
      Helping Hand
      Rank: Community Hero

      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
        Participant
        Topics: 7
        Replies: 10
        Points: 0
        Rank: Member

        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
      Participant
      Topics: 2
      Replies: 1752
      Points: 3,621
      Helping Hand
      Rank: Community Hero

      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 
      • #66930
        Participant
        Topics: 7
        Replies: 10
        Points: 0
        Rank: Member

        Thanks it worked!

    • #66945
      Participant
      Topics: 9
      Replies: 431
      Points: 736
      Helping Hand
      Rank: Major Contributor

      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
        Participant
        Topics: 2
        Replies: 1752
        Points: 3,621
        Helping Hand
        Rank: Community Hero

        @Sam,

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

Viewing 3 reply threads
  • The topic ‘How to combine 2 csv's’ is closed to new replies.