Here is a second method that does not require ACE drivers.
#<#
#Here is an an example of joining two tables in a DataViewRelation with disconnected data.
# (of couse this is much easier with Linq}
##>
# This method creates a LEFT OUTER JOIN T1(primarykey) += T2(primarykey)
#region Control Helper Functions
function ConvertTo-DataTable {
[OutputType([System.Data.DataTable])]
param (
[ValidateNotNull()]
$InputObject,
[ValidateNotNull()]
[System.Data.DataTable]$Table,
[switch]$RetainColumns,
[switch]$FilterWMIProperties)
if ($null -eq $Table) {
$Table = New-Object System.Data.DataTable
}
if ($InputObject -is [System.Data.DataTable]) {
$Table = $InputObject
} elseif ($InputObject -is [System.Data.DataSet] -and $InputObject.Tables.Count -gt 0) {
$Table = $InputObject.Tables[0]
} else {
if (-not $RetainColumns -or $Table.Columns.Count -eq 0) {
#Clear out the Table Contents
$Table.Clear()
if ($null -eq $InputObject) { return } #Empty Data
$object = $null
#find the first non null value
foreach ($item in $InputObject) {
if ($null -ne $item) {
$object = $item
break
}
}
if ($null -eq $object) { return } #All null then empty
#Get all the properties in order to create the columns
foreach ($prop in $object.PSObject.Get_Properties()) {
if (-not $FilterWMIProperties -or -not $prop.Name.StartsWith('__')) #filter out WMI properties
{
#Get the type from the Definition string
$type = $null
if ($null -ne $prop.Value) {
try { $type = $prop.Value.GetType() } catch { Out-Null }
}
if ($null -ne $type) # -and [System.Type]::GetTypeCode($type) -ne 'Object')
{
[void]$table.Columns.Add($prop.Name, $type)
} else #Type info not found
{
[void]$table.Columns.Add($prop.Name)
}
}
}
if ($object -is [System.Data.DataRow]) {
foreach ($item in $InputObject) {
$Table.Rows.Add($item)
}
return @( ,$Table)
}
} else {
$Table.Rows.Clear()
}
foreach ($item in $InputObject) {
$row = $table.NewRow()
if ($item) {
foreach ($prop in $item.PSObject.Get_Properties()) {
if ($table.Columns.Contains($prop.Name)) {
$row.Item($prop.Name) = $prop.Value
}
}
}
[void]$table.Rows.Add($row)
}
}
return @( ,$Table)
}
#endregion
$csv1 = Import-Csv testjoin1.csv
$dt1 = ConvertTo-DataTable $csv1
$dt1.PrimaryKey = $dt1.Columns[0]
$csv2 = Import-Csv testjoin2.csv
$dt2 = ConvertTo-DataTable $csv2
$dt2.PrimaryKey = $dt2.Columns[0]
$dt3 = [System.Data.DataTable]::new()
$dt3.Merge($dt1)
$dt3.Merge($dt2)
$dt3
$ds3 | Export-Csv ….
Test CSV files:
<# testjoin1.csv
ID,Name,Status
1,joe,enabled
2,sam,enabled
3,chris,enabled
4,susan,disabled
#>
<# testjoin2.csv
ID,Phone
1,111-111-1111
2,222-222-2222
3,333-333-3333
#>
<# results
ID Name Status Phone
-- ---- ------ -----
1 joe enabled 111-111-1111
2 sam enabled 222-222-2222
3 chris enabled 333-333-3333
4 susan disabled
#>
\_(ツ)_/
