Powershell: calculate data in 2d Array

Question

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.

@jisaak I have added the sample data. I want to get count of machine by model number. — Roxx
– Roxx, Commented Mar 31, 2016 at 10:18
after seeing your data wouldnt it be better to read out the manufacturer instead of the model if that is basically what you are looking for? — Paul
– Paul, Commented Mar 31, 2016 at 10:30
It doesn't matter about manufacturer/model. Problem is how can i get the count. So, i will use that in bar chart. — Roxx
– Roxx, Commented Mar 31, 2016 at 10:36

Paul · Accepted Answer · 2016-03-31 10:24:43Z

1

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

edited Mar 31, 2016 at 10:24

answered Mar 31, 2016 at 9:04

Paul

5,9412 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Roxx Over a year ago

Thanks for your answer. I will try and let you know. Let me exlain you about the data. In excel file data is under a column (Model) in that column model details are mentioned (HP, Dell, IBM etc). I want to get the total number of devices by model type. HP would hav 10 devices, IBM would have 15 devices. Let me know if there are still confusion.

Roxx Over a year ago

One more thing there are no type value in excel file. There are only models field and i need to get the count of devices with model name. Sample data has been added in the question.

Roxx Over a year ago

How can i show all models with count. Because if i use ($arr | where {$_ -like "HP"}).count this then it will only show HP data and i can't update data manualy. Model names are in excel file i need to show them with total count

Paul Over a year ago

@CalculatingMachine you will just have to count it for every manufacturer. For HP ($arr | where {$_ -like "*HP*"}).count, for IBM ($arr | where {$_ -like "*IBM*"}).count and so on

Roxx Over a year ago

ah, then it won't work because i want to automate it and i can't just simply write down all the models type. Because dell, hp, ibm are sample data. actual data have more than 500 models.

|

Collectives™ on Stack Overflow

Powershell: calculate data in 2d Array

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related