How to combine 2 csv's

PowerShell Help
1

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

2

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. :wink:

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

3

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!
4

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

5

Thanks it worked!

6

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)

7

@Sam,

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