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

In this post, you will get the PowerShell script to Import CSV to Pivot Table. Being an SCCM Patch management administrator, our responsibility is to deliver good patch compliance.

This activity is also important to create reports to track our overall patch compliance.

As part of this activity, I manually worked on a weekly SCCM patch report to get the patch compliance in our environment.

Introduction

We use a security tool to get the raw data for a total number of scanned clients and noncompliance clients.

Patch My PC

I have to import this data of multiple regions into different excel files, create a pivot table, calculate the compliance percentage, and finally send the report.

After a couple of months, I was bored of doing the same thing and started to check over the internet if I could automate this regular activity.

PowerShell Module

In search of how to write a simple script for the same, I came 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 a few doubts with the GitHub community  https://github.com/dfinke/ImportExcel/issues/559. Big shout out to #pkarunkar and Doug for their help.

Adaptiva

Import CSV to Pivot Table

Finally, I can write a simple script to automate my regular activity. All the file names are hardcoded in this script as these weekly reports will not get changed dynamically.

Copy the raw data into a folder and modify the script to match your input\output file names.

  • Run the script. It will take around a minute to import CSV files into an excel file in different worksheets, create a pivot table, and 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 1
  • After executing the script, a new excel file will be created as below with Pivot tale on the 3rd worksheet of the excel file.
import CSV to Pivot Table
Import CSV to Pivot Table 2

Results & Download Script

I 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

2 thoughts on “PowerShell Script to Import Multiple CSV Files to Pivot Table SCCM Patch Report”

  1. Could you please share the format, column details for the RAW data files for “All Clients.csv” and “Non-Compliant Clients.csv” file, Also if you use any query to fetch that dats then kindly share, So i can use this solution to implement the patching automation in my envirnoment.

    Reply

Leave a Comment

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