<#
.SYNOPSIS
Merges an arbitrary amount of CSV files based on an ID column or several combined ID columns.
Also works on custom PowerShell objects, with the InputObject parameter.

Joakim Svendsen (C) 2014-2017
All rights reserved.

MIT license.

.PARAMETER Id
Shared ID column(s)/header(s).
.PARAMETER Path
CSV files to process.
.PARAMETER InputObject
Custom PowerShell objects to process.
.PARAMETER Delimiter
Optional delimiter that's used if you pass file paths (default is a comma).
.PARAMETER Separator
Optional multi-ID column string separator (default "#Merge-Csv-Separator#").
.PARAMETER AllowDuplicates
Allow duplicate entries (IDs).

.EXAMPLE
ipcsv users.csv | ft -AutoSize

Username Department
-------- ----------
John     IT        
Jane     HR        

PS C:\> ipcsv user-mail.csv | ft -AutoSize

Username Email           
-------- -----           
John     john@example.com
Jane     jane@example.com

PS C:\> Merge-Csv -Path users.csv, user-mail.csv -Id Username | Export-Csv -enc UTF8 merged.csv

PS C:\> ipcsv .\merged.csv | ft -AutoSize

Username Department Email           
-------- ---------- -----           
John     IT         john@example.com
Jane     HR         jane@example.com

.EXAMPLE
Merge-Csv -In (ipcsv .\csv1.csv), (ipcsv csv2.csv), (ipcsv csv3.csv) -Id Username | sort-object username | ft -AutoSize

Merging three files.

WARNING: Duplicate identifying (shared column(s) ID) entry found in CSV data/file 0: user42
WARNING: Identifying column entry 'firstOnly' was not found in all CSV data objects/files. Found in object/file no.: 1
WARNING: Identifying column entry '2only' was not found in all CSV data objects/files. Found in object/file no.: 2
WARNING: Identifying column entry 'user2and3only' was not found in all CSV data objects/files. Found in object/file no.: 2, 3

Username      File1A      File1B      TestID File2A  File2B  TestX      File3  
--------      ------      ------      ------ ------  ------  -----      -----  
2only                                        a       b       c                 
firstOnly     firstOnlyA1 firstOnlyB1 foo                                      
user1         1A1         1B1         same   1A3     2A3     same       same   
user2         2A1         2B1         diff2  2A3     2B3     diff2_2    testC2 
user2and3only                                2and3A2 2and3B2 test2and3X testID 
user3         3A1         3B1         same   3A3     3B3     same       same   
user42        42A1        42B1        same42 testA42 testB42 testX42    testC42

.EXAMPLE
Merge-Csv -Path csvmerge1.csv, csvmerge2.csv, csvmerge3.csv -Id Username, TestID | Sort-Object username | ft -a

Two shared/ID column, three files.

WARNING: Duplicate identifying (shared column(s) ID) entry found in CSV data/file 1: user42, same42
WARNING: Identifying column entry 'user2, diff2' was not found in all CSV data objects/files. Found in object/file no.: 1
WARNING: Identifying column entry 'user2and3only, testID' was not found in all CSV data objects/files. Found in object/file no.: 3
WARNING: Identifying column entry 'user2, testC2' was not found in all CSV data objects/files. Found in object/file no.: 3
WARNING: Identifying column entry '2only, c' was not found in all CSV data objects/files. Found in object/file no.: 2
WARNING: Identifying column entry 'user2and3only, test2and3X' was not found in all CSV data objects/files. Found in object/file no.: 2
WARNING: Identifying column entry 'user2, diff2_2' was not found in all CSV data objects/files. Found in object/file no.: 2
WARNING: Identifying column entry 'firstOnly, foo' was not found in all CSV data objects/files. Found in object/file no.: 1

Username      TestID     File1A      File1B      File2A  File2B 
--------      ------     ------      ------      ------  ------ 
2only         c                                  a       b      
firstOnly     foo        firstOnlyA1 firstOnlyB1                
user1         same       1A1         1B1         1A3     2A3    
user2         diff2      2A1         2B1                        
user2         diff2_2                            2A3     2B3    
user2         testC2                                            
user2and3only testID                                            
user2and3only test2and3X                         2and3A2 2and3B2
user3         same       3A1         3B1         3A3     3B3    
user42        same42     42A1        42B1        testA42 testB42

#>
function Merge-Csv {
    [CmdletBinding(
        DefaultParameterSetName='Files'
    )]
    param(
        # Shared ID column(s)/header(s).
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string[]] $Id,
        # CSV files to process.
        [Parameter(ParameterSetName='Files',Mandatory=$true)][ValidateScript({Test-Path $_ -PathType Leaf})][string[]] $Path,
        # Custom PowerShell objects to process.
        [Parameter(ParameterSetName='Objects',Mandatory=$true)][psobject[]] $InputObject,
        # Optional delimiter that's used if you pass file paths (default is a comma).
        [Parameter(ParameterSetName='Files')][string] $Delimiter = ',',
        # Optional multi-ID column string separator (default "#Merge-Csv-Separator#").
        [string] $Separator = '#Merge-Csv-Separator#',
        # Allow duplicate entries (IDs).
        [switch] $AllowDuplicates
    )
    # v1.5 - Changed Sort to Sort-Object to support "Linux changes" in PSv6.
    # v1.4 - 2016-09-16 - Added support for handling duplicate IDs.
    [psobject[]] $CsvObjects = @()
    if ($PSCmdlet.ParameterSetName -eq 'Files') {
        $CsvObjects = foreach ($File in $Path) {
            ,@(Import-Csv -Delimiter $Delimiter -Path $File)
        }
    }
    else {
        $CsvObjects = $InputObject
    }
    $Headers = @()
    foreach ($Csv in $CsvObjects) {
        $Headers += , @($Csv | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name)
    }
    $Counter = 0
    foreach ($h in $Headers) {
        $Counter++
        foreach ($Column in $Id) {
            if ($h -notcontains $Column) {
                Write-Error "Headers in object/file $Counter don't include $Column. Exiting."
                return
            }
        }
    }
    $HeadersFlatNoShared = @($Headers | ForEach { $_ } | Where { $Id -notcontains $_ })
    if ($HeadersFlatNoShared.Count -ne @($HeadersFlatNoShared | Sort-Object -Unique).Count) {
        Write-Error "Some headers are shared. Are you just looking for '@(ipcsv csv1) + @(ipcsv csv2) | Export-Csv ...'?`nTo remove duplicate (between the files to merge) headers from a CSV file, Import-Csv it, pass it to Select-Object, and omit the duplicate header(s)/column(s).`nExiting."
        return
    }
    $SharedColumnHashes = @()
    $SharedColumnCount = $Id.Count
    $Counter = 0
    foreach ($Csv in $CsvObjects) {
        $SharedColumnHashes += @{}
        $Csv | ForEach {
            $CurrentID = $(for ($i = 0; $i -lt $SharedColumnCount; $i++) {
                $_ | Select -ExpandProperty $Id[$i] -EA SilentlyContinue
            }) -join $Separator
            if (-not $SharedColumnHashes[$Counter].ContainsKey($CurrentID)) {
                $SharedColumnHashes[$Counter].Add($CurrentID, @($_ | Select -Property $Headers[$Counter]))
            }
            else {
                if ($AllowDuplicates) {
                    $SharedColumnHashes[$Counter].$CurrentID += $_ | Select -Property $Headers[$Counter]
                }
                else {
                    Write-Warning ("Duplicate identifying (shared column(s) ID) entry found in CSV data/file $($Counter+1): " + ($CurrentID -replace [regex]::Escape($Separator), ', '))
                }
            }
        }
        $Counter++
    }
    $Result = @{}
    $NotFound = @{}
    foreach ($Counter in 0..($SharedColumnHashes.Count-1)) {
        foreach ($InnerCounter in (0..($SharedColumnHashes.Count-1) | Where-Object { $_ -ne $Counter })) {
            foreach ($Key in $SharedColumnHashes[$Counter].Keys) {
                Write-Verbose "Key: $Key, Counter: $Counter, InnerCounter: $InnerCounter"
                $Obj = New-Object -TypeName PSObject
                if ($SharedColumnHashes[$InnerCounter].ContainsKey($Key)) {
                    foreach ($Header in $Headers[$InnerCounter] | Where { $Id -notcontains $_ }) {
                        Add-Member -InputObject $Obj -MemberType NoteProperty -Name $Header -Value ($SharedColumnHashes[$InnerCounter].$Key | Select $Header)
                    }
                }
                else {
                    foreach ($Header in $Headers[$Counter]) {
                        if ($Id -notcontains $Header) {
                            Add-Member -InputObject $Obj -MemberType NoteProperty -Name $Header -Value ($SharedColumnHashes[$Counter].$Key | Select $Header)
                        }
                    }
                    if (-not $NotFound.ContainsKey($Key)) {
                        $NotFound.Add($Key, @($Counter))
                    }
                    else {
                        $NotFound[$Key] += $Counter
                    }
                }
                if (-not $Result.ContainsKey($Key)) {
                    $Result.$Key = $Obj
                }
                else {
                    foreach ($Property in @($Obj | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name)) {
                        if (-not ($Result.$Key | Get-Member -MemberType NoteProperty -Name $Property)) {
                            Add-Member -InputObject $Result.$Key -MemberType NoteProperty -Name $Property -Value $Obj.$Property #-EA SilentlyContinue
                        }
                    }
                }
                
            }
        }
    }
    if ($NotFound) {
        foreach ($Key in $NotFound.Keys) {
            Write-Warning "Identifying column entry '$($Key -replace [regex]::Escape($Separator), ', ')' was not found in all CSV data objects/files. Found in object/file no.: $(
                if ($NotFound.$Key) { ($NotFound.$Key | ForEach { ([int]$_)+1 } | Sort-Object -Unique) -join ', '}
                elseif ($CsvObjects.Count -eq 2) { '1' }
                else { 'none' }
                )"
        }
    }
    #$Global:Result = $Result
    $Counter = 0
    [hashtable[]] $SharedHeadersNoDuplicate = $Id | ForEach {
        @{n="$($Id[$Counter])";e=[scriptblock]::Create("(`$_.Name -split ([regex]::Escape('$Separator')))[$Counter]")}
        $Counter++
    }
    [hashtable[]] $HeaderPropertiesNoDuplicate = $HeadersFlatNoShared | ForEach {
        @{n=$_.ToString(); e=[scriptblock]::Create("`$_.Value.'$_' | Select -ExpandProperty '$_'")}
    }
    [hashtable[]] $SharedHeaders = @(foreach ($h in $Id) {
        @{n="$($Id[$Counter])";e=[scriptblock]::Create("`$Obj.Name.'$(($h -split [regex]::Escape($Separator))[$Counter])'")}
        $Counter++
    })
    [hashtable[]] $HeaderProperties = @(foreach ($h in $HeadersFlatNoShared) {
        @{n=$h.ToString(); e=[scriptblock]::Create("`$Obj.Value.'$h'")}
    })
    [int] $HeadersFlatNoSharedCount = $HeadersFlatNoShared.Count
    # Return results.
    if (-not $AllowDuplicates) {
        $Result.GetEnumerator() | Select -Property ($SharedHeadersNoDuplicate + $HeaderPropertiesNoDuplicate)
    }
    else {
        $Result.GetEnumerator() | foreach { #Select -Property ($SharedHeaders + $HeaderProperties) | foreach {
            # Latching on support for duplicate objects. Insanely inefficient.
            # Variable for the count of duplicates we find. Initialize to 1 for each array of PSobjects for each ID.
            $MaxDuplicateCount = 1
            foreach ($Title in $_.Value | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name) {
                $Count = @($_.Value.$Title).Count
                # find max count for this instance (if at all higher than 1)
                # duplicates are processed in the order they occur
                if ($MaxDuplicateCount -lt $Count) {
                    $MaxDuplicateCount = $Count
                }
            }
            Write-Verbose "Max duplicate count: $MaxDuplicateCount"
            foreach ($i in 0..($MaxDuplicateCount-1)) {
                # Add ID(s) once to each object.
                $Obj = $null
                $Obj = New-Object -TypeName PSObject
                foreach ($TempID in $Id) {
                    Add-Member -InputObject $Obj -MemberType NoteProperty -Name $TempID -Value $_.Name
                }
                foreach ($NumHeader in 0..($HeadersFlatNoSharedCount-1)) {
                    try {
                        $Value = ($_.Value.($HeadersFlatNoShared[$NumHeader]))[$i] | Select -ExpandProperty $HeadersFlatNoShared[$NumHeader]
                    }
                    catch {
                        Write-Verbose "Caught out of bounds in array."
                        $Value = '' | Select -Property $HeadersFlatNoShared[$NumHeader]
                    }
                    Add-Member -InputObject $Obj -MemberType NoteProperty -Name $HeadersFlatNoShared[$NumHeader] -Value $Value
                }
                $Obj | Select -Property ($Id + $HeadersFlatNoShared)
            }
        }
    }
}