Integrating Microsoft Excel with PowerShell: Reading Data, calculate data in the columns

I am attaching spread sheet example.

I can generate spread sheet but then i would like embed excel spreadsheet in webpage and run on the background powershell to pull numbers by tab names

So, if i would like to find out how many printers are on the generated spread i will select total printer count
If i would like to find out how many printer per device groups (EUR, AMERICA) i would select total printer per device groups.Same think with total page count. (total 0 pages: XXX ; total 1-50 pages: XXX; total 51-100 pages: XXX and so on.

Instead of going to spread sheet and looking for the data manually I would like to select these tabs on webpage that would run script against excel file to pull correct info.

> I am looking for a Powershell script that can pull data from csv file

PowerShell can natively read CSV files, you don't need a script for that:

Import-Csv SomeFile.csv

Select allOpen in new window

Given that it's a scripting language you can perform pretty much whatever calculations you can imagine. Of course, you'll have to define what you hope to have for a more realistic example.

However, a quick example would be this which attempts to ping each of the hosts and constructs a new field to hold the result:

Import-Csv FileName.csv | Select-Object *, @{n='PingResponds';e={ Test-Connection $_.'IP Hostname' -Quiet -Count 1 }}

Select allOpen in new window

That can be exported to another CSV file as follows:

Import-Csv FileName.csv | Select-Object *, @{n='PingResponds';e={ Test-Connection $_.'IP Hostname' -Quiet -Count 1 }} | Export-Csv FIleName-Updated.csv -NoTypeInformation

Select allOpen in new window

Cheers,

Chris

Would you be willing to give me an example of Powershell script that  can calculate column K and provide summary of

How many
total 0 pages:  
total 1-50 pages:
total 51-100 pages:
total 100-1000 pages:
total 1000-5000 pages:

Then calculate again column K but this time to display total pages printed by region (Column E - Device Groups)

I will appreciate, Thx, MikeMonthly-Report.csv

So in theory you can have that, but where does the data come from? Is that the Total column at the moment?

Perhaps you can give an example of what you're trying to get to?

Chris

I am generating that data ftom HP WJA.
I want to quickly calculate get total number by running powerdhell script against csv file.

This file is located on a server so it needs to be pull from remote location.

I hope this helps.

Basically i am looking for print matrics

let me upload better example

Please check that new attachment.

Perhaps you can give an example of what you're trying to get to?

In the column K  called "Total"  there are many numbers. I would like to quickly pull quickly

How many
total 0 pages:  
total 1-50 pages:
total 51-100 pages:
total 100-1000 pages:
total 1000-5000 pages:sing powershell script

In the column E called "Device Group" i would like to generate all printer by group and and sort them from A to Z then and provide result like

AME:200
APAC:400
EU: 50


In the column G called "Device Mode;" i would like to generate report that would provide quantity result

Xerox WorkCentre 7832 v1 Multifunction System :150
Xerox WorkCentre 7435: 50
HP LaserJet 7320: 50
Brother MFC-8370DN: 23
HP LaserJet P2032n: 78
HP Officejet Pro X276dw MFP:  23
HP LaserJet 1320: 87

In the column J called "Installed Date" i would like to generate report that would provide
Year of installation and host name.

Basically, i would like to get results from each column.

Regards to GUI integration that would be great integration for each and clean read.

Script that can be executed from GUI page to get result.

Please let me know if this is clear.

Thx, Mike
Devinv.csv

Hi, if the input file is a CSV as you have posted, I have assumed that the top 12 lines are to be skipped.  This script would then import that CSV into Powershell, group it, and display the count.

$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12 | ConvertFrom-CSV
$Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending | Format-Table -AutoSize

Select allOpen in new window

Regards,

Rob.

@Rob

I found that I needed to prepend some bogus column name, like "NullCol" to the first non-skipped line in order for that to work.

@aikimark

I didn't have to, it worked with the sample file attached for me.  I did get a warning though that stated a H1 column header had been automatically put on.

Rob.

I guess MS fixed that issue.  I'm using an older version of PS.

it doesn't work this for me

I tested on PS GUI script editor 3.8.0.129  and PS ver.2

PS C:\Temp\Scripts> .\Metrics.ps1
ConvertFrom-Csv : Cannot process argument because the value of argument "name"
is invalid. Change the value of the "name" argument and run the operation again
.
At C:\Temp\Scripts\Metrics.ps1:2 char:71
+ $Software = Get-Content $InputFile | Select -Skip 12 | ConvertFrom-CSV <<<<
    + CategoryInfo          : InvalidArgument: (:) [ConvertFrom-Csv], PSArgume
   ntException
    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.ConvertFr
   omCsvCommand

@michalek19
Try this work-around:

$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV
$Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending | Format-Table -AutoSize

Select allOpen in new window

Thx, That works. The result is on the below.  

What about this , please help

In the column K  called "Total"  there are many numbers. I would like to quickly pull quickly

How many
total 0 pages:  
total 1-50 pages:
total 51-100 pages:
total 100-1000 pages:
total 1000-5000 pages:

In the column E called "Device Group" i would like to generate all printer by group and and sort them from A to Z then and provide result like

AME:200
APAC:400
EU: 50

In the column J called "Installed Date" i would like to generate report that would provide
Year of installation and host name.

-----------------------------------------------------------------------------------
Result
-------------------------------------------------------------------------------
PS C:\temp\Scripts> .\Metrics.ps1

Name                                                             Count
----                                                             -----
Xerox WorkCentre 7732 v 71.  1.  0 Multifunction System            102
HP LaserJet 7220                                                    70
HP Color LaserJet 7700                                              58
HP LaserJet 7320                                                    52
HP LaserJet 7300                                                    24
HP Officejet Pro X276dw MFP                                         23
HP Color LaserJet 7620                                              23
HP Color LaserJet 7600                                              16
HP LaserJet 7200                                                    16
Xerox WorkCentre 7832 v1 Multifunction System                       14
HP LaserJet 1320                                                    14
HP LaserJet P2012 Series                                            14
HP LaserJet 7020                                                    10
FUJI XEROX ApeosPort-IV C3372  v 82. 70.  0 Multifunction System     7
Xerox WorkCentre 7872 v1 Multifunction System                        6
FUJI XEROX ApeosPort-IV C3372  v 87.  2.  0 Multifunction System     6
Dell Color Laser 2110cn                                              5
HP LaserJet 700 color MFP M772                                       5
HP LaserJet 700 color M721nw                                         5
Xerox WorkCentre 7272 v1 Multifunction System                        3
FUJI XEROX DocuCentre-IV C3372 v 87.  8.  0 Multifunction System     3
HP Color LaserJet 3600                                               3
HP LaserJet 7000                                                     3
HP LaserJet 7100                                                     3
HP Color LaserJet M621                                               3
HP LaserJet 8120                                                     3
FUJI XEROX DocuCentre-IV C3372 v 82. 23.  0 Multifunction System     3
FUJI XEROX DocuPrint C1190 FS; Net 12.27,ESS 200907031207            3

Select allOpen in new window

Have you seen an instance where there are zero pages printed?

You right.  I didn't see zero pages printed  but this will indicate that this printer is not in use or is not utilized (probability)

Are you trying to generate sub-reports in the columns or separate reports?

I am for looking for sub-reports. It should be located  in the right columns.

>> I guess MS fixed that issue.  I'm using an older version of PS.
I am using PS 3.0, so it appears to handle that automatically now.

Well, I really don't know whether this is the best way to create the subreport, but I come up with this, which is pretty flexible, and shows you the count of page groups.

$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
}
$Totals = $null
$arrRanges.GetEnumerator() | ForEach {
    $Name = $_.Name
    If ($_.Value -eq '**') {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -eq '$($_.Value)'} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    } Else {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -ne '**' -and [int]`$_.Total -in $($_.Value)} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    }
    Invoke-Expression $Expression
}
$Totals | Group Name | Select Name,Count | Format-Table -AutoSize

Select allOpen in new window

Regards,

Rob.

For the Install Date report, you have a lot in column J that are <Not Support> or <No Value>, but they DO have an Interval.  Which column did you want to report on?

Rob.

For the Install Date report,
i would like to have report with dates, host names, ip address and device names
Any data which is not supported or no value should be also reported so i will know that we have brands that not supported. so i can addressed this with my managment

Also, how difficult would be to integrate these short scripts with GUI(page)

Can you  give me an example?

@michalek19

integrate these short scripts with GUI(page)

Since you have a solution to this question, you should post a new question about the GUI.

I've had a play with the install date report....hard to say what you're after, but this is what I have come up with:

$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV

$Software | Group {
    If ("($_.'Install Date')") {
        $Date = $_.'Install Date'.Split('/')
        If (($Date.Length)-1 -eq 0) {
            $Date
        } else {
            $Date[2].Split(' ')[0]
        }
    }
} | Sort Name | 
    Select Count,Name,@{n='Hostname';e={($_.Group.'IP Hostname' | Get-Unique) -join ","}},
    @{n='IP Address';e={($_.Group.'IP Address' | Get-Unique) -join ","}},
    @{n='Device Model';e={($_.Group.'Device Model' | Get-Unique) -join ","}} | Out-GridView

Select allOpen in new window

For a GUI solution, it depends entirely on what you want on the GUI, and what actions it is supposed to perform.
With this last code, I have added Out-GridView, so you get a GUI with the results.

Rob.

Please check new attachment. I am not getting IP address, MAC address, device name.
install-date.png

Does your CSV file have exactly the same headers as the sample file you posted?

That works. Great

So looking on all of these script can we put them together as one script and export output to CSV file or powershell gui

Maybe we should start a new question for these additions.  If you are asking whether one script could create three different reports, sure, that can be done.  I am not sure whether you could three Out-GridView commands, but we can give that a shot.

Rob.