Join Two CSV Files on a Relation

 

The question of joining two CSV files on a relation has come up often in many blogs.  So far I have not seen anyone use the OleDB provider for text files to do a join of two or more CSV files.  Here is a discussion of how this can be accomplished.

PowerShell:

Function Get-CsvReader2 {
    Param (
        $csvPath,
        $CommandText
    )
    
    $tmpl = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Text;HDR=Yes;FORMAT=Delimited"'
    $connStr = $tmpl -f $csvPath
    
    $csvConnection = New-Object System.Data.OleDb.OleDbConnection($connStr)
    $csvConnection.Open()
    
    $cmd = $csvConnection.CreateCommand()
    $cmd.CommandText = $CommandText
    
    $dt = New-Object System.Data.DataTable
    $adapt = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
    [void]$adapt.Fill($dt)
    $dt
}
$sql = @'
SELECT F1.ID As ID, F1.Name As Name, F1.Status As Status, F2.Status As Status2 
FROM [test1.csv] As F1,[test2.csv] As F2 
WHERE F1.ID = F2.ID
'@

Get-CsvReader2 -csvPath c:\scripts -CommandText $sql