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'

Answer 1

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.

Answer 2

As it appears that there several used cases to discern unrelated property keys instead of merging them, I have added a new feature. The -Unify (formally/alias -Mergeparameter) to the Join-Object cmdlet, now accepts a one or two dynamic keys to distinguish unrelated column pairs in a join.

The -Unify (alias-Merge) parameter defines how to unify the left and right object with respect to the unrelated common properties. The common properties can discerned (<String>[,<String>]) or merged (<ScriptBlock>). By default the unrelated common properties wil be merged using the expression: {$LeftOrVoid.$_, $RightOrVoid.$_}

<String>[,<String>] If the value is not a ScriptBlock, it is presumed a string array with one or two items defining the left and right key format. If the item includes an asterisks (*), the asterisks will be replaced with the property name otherwise the item will be used to prefix the property name.

Note: A consecutive number will be automatically added to a common property name if is already used.

...

Example:

$Csv1 = ConvertFrom-Csv 'Server,Info
server1,item1
server1,item1'

$Csv2 = ConvertFrom-Csv 'Server,Info
server2,item2
server2,item2'

$Csv1 | Join $Csv2 -Unify *1, *2

Result:

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