Faster way to merge 2 csv files based upon match with first column
<p>Currently,</p>

<p>I'm trying to merge two csv files. The first file, has roughly over 3000 lines. The second file, has roughly over 400,000 lines.</p>

<p>To test this, I'm using these two...</p>

<p>first csv file:</p>

<pre><code>Csv1ColumnOne,Csv1ColumnTwo,Csv1ColumnThree,Csv1ColumnFour
1234,Value1,Value1,Value1
2345,Value2,Value1,Value1
3456,Value1,Value2,Value1
4567,Value1,Value1,Value2
7645,Value3,Value3,Value3
</code></pre>

<p>second csv file:</p>

<pre><code>Csv2ColumnOne,Csv2ColumnTwo,Csv2ColumnThree
1234,abc,Value1
2345,asd,Value1
3456,qwe,Value1
4567,mnb,Value1
</code></pre>

<p>The end result file, should look like this:</p>

<pre><code>"Csv1ColumnOne","Csv1ColumnTwo","Csv1ColumnThree","Csv1ColumnFour","Csv2ColumnOne"
"1234","Value1","Value1","Value1","abc"
"2345","Value2","Value1","Value1","asd"
"3456","Value1","Value2","Value1","qwe"
"4567","Value1","Value1","Value2","mnb"
"7645","Value3","Value3","Value3","Not Found"
</code></pre>

<p>Here is the code I have now (currently working):</p>

<pre><code>Function GetFirstColumnNameFromFile
{
    Param ($CsvFileWithPath)

    $FirstFileFirstColumnTitle = ((Get-Content $CsvFileWithPath -TotalCount 2 | ConvertFrom-Csv).psobject.properties | ForEach-Object {$_.name})[0]
    Write-Output $FirstFileFirstColumnTitle
}

Function CreateMergedFileWithCsv2ColumnOneColumn
{
    Param ($firstColumnFirstFile, $FirstFileFirstColumnTitle, $firstFile, $secondFile, $resultsFile)

    Write-Host "Creating hash table with columns values `"Csv2ColumnOne`" `"Csv2ColumnTwo`" From $secondFile"
    $hashColumnOneColumnTwo2ndFile = @{}
    Import-Csv $secondFile | Where-Object {$firstColumnFirstFile -contains $_.'Csv2ColumnOne'} | ForEach-Object {$hashColumnOneColumnTwo2ndFile[$_.'Csv2ColumnOne'] = $_.Csv2ColumnTwo}
    Write-Host "Complete."

    Write-Host "Creating Merge file with file $firstFile
    and column `"Csv2ColumnTwo`" from file $secondFile"
    Import-Csv $firstFile | Select-Object *, @{n='Csv2ColumnOne'; e={
    if ($hashColumnOneColumnTwo2ndFile.ContainsKey($_.$FirstFileFirstColumnTitle)) {
        $hashColumnOneColumnTwo2ndFile[$_.$FirstFileFirstColumnTitle]
    } Else {
        'Not Found'
    }}} | Export-Csv $resultsFile -NoType
    Write-Host "Complete."
}

Function MatchFirstTwoColumnsTwoFilesAndCombineOtherColumnsOneFile
{
    Param ($firstFile, $secondFile, $resultsFile)

    [string]$FirstFileFirstColumnTitle = GetFirstColumnNameFromFile $firstFile

    $FirstFileFirstColumn = Import-Csv $firstFile | Where-Object {$_.$FirstFileFirstColumnTitle} | Select-Object -ExpandProperty $FirstFileFirstColumnTitle

    CreateMergedFileWithCsv2ColumnOneColumn $FirstFileFirstColumn $FirstFileFirstColumnTitle $firstFile $secondFile $resultsFile
}

Function Main
{
    $firstFile = 'C:\Scripts\Tests\test1.csv'
    $secondFile = 'C:\Scripts\Tests\test2.csv'
    $resultsFile = 'C:\Scripts\Tests\testResults.csv'

    MatchFirstTwoColumnsTwoFilesAndCombineOtherColumnsOneFile $firstFile $secondFile $resultsFile
}

Main
</code></pre>

<p>For the line of:</p>

<pre><code>Import-Csv $secondFile | Where-Object {$firstColumnFirstFile -contains $_.'Csv2ColumnOne'} | ForEach-Object {$hashColumnOneColumnTwo2ndFile[$_.'Csv2ColumnOne'] = $_.Csv2ColumnTwo}
</code></pre>

<p>This takes roughly 30 minutes (per column - 10 columns each). This means it takes roughly 5-7 hours just to merge 3,000 lines between 2 csv files (when I add the code to add the other columns in the final result file). Is there a faster way to create the hash table from the second file with over 400,000 lines?</p>