使用PowerShell将两个相似的表与两个不同的列合并 [英] Combine two tables that are similar with two columns different using powershell

问题描述

我需要加入我在Powershell中创建的两个表.问题在于它们共享的5列中，每个表中的两列都不同.如何连接这两个表，然后对其进行排序.我猜我将要执行以下两个操作:

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:

示例: 表格1 列:a，b，c，d，e

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

表2 列a，x，c，d，z

table2 Columns a, x, c, d, z

我是否必须向每个空的表中添加一个新列，但要代表另一个表中缺少的列，以便在执行table1 + table2时，它们不会删除任何数据?

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?

没有sql连接内容的代码示例.

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]"

提前谢谢！

推荐答案

好吧，假设您没有链接条目并且所有记录都是单独的，则可以执行以下操作:

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.

这篇关于使用PowerShell将两个相似的表与两个不同的列合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！