Wednesday, October 28, 2015

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.


Function Get-CsvReader2 {
    Param (
    $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)
    $cmd = $csvConnection.CreateCommand()
    $cmd.CommandText = $CommandText
    $dt = New-Object System.Data.DataTable
    $adapt = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
$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 

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