CODEWITHSUNDEEP
arrayspowershell
Roxx
Roxx

Reputation: 3986

Powershell: calculate data in 2d Array

I am trying to create bar chart from excel data by powershell.

In excel file i have list of computers with hardware model. I am trying to get the total count of devices group by hardware model.

e.g.
HP- 50
Dell - 100
IBM -10

This is the code i have written so far.

#Declare the file path and sheet name
$file = "D:\HealthCheck_test.xlsx"
$sheetName = "DC01"
#Create an instance of Excel.Application and Open Excel file
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file)
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible=$false
#$red = 1;
#$orange = 1;
#Count max row
$rowMax = ($sheet.UsedRange.Rows).count
#Declare the starting positions
$rowHB,$colHB = 5,14 #pie
$rowModel,$colModel = 5,32 #bar
$rowHWInv,$colHWInv = 5,16 #Pie
$rowSite,$colSite = 5,40 #Bar
$rowOS,$colOS = 5,41 #Bar
#array to get 2d data for bar chart

$arr=@{}
$arr["model"] = @{}
$arr["model"]["type"] = @{} 
#loop to get values and store it
for ($i=1; $i -le $rowMax-1; $i++)
{
$HB = $sheet.Cells.Item($rowHB+$i,$colHB).text
$Model = $sheet.Cells.Item($rowModel+$i,$colModel).text
$HWInv = $sheet.Cells.Item($rowHWInv+$i,$colHWInv).text
$Site = $sheet.Cells.Item($rowSite+$i,$colSite).text
$OS = $sheet.Cells.Item($rowOS+$i,$colOS).text
#$i
Write-Host ("I am reading data on row: "+$i)

[int] $hb = $HB
 $arr["model"]["type"] = $Model

[int] $HWInv = $HWInv
 $Site = $Site
 $OS = $OS

        if($hb -ge 31){
            $red = $red + 1
        }
        if($hb -ge 14 -and $hb -le 30){
            $orange = $orange + 1
        }

}

$objExcel.quit()

Write-Host ("Total devices more than 30 days old: "+$red)
Write-Host ("Total devices 14-30 days old: "+$orange)

Above code is working fine but it is not returning data for model type.

I thought i need to use 2d array to store the hardware type data. So, all data will be store in array after that i can get the data by model type. Can anybody advise me on this. I don't know how can i do that?

Name    Days Since PWD  Model   Manufacturer
desktop 40  OptiPlex 790    Dell Inc.
lappy   15      
test    209     
test2   51      
test5   27  OptiPlex XE2    Dell Inc.

Upvotes: 0

Views: 54

Answers (1)

Paul
Paul

Reputation: 5861

First off with @{} you are creating a hashtable, not an array. Second the index of an array is allways numeric so your approach will fail even if you declare $arr correctly. Third in your code example i dont even see the need to use anything more than a simple array since you only store a single value (maybe your example is not complete or i am missing something?)

Ok after reading your comment we can simplify this.

Before your loop create an empty array:

$arr= @()

In your loop add the model value to the array:

$arr+=$model

After your loop you can count the number of occurances for each manufacturer:

($arr | where {$_ -like "*HP*"}).count

For example will give you the number of times the model value contains "HP", you might have to adjust the String you are looking for but this should basically work

Upvotes: 1

Related Questions