adding up values of a CSV column according to applied filter

tchintchie · August 26, 2013, 5:41pm

Hello experts!

I have two csv files with multiple columns each (only two which are of importance) and have filtered and grouped the MID column (Machine-ID). Now each MID has a corresponding GB Storage Column whos values I want to add up for each group. I hope this was not too confusing. HereÂ´s the example:

MID | GB Storage
A00001FE8 | 660
A00001FE8_SQL | 75
B0001458 | 1230

and so on… so IÂ´ve grouped and filtered the MIDs already so A00001FE8 and A00001FE8_SQL are in one group. However now I also need to add up their Storage values. In this example 735 GB. How do I accomplish that?

My code so far:

#converting the .xls files to .csv for import
Function makeCSV($Excelfilename, $CSVfilename){
$xlCSV=6
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.displayalerts=$False
$Workbook = $Excel.Workbooks.Open($ExcelFileName)
$Workbook.SaveAs($CSVfilename,$xlCSV)
$Excel.Quit()
If(ps excel){
kill -name excel
}
}

#call function to convert desired files to .csv
#param1 = filename and path to .xls file
#param2 = filename and path to .csv file that will be created

makeCSV “H:\Documents\Excel\cebrA-Cloud_Server_2.3_Skriptvorlage.xlsx” “H:\Documents\Excel\cebrA-Cloud_Server_2.3_Skriptvorlage.csv”
makeCSV “H:\Documents\Excel\2013_Q2_Kostenaufteilung_ISS-Server_skriptvorlage.xlsx” “H:\Documents\Excel\2013_Q2_Kostenaufteilung_ISS-Server_skriptvorlage.csv”

#import first csv file and multiply the GB TSM Storage values by 100
$csv = Import-Csv “H:\Documents\Excel\cebrA-Cloud_Server_2.3_Skriptvorlage.csv” -Delimiter “;”

$TSM = foreach($csvObj in $csv ){

$csvObj.'TSM-Storage'=100*($csvObj.'TSM-Storage').replace(',','.')

}

#import second csv file and divide the TSM Storage by 2 and by 1000

$csv2 = Import-Csv “H:\Documents\Excel\2013_Q2_Kostenaufteilung_ISS-Server_skriptvorlage.csv” -Delimiter “;”

$TSM2 = foreach($csvObj2 in $csv2 ){

$csvObj2.'TSM-Storage'=($csvObj2.'TSM-Storage').replace(',','.')/2/1000

}

$group = $csv | group {$.MID.substring(0,9)} #since these MIDs have the pattern A0000648_SQL and so on and I only need the first 8 characters for grouping
$group2= $csv2 | group{$.MID.substring(5,8)} #since these MIDs have the pattern M048_A48910EE_TDP and I only need the ID in between the “_”

$filePathes=“c:\test2.csv”,“c:\test.csv”
$summary=foreach ($filePath in $filePathes){
Import-Csv -Path $filePath | group {$.MID.substring(0,9)} | foreach {
$sum=($.Group.“GB TSM Storage” | Measure-Object -Sum).Sum
New-Object PSObject -Property @{“GB Sum”=$sum;MID=$_.Name}
}
}
the above code for the $summary was suggested by another forum member. However in my case (because the MID of one file needs to be trimmed at (0,9) and the other one at (5,8)) I get this output after some modifying (aside from some error messages):
$filePaths=“H:\Documents\Excel\2013_Q2_Kostenaufteilung_ISS-Server_skriptvorlage.csv”,“H:\Documents\Excel\cebrA-Cloud_Server_2.3_Skriptvorlage.csv”

$summary=foreach ($filePath in $filePaths){
Import-Csv -Path $filePath -Delimiter “;” | group {$group, $group2} | foreach {
$sum=($.Group.$TSM | Measure-Object -Sum).Sum
New-Object PSObject -Property @{“GB Sum”=$sum;MID=$.Name}
}
}
$summary | Sort-Object MID | ft -AutoSize

MID GB Sum

System.Object System.Object 0
System.Object System.Object 0

rob-simmers · August 27, 2013, 12:58am

I’ll give this a whack. I’m curious to see if someone else shares another solution, but I think this is basically what you are trying to accomplish:

#Empty object to emulate data
$object = @()
 
$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=("A00001FE8"); 
            'GB Storage'=(660);})

$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=("A00001FE8_SQL"); 
            'GB Storage'=(75);})

$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=(" B0001458"); 
            'GB Storage'=(1230);})

#Add a column with a friendly group name
$object | foreach {
    $GroupName = $_.MID
    #If there is a _, use it as delimiter and get groupname
    if ($GroupName.contains("_")) {$GroupName = $GroupName.Split("_")[0]}
    $_ | add-member Noteproperty -Name GroupName -Value $GroupName
}


#Items grouped by GroupName
$results = $object | Group-Object -Property GroupName

#Count Name      Group                                                                                                            
#----- ----      -----                                                                                                            
#    2 A00001FE8 {@{MID=A00001FE8; GB Storage=660; GroupName=A00001FE8}, @{MID=A00001FE8_SQL; GB Storage=75; GroupName=A00001FE8}}
#    1  B0001458 {@{MID= B0001458; GB Storage=1230; GroupName= B0001458}}     


#Empty Summary
$summary = @()

$results | foreach {
        # Get the sum of the storage
        $groupResults = $_.Group | Measure-Object -Property 'GB Storage' -Sum
        # Empty array for all MIDs you are getting sum for
        $MID = @()
        #For every item in the group, get the MID name and generate a name of all MID's in storage sum
        $_.Group | foreach { $MID += $_.MID  }
        #Generate a summary with all Mids calculated, the MID group and total storage
        $summary += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=($MID -join ","); 
            'MID_Group'=($_.Name);
            'Total GB Storage'=($groupResults.Sum);})
}

$summary | ft -AutoSize

#MID_Group Total GB Storage MID                    
#--------- ---------------- ---                    
#A00001FE8              735 A00001FE8,A00001FE8_SQL
# B0001458             1230  B0001458

tchintchie · August 27, 2013, 7:05pm

Rob, you are a genius!! thanks a ton! this is exactly what I was looking for. Needed some adapting but now it works fine!! I hope I can figure out the rest of the task by myself…well, if not IÂ´ll be back…
thanks again, you are awesome!!

kind regards
tchintchie

tchintchie · September 2, 2013, 7:45pm

Hey all! ItÂ´s me again. I have successfully modified RobÂ´s above script (thanks again!!!) to work with my files. Now I need to compare the results of both files to each other like so:

`#compare the two outputs and display only those with a difference of over 10%

$cebralist = $summary_cebra | Sort-Object MID_Group

$isslist = $summary_iss | Sort-Object MID_Group
#empty results-array

$results = @()

foreach ($i in $cebralist)

{

foreach ($j in $isslist)

{

if ($i.MID_Group -like $j.MID_Group -and ((((1-($j.'Total TSM Storage'/$i.'Total TSM Storage'))-lt -0.01) -or ((1-($j.'Total TSM Storage'/$i.'Total TSM Storage'))-gt 0.01)) -or (($j.'Total TSM Storage' -eq '0') -and ($i.'Total TSM Storage' -ne '0'))))

{

$results += $i

}

}

}

`

As you can see itÂ´s checking wether TSM Storage of file A is more than 10% larger/smaller than TSM Storage in file B. This works great, however some MIDÂ´s have the value 0 or nothing and those arenÂ´t displayed at all. My boss asked me to regard them as well because sometimes itÂ´s 0 in file A but has some value in file B… so I added the quite ugly if-condition at the end. The result still stays the same (without any zero values). I have clearly messed up that condition block but donÂ´t know where the error lies. ItÂ´s still working for the 10% query though…

any ideas?

rob-simmers · September 2, 2013, 11:19pm

Your first issue, which is assigning a zero to an empty item could be handled by checking length or [string]::IsNullOrEmpty and then setting that current object property to 0:

PS C:\Windows\system32> $object = @()
 
$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=("A00001FE8"); 
            'GB Storage'=(660);})

$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=("A00001FE8_SQL"); 
            'GB Storage'=(75);})

$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=(" B0001458"); 
            'GB Storage'=(1230);})

$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=(" B0001458"); 
            'GB Storage'=("");})

$object += New-Object â€“TypeName PSObject -Property `
        (@{'MID'=(" B0001458"); 
            'GB Storage'=(230);})

PS C:\Windows\system32> $object

MID                                                                                                                                                                                 GB Storage
---                                                                                                                                                                                 ----------
A00001FE8                                                                                                                                                                                  660
A00001FE8_SQL                                                                                                                                                                               75
 B0001458                                                                                                                                                                                 1230
 B0001458                                                                                                                                                                                     
 B0001458                                                                                                                                                                                  230

PS C:\Windows\system32> $object | foreach{ [string]::IsNullOrEmpty($_.'GB Storage'); If( [string]::IsNullOrEmpty($_.'GB Storage')) {$_.'GB Storage' = 0}}
False
False
False
True
False

# OR - Reset object and test length

PS C:\Windows\system32> $object | foreach{$_.'GB Storage'.Length; If($_.'GB Storage'.Length -lt 1) {$_.'GB Storage' = 0}}
1
1
1
0
1

#Both methods produce:

PS C:\Windows\system32> $object

MID                                                                                                                                                                                 GB Storage
---                                                                                                                                                                                 ----------
A00001FE8                                                                                                                                                                                  660
A00001FE8_SQL                                                                                                                                                                               75
 B0001458                                                                                                                                                                                 1230
 B0001458                                                                                                                                                                                    0
 B0001458                                                                                                                                                                                  230

So, you would have to loop through both the group prior to your math logic. My question is on your actual math. There are a ton of examples of storage math. The values you’re referencing are confusing since the field names are the same, but here is a basic disk example:

PS C:\Windows\system32> gwmi Win32_LogicalDisk -Filter "DriveType = 2 And Size > 0"

DeviceID     : H:
DriveType    : 2
ProviderName : 
FreeSpace    : 294912
Size         : 131006464
VolumeName   : 

PS C:\Windows\system32> $disks = gwmi Win32_LogicalDisk -Filter "DriveType = 2 And Size > 0"

PS C:\Windows\system32> $disks | foreach { $Used = $_.Size - $_.FreeSpace; $percentUsed = ($_.Freespace/$_.Size)*100;"{0} bytes of the disk is used and {1:p0} is free" -f $used, $percentUsed}
130711552 bytes of the disk is used and 23 % is free

Topic		Replies	Views
Sum rows from csv PowerShell Help	5	299	January 18, 2022
Using Group-object to sum more than one column (property) PowerShell Help	1	167	August 30, 2018
CSV column - Total value PowerShell Help	4	119	June 7, 2017
Group and Sum CSV with unknown columns. PowerShell Help	2	173	January 17, 2019
Grouping CSV data PowerShell Help	1	123	August 12, 2015

adding up values of a CSV column according to applied filter

Related Topics