Combine two tables that are similar with two columns different using powershell

Question

I need to join two tables that I have created in Powershell. The problem is that out of the 5 columns they share, two columns are different in each table. How can I join these two tables, and then be able to sort them. I am guessing I would have two do the following:

Example: table1 Columns: a, b, c, d, e

table2 Columns a, x, c, d, z

Do I have to add a new column to each table that is empty but to represent the column it is missing from the other table, so that when I do table1 + table2, they do not drop any data?

Example of the code without sql connection stuff.

    $Command.CommandText = "SELECT job_number, job_desc, permit_number, pieces, rate, postage_total FROM monticello_charges WHERE (insert_date >= '$from') AND (insert_date < '$to') ORDER BY job_number"
   $Command2.CommandText = "SELECT job_number, description, permit_number, pieces, rate, total FROM mailshop_charges WHERE (mailing_date >= '$from') AND (mailing_date < '$to')ORDER BY permit_number, job_number"

   $Reader = $Command.ExecuteReader() 
   $Reader2 = $Command2.ExecuteReader()  

   ###################################
   #Do not add entries that have a [permit_number] of (360) and [rate] less than (0) 
   ###################################
   $results = @()
   while ($Reader.Read())
   {
        $row = [ordered]@{}        
        for ($i = 0; $i -lt $Reader.FieldCount; $i++)
        {
            if($reader.GetValue(2) -eq 360 -and $reader.GetValue(4) -lt 0)
        {
              #pass over it
            }
            else
            {
               $row[$reader.GetName($i)] = $reader.GetValue($i)
            }             
        }
        $results += new-object psobject -property $row 
   }

   ###################################
   # Build the second table and replace 280 permits to (M) 
   ###################################
   $results2 = @()
   while ($Reader2.Read())
   {       
        $row = [ordered]@{}
        for ($i = 0; $i -lt $Reader2.FieldCount; $i++)
        {
            if($i -eq 2 -and $reader2.GetValue($i) -eq 280)
        {
              $row[$reader2.GetName($i)] = "M"
          #$counter++
            }
            else
            {
               $row[$reader2.GetName($i)] = $reader2.GetValue($i)
            }             
        }
        $results2 += new-object psobject -property $row            
   }


   $connection.Close();
   $connection2.Close();

   ###################################
   #Combine the tables and sort,then inject to CSV output file.
   ##################################    
   $finalResult = $results + $results2;   

   #$finalResult = $finalResult | sort-object @{Expression = {$_.permit_number}; Ascending = $true},{$_.job_number}

   $finalResult | sort-object @{Expression = {$_.permit_number}; Ascending = $true},{$_.job_number} | export-csv c:\Users\cpharr\Desktop\out.csv

   Write-Host "Report has been exported to current directoy. Filename:[out.csv]"

Thanks in advance!

Do the table have the same info in the columns that they share? Or do you just want to combine the tables into one big table? Do you want to rename fields (like 'postage_total' to just 'total')? — TheMadTechnician
– TheMadTechnician, Commented Mar 25, 2014 at 18:27

TheMadTechnician · Accepted Answer · 2014-03-25 18:54:46Z

Ok, assuming you don't have linked entries and all the records are separate you could do this:

$T2Keys = $Results2|gm|?{$_.MemberType -match "Property"}|Select -ExpandProperty Name
$T1Keys = $Results|gm|?{$_.MemberType -match "Property"}|Select -ExpandProperty Name
$KeysToAdd = $T2Keys|?{$T1Keys -notcontains $_}
$Results3 = @()
$Results3 += $Results
$KeysToAdd|%{$Results3|Add-Member $_ ""}
$Results3+=$Results2

That queries the properties of the first table to a variable. It does the same to the second table. It finds the properties that are in the second table that aren't in the first one and saves them to a variable. Then it makes an empty array, adds the first table to it, adds the missing fields, then adds the second table to it.

Collectives™ on Stack Overflow

Combine two tables that are similar with two columns different using powershell

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related