adding up values of a CSV column according to applied filter

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 years, 7 months ago.

  • Author
    Posts
  • #9761
    Profile photo of tchintchie
    tchintchie
    Participant

    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

  • #9764
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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 
    
  • #9782
    Profile photo of tchintchie
    tchintchie
    Participant

    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

  • #9888
    Profile photo of tchintchie
    tchintchie
    Participant

    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?

  • #9889
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    

You must be logged in to reply to this topic.