Search code examples
excelpowershellcsvpivot-tablepowercli

Create Pivot Table from data in .csv using PowerShell

I have a .csv file, I want to take all the data from one column and produce a pivot table with it using powershell. This column has many duplicates in and I want to see how many of each item there are. Attached are images of what i would like to be done through powershell instead of manually in excel. First image is example data, and the second image is what I want to be produced from the data. Thanks for any help :)

Data Result

Solution

  • Doug Finke made a wonderful PowerShell module called Import-Excel which you can get here.

    If you're on PowerShell v5 or higher, you can install it like so:

    Install-Module ImportExcel -scope CurrentUser

    This module features some awesome Pivot functions, here's an example:

    Get-Service |
    Export-Excel "c:\temp\test2.xlsx" `
        -Show `
        -IncludePivotTable `
        -IncludePivotChart `
        -PivotRows status `
        -PivotData @{status='count'}

    enter image description here

    I don't have access to your source data, but you could run something like this:

    Import-CSV C:\yourPath\YourFile.csv | Export-Excel "c:\temp\OSReport.xlsx" `
    -Show `
    -IncludePivotTable `
    -IncludePivotChart `
    -PivotRows 'Operating System' `
    -PivotData @{'Operating System'='count'}

    I just mocked up a file with some OS counts and ran it through that code. Here's the result.

    If you have this many Windows 98 machines...seek new employment or give me a call to come help you fix it!

    I hope this gets you headed in the right direction!

    An important note about this tool:, you must provide a new file name every time you run Export-Excel or it throws an ugly error message. Either script the deletion of the file, or change the file name every time to avoid it.