Author Posts

March 22, 2017 at 8:42 am

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

March 22, 2017 at 8:58 am

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

March 22, 2017 at 9:32 am

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!

March 22, 2017 at 10:06 am

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 

March 22, 2017 at 12:30 pm

Thanks it worked!

March 22, 2017 at 1:17 pm

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)

March 22, 2017 at 1:36 pm

@Sam,

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