使用PowerShell从.csv中的数据创建数据透视表

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 :)

我有一个.csv文件,我想从一列中获取所有数据并使用powershell生成一个数据透视表。这个专栏有很多重复,我想知道每个项目有多少。附件是我想通过powershell而不是手动在excel中完成的图像。第一个图像是示例数据,第二个图像是我想从数据中生成的图像。谢谢你的帮助 :)

Data Result

2 个解决方案

#1

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

Doug Finke制作了一个名为Import-Excel的精彩PowerShell模块,你可以在这里找到它。

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

如果您使用的是PowerShell v5或更高版本,则可以像下面这样安装它:

Install-Module ImportExcel -scope CurrentUser

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

这个模块有一些很棒的Pivot功能,这是一个例子:

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

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.

我只是嘲笑了一个带有一些操作系统计数的文件并通过该代码运行它。这是结果。

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.

关于此工具的一个重要注意事项:,每次运行Export-Excel时都必须提供新的文件名,否则会抛出一条丑陋的错误消息。脚本删除文件,或每次更改文件名以避免它。

#2

I can't see those images, but it should be something like

我看不到那些图像,但应该是这样的

$list = Import-Csv -Path P:\ath\to\file.csv -Delimiter ";"
$duplicates = ($list | Group-Object -Property <ColumName>).Where({ $_.Count -gt 1 })

Replace the with the name of the Column you want to search for duplicates.

将要替换为要搜索重复项的列的名称。

The 2 main functions you need are Import-Csv & Group-Object

您需要的两个主要功能是Import-Csv和Group-Object

Consider, that Import-Csv creates PSCustomObjects in an array and the first rows in your colums are their properties.

考虑一下,Import-Csv在数组中创建PSCustomObjects,并且colums中的第一行是它们的属性。

So if you have a csv file like:

所以,如果你有一个csv文件,如:

Column1;Column2;Column3
foo;bar;baz
baz;bar:foo

You'll have an Array of PSCustomObjects withs Column1,Column2,Column3 as properties. If your csv-file has no Column Name in the first line, you can use Import-Csv with the argument -Header ColumName.

您将拥有一个PSCustomObjects数组,其中Column1,Column2,Column3作为属性。如果您的csv文件在第一行中没有列名,则可以使用带有参数-Header ColumName的Import-Csv。

All in all, what you want to do is a simple job, just read about the functions I linked and you'll do it :)

总而言之,你想要做的只是一个简单的工作,只需阅读我链接的功能,你就可以做到:)

Greetz Eldo.O

#1