In this post, you will get the PowerShell script to Import CSV to Pivot Table. Being an SCCM Patch management administrator it is our responsibility to deliver good patch compliance across the the domain. This activity is also important to create reports to track our overall patch compliance.
As part of this activity, I was working on a weekly SCCM patch report manually to get the patch compliance in our environment.
We use a security tool to get the raw data for total number of scanned clients and non compliance clients, I have to import this data of multiple regions into different excel files, create pivot table, calculate the compliance percentage, finally send the report.
After couple of months I was bored of doing the same thing and started to check over the internet if I can automate this regular activity.
In search of how to write a simple script for the same I had come across a PowerShell module developed by Doug Finke @dfinke I felt it is really simple and easy to use.
I had done little homework and clarified few doubts with github community https://github.com/dfinke/ImportExcel/issues/559 big shout out to #pkarunkar and Doug for their help.
Import CSV to Pivot Table
Finally I am able to write a simple script to automate my regular activity. In this script all the file names are hard coded as these weekly reports will not get changed dynamically. Copy the raw data in to a folder modify the script to match your input\output file names.
- Run the script it will just take around a minute to import CSV files into an excel file different worksheets and will create a pivot table, calculate the compliance percentage.
- Files are placed under a folder as below before running the script
- After executing the script new excel file will be created as below with Pivot tale on the 3rd worksheet of excel file.
Results & Download Script
I just have two CSV files to import to excel so my pivot table is in 3rd worksheet. Right click on the Compliance Percentage clients number to convert the number into a % representation.
Please find the actual code of the script from Github https://raw.githubusercontent.com/jampaniharish/OnlineScripts/master/WeeklyPatchComplianceReport.ps1 There could be other scripts over internet which can best fit this requirement, I hope someone will find this helpful.
- SCCM Patch Management is Enough for Vulnerability Management?
- SCCM Third-Party Updates Step by Step Background Process Guide Post