Merging two CSVs and then re-ordering columns on output

Question

I have this first CSV:

Server,Info  
server1,item1
server1,item1

and this 2nd CSV:

Server,Info  
server2,item2
server2,item2

And I am trying to get this output:

Server,Server,Info,Info
server1,server2,item1,item2
server1,server2,item1,item2

As you see, the problem is that the headers of the 2 CSV have the same names, which cause a problem if I parse them into objects and loop over keys.

So I am trying to merge them then reordering them as strings, but my mind can't figure how to do it in the last for loop:

$file1 = Get-Content ".\Powershell test\A.csv"
$file2 = Get-Content ".\Powershell test\B.csv"

$content = for ($i = 0; $i -lt $file1.Length; $i++) {
    '{0},{1}' -f $file1[$i].Trim(), $file2[$i].Trim()
}

$content | Out-File  ".\Powershell test\merged.csv"

$firstFileParsed = Import-Csv -Path ".\Powershell test\B.csv"
$secondFileParsed = Import-Csv -Path ".\Powershell test\B.csv"

$secondFilePath =  ".\Powershell test\B.csv"
$contentOf2ndFile = Get-Content $secondFilePath

$csvColumnNames = (Get-Content '.\Powershell test\B.csv' |
                  Select-Object -First 1).Split(",")

$newColumns = @()

foreach($header in $csvColumnNames) {
    $newColumns += $header
}

$newColumns = $newColumns -join ","
$contentOf2ndFile[0] = $newColumns
$contentOf2ndFile | Out-File ".\Powershell test\temp.csv"

$tempObject = Import-Csv -Path ".\Powershell test\temp.csv"
$tempFile = Get-Content ".\Powershell test\temp.csv"

$array = @()
$tempArr = @()

for ($i = 0; $i -lt $file1.Length; $i++) {
    $tempArr1 = $file1[$i] -split ","
    $tempArr2 = $tempFile[$i] -split ","

    for ($j = 0; $j -lt $tempArr1.Length; $j++) {
        $tempArr += $tempArr1[$j] +  "," + $tempArr2[$j]
        $tempArr
    }

    $array += $tempArr
}

$array | Out-File '.\Powershell test\merged.csv'

Please take a step back and describe the actual problem you're trying to solve instead of what you perceive as the solution. Why do you need two columns with identical header name? — Ansgar Wiechers
I'm with @AnsgarWiechers on this one -- having duplicate header names isn't really useful as you can't refer to them and know what you'll get. Surely headers like Server1,Info1,Server2,Info2 would be more useful? — henrycarteruk
A CSV file with duplicate headers is simply not a valide CSV format. If you Import-Csv such a CSV file, you will get a Import-Csv : The member "Server" is already present. — iRon
You might use the default output of this Join-Object cmdlet: $File1 | Join $File2 where the columns are merged in an array: {server1, server2} {item1, item2}. Than you can access your result ($Result = $File1 | Join $File2) like: $Result[0].Server[1] ` — iRon
@iRon To my knowledge the CSV format specification doesn't forbid duplicate column titles, so technically the format is valid CSV. It's just not very useful in about every practical application, and particularly not in PowerShell, where the records are represented as the properties of custom objects (whose names must be unique). — Ansgar Wiechers

marsze marsze · Accepted Answer · 2019-02-26T11:11:24

What you suggest is not very useful or even valid CSV. IMHO only two results would make sense:

This:

Server1,Info1,Server2,Info2
server1,item1,server2,item2
server1,item1,server2,item2

Or this:

Server,Info
server1,item1
server1,item1
server2,item2
server2,item2

First approach:

$csv1 = Import-Csv ".\Powershell test\A.csv"
$csv2 = Import-Csv ".\Powershell test\B.csv"

$merged = for($i = 0; $i -lt $csv1.Count; $i++) {
    $new = new-object psobject
    $entry1 = $csv1[$i]
    $entry1 | Get-Member -Type NoteProperty | foreach {
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_.Name + "1") -Value $entry1.($_.Name)
    }
    $entry2 = $csv2[$i]
    $entry2 | Get-Member -Type NoteProperty | foreach {
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_.Name + "2") -Value $entry2.($_.Name)
    }
    $new
}

$merged | Export-Csv ".\Powershell test\merged.csv"

Second approach:

$csv1 = Import-Csv ".\Powershell test\A.csv"
$csv2 = Import-Csv ".\Powershell test\B.csv"

$merged = $csv1 + $csv2

$merged | Export-Csv ".\Powershell test\merged.csv"

UPDATE

If you want exactly your output (and the files are certain to have the same headers and line count), you could use unique headers first, and then simply rename them later:

$csv1 = Import-Csv ".\Powershell test\A.csv"
$csv2 = Import-Csv ".\Powershell test\B.csv"
$merged = for($i = 0; $i -lt $csv1.Count; $i++) {
    $new = New-Object PSObject
    ("Server", "Info") | foreach {
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_ + "1") -Value $csv1[$i].$_
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_ + "2") -Value $csv2[$i].$_
    }
    $new
}
$header = $true
$merged | ConvertTo-Csv -NoTypeInformation | foreach {
    if ($header) {
        $header = $false
        # remove the numbers from the headers
        $_ -replace "\d", ""
    }
    else { $_ }
} | Out-File ".\Powershell test\merged.csv"

Explanations:

Count is available in Powershell for all collections, and safer than Length which is a property of arrays only. But in this case, both should work.

In the loop, a new empty object is created (with New-Object) and then populated by adding the members of the parsed CSV objects (with Add-Member). A counter is added to the property names to make them unique.

The collection of these objects ($merged) is then converted to CSV, the numbers in the header line removed, and everything saved to file.

Merging two CSVs and then re-ordering columns on output

2 Answers