PowerShell Script to Import Multiple CSV Files to Pivot Table SCCM Patch Report

1
Import CSV to Pivot Table

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.

Introduction

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.

PowerShell Module

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
Import CSV to Pivot Table
Import CSV to Pivot Table
  • After executing the script new excel file will be created as below with Pivot tale on the 3rd worksheet of excel file.
import CSV to Pivot Table
Import CSV to Pivot Table

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.

Resources

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.