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

Hi Again

I am looking for a Powershell script that can pull data from csv file
IP Hostname, Device Groups, IP Address,MAC address, Accessory Name, Device Model, Total Page

Moreover, i need this script to be able calculate data in columns for example total page.


Thx, Michal
michalek19Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

michalek19Author Commented:
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.
0
Chris DentPowerShell DeveloperCommented:
> 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

Open 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 }}

Open 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

Open in new window

Cheers,

Chris
0
michalek19Author Commented:
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
0
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

Chris DentPowerShell DeveloperCommented:
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
0
michalek19Author Commented:
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
0
michalek19Author Commented:
let me upload better example
0
michalek19Author Commented:
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
0
RobSampsonCommented:
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

Open in new window


Regards,

Rob.
0
aikimarkCommented:
@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.
0
RobSampsonCommented:
@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.
0
aikimarkCommented:
I guess MS fixed that issue.  I'm using an older version of PS.
0
michalek19Author Commented:
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
0
aikimarkCommented:
@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

Open in new window

0
michalek19Author Commented:
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

Open in new window

0
aikimarkCommented:
Have you seen an instance where there are zero pages printed?
0
michalek19Author Commented:
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)
0
aikimarkCommented:
Are you trying to generate sub-reports in the columns or separate reports?
0
michalek19Author Commented:
I am for looking for sub-reports. It should be located  in the right columns.
0
RobSampsonCommented:
>> 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

Open in new window


Regards,

Rob.
0
RobSampsonCommented:
For the Device Groups, try this:
$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV
$Software | Group 'Device Groups' | Select Name,Count | Format-Table -AutoSize

Open in new window


Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobSampsonCommented:
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.
0
michalek19Author Commented:
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
0
michalek19Author Commented:
Also, how difficult would be to integrate these short scripts with GUI(page)

Can you  give me an example?
0
aikimarkCommented:
@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.
0
RobSampsonCommented:
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

Open 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.
0
michalek19Author Commented:
Please check new attachment. I am not getting IP address, MAC address, device name.
install-date.png
0
RobSampsonCommented:
Does your CSV file have exactly the same headers as the sample file you posted?
0
michalek19Author Commented:
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
0
RobSampsonCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.