使用PowerShell将两个不同的CSV文件加入对象 [英] Join-Object two different csv files using PowerShell

问题描述

第一个.csv文件是基于客户端名称的KB每月备份大小.第二个.csv文件是基于客户端名称的下一个月度备份大小(以KB为单位).

The first .csv file is an monthly backup size in KB on based client name. The second .csv file is an next monthly backup size in KB on based client name.

它在A列中列出了所有的客户端名称.B列具有相应的客户端策略名称，最后一列的备份大小以KB为单位(即-487402463).

It lists all the Client Name in column A. Column B has the corresponding policy name of client and last column backup size in KB (i.e. - 487402463).

如果客户端大小之间的差异(1638838488-1238838488 = 0.37 in TB)大于0.10 TB，则结果将以TB大小吐出到csv文件中，如下所示.

If the difference between client size (1638838488 - 1238838488 = 0.37 in TB ) is greater than 0.10 TB , the results will be spit out in TB size to a csv file like below.

此外，客户端可能与多个策略名称相关.

Also , a client may be related multiple policy name.

我的问题是:我也想添加一些东西.

My question is : I want to add something too.

• 下个月的备份大小可能会减少，例如主机名15，Company_Policy_11.
• 此外，hostname55，Company_Policy_XXX的策略名称也可能不同.
• 主机名XX，Company_Policy_XXX，0和主机名XX，Company_Policy_XXX，41806794，它可以是重复的客户端和策略名称.如果这在CSV2中不存在，那么我想显示为负数(-0.14)，如下所示.或者也可以存在于CSV2主机名ZZ，Company_Policy_XXX中.
• 最后，它可能位于CSV2中，例如主机名SS，Company_Policy_XXX. 我使用了Join-Object模块. https://github.com/ili101/Join-Object

• Backup size may decrease in the next month such as hostname15,Company_Policy_11.
• Also , hostname55,Company_Policy_XXX may have different policy name.
• hostnameXX,Company_Policy_XXX,0 and hostnameXX,Company_Policy_XXX,41806794 it may be duplicate client and policy name. if this does not exist in CSV2 then I want to display as negative (-0.14) like below. Or may be exist in CSV2 hostnameZZ,Company_Policy_XXX as well.
• Lastly just it may be in CSV2 such as hostnameSS,Company_Policy_XXX. I used the Join-Object module. https://github.com/ili101/Join-Object

示例CSVFile1.csv

Example CSVFile1.csv

Client Name,Policy Name,KB Size
hostname1,Company_Policy,487402463
hostname2,Company_Policy,227850336
hostname3,Company_Policy_11,8360960
hostname4,Company_Policy_11,1238838488
hostname15,Company_Policy_11,3238838488
hostname1,Company_Policy_55,521423110
hostname10,Company_Policy,28508975
hostname3,Company_Policy_66,295925
hostname5,Company_Policy_22,82001824
hostname2,Company_Policy_33,26176885
hostnameXX,Company_Policy_XXX,0
hostnameXX,Company_Policy_XXX,141806794
hostnameYY,Company_Policy_XXX,121806794
hostname55,Company_Policy_XXX,41806794
hostnameZZ,Company_Policy_XXX,0
hostnameZZ,Company_Policy_XXX,141806794

示例CSVFile2.csv

Example CSVFile2.csv

Client Name,Policy Name,KB Size
hostname1,Company_Policy,487402555
hostname2,Company_Policy,227850666
hostname3,Company_Policy_11,8361200
hostname4,Company_Policy_11,1638838488
hostname1,Company_Policy_55,621423110
hostname15,Company_Policy_11,1238838488
hostname10,Company_Policy,28908975
hostname3,Company_Policy_66,295928
hostname5,Company_Policy_22,92001824
hostname2,Company_Policy_33,36176885
hostname22,Company_Policy,291768854
hostname23,Company_Policy,291768854
hostname55,Company_Policy_BBB,191806794
hostnameZZ,Company_Policy_XXX,0
hostnameZZ,Company_Policy_XXX,291806794
hostnameSS,Company_Policy_XXX,0
hostnameSS,Company_Policy_XXX,291806794

所需的输出:

Client Name,Policy Name,TB Size
hostname4,Company_Policy_11,0.37
hostname22,Company_Policy,0.27
hostname23,Company_Policy,0.27
hostnameYY,Company_Policy_XXX,-0.12
hostnameXX,Company_Policy_XXX,-0.14
hostname15,Company_Policy_11,-2
hostname55,Company_Policy_BBB,0.15
hostnameZZ,Company_Policy_XXX,0.15
hostnameSS,Company_Policy_XXX,0.29

到目前为止，这是我的脚本:

Here is my script so far :

$CSV2 | FullJoin $CSV1 `
    -On 'Client Name','Policy Name' `
    -Property 'Client Name',
              'Policy Name', 
              @{'TB Size' = {[math]::Round(($Left.'KB Size' - $Right.'KB Size') * 1KB / 1TB, 2)}} | 
    Where-Object  {[math]::Abs($_.'TB Size') -gt 0.10} | Export-Csv C:\Toolbox\DataReport.csv -NoTypeInformation

推荐答案

您可以这样做类似于以下内容.假设您要从CSV2值中减去CSV1值.

You could so something similar to the following. This assumes you want to subtract CSV1 values from CSV2 values.

# Read CSV files and make CSV1 sizes negative. Makes summing totals simpler.
$1 = Import-Csv CSVFile1.csv | Foreach-Object { $_.'KB Size' = -$_.'KB Size'; $_ }
$2 = Import-Csv CSVFile2.csv

# Calculated Properties to be used with Select-Object
$CalculatedProperties = @{n='Client Name';e={$_.Group.'Client Name' | Get-Unique}},
                        @{n='Policy Name';e={$_.Group.'Policy Name' | Get-Unique}},
                        @{n='TB Size';e={[math]::Round(($_.Group.'KB Size' | Measure -Sum).Sum*1KB/1TB,2)}}

# Grouping objects based on unique client and policy name combinations
$1 + $2 | Group-Object 'Client Name','Policy Name' |
    Select-object $CalculatedProperties |
        Where {[math]::Abs($_.'TB Size') -gt 0.10}

这篇关于使用PowerShell将两个不同的CSV文件加入对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！