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

1 comment:

  1. Very glad that you posted this. Although its 2-3 years after you posted, I find it useful now. Looking to move away from SQL server but still maintaining the necessity for organized object datasets like csv, accdb (old school but rocks!), table storage (need to get savvy with it in where SQL used to be database choice)