CODEWITHSUNDEEP
excelpowershell
Empty Coder
Empty Coder

Reputation: 589

Display Count of records in excel

I Have the below data in one excel Sheet

  Error Code                         type   object
-Ignored:31 Modified src data       *file   MINOSFIC/UTMNUP10
-Ignored:33 Modified src & tgt data *file   MINOSFIC/UVEGAP10
*Error:  08 Different data          *file   MINOSFIC/VM010P50

I need to count the records based on Error Code and put the data in same Sheet

ErrorCode  Count
Ignored    2
Error      1

I was trying Pivot table, but seems can't use it in existing excel sheet.

Update:

I am able to get the count using below code, but need help to put it in excel sheet in some table or some other way

$Excel = Import-Excel -Path "C:\Verify.xlsx" -WorksheetName "EDH_VFN"

$err = 0
$ign = 0
foreach($line in $Excel )
{
    $line_1 = $line.'Error Code'
    if($line_1 -match "Ignored")
    {
        
       $ign+=1 
    }
    if($line_1 -match "Error")
    {
    
        $err+=1
    }        
}

write-host "Error:"$err
write-host "Ignored:"$ign

Please need help in doing this

Upvotes: 0

Views: 123

Answers (1)

Theo
Theo

Reputation: 61178

If you don't want to do this with Excel function COUNTIFS, I would suggest simply exporting that Excel file to a CSV file which makes things a lot easier in PowerShell.

Example CSV file

Error Code,type,object
-Ignored:31 Modified src data,*file,MINOSFIC/UTMNUP10
-Ignored:33 Modified src & tgt data,*file,MINOSFIC/UVEGAP10
*Error:  08 Different data,*file,MINOSFIC/VM010P50

Once you have this, getting the count values could be done like this:

Import-Csv -Path 'X:\TheExcelToCsvExportedFile.csv' | 
    Group-Object @{Expression = {($_.'Error Code' -split ':')[0].Substring(1)}} | 
    Select-Object @{Name = 'ErrorCode' ; Expression = {$_.Name}},Count

This will output:

ErrorCode  Count
---------- -----
Ignored        2
Error          1

you can simply copy/paste in that your Excel file anywhere you like.

Beware though that this does not comply with the columns you already have there..

Upvotes: 1

Related Questions