CSV导入文件的Powershell分组 [英] Powershell Grouping of csv import file

问题描述

希望我只是缺少一些简单的东西...

Hopefully I'm just missing something that is simple...

我有一个与此类似的csv文件:

I have a csv file similar to this :

Employee ID, Entry Date, Product Code,Amount Due
0001,20/11/2017,A001,10
0001,20/11/2017,Q003,13
0001,20/11/2017,H001,8
0002,20/11/2017,P003,12
0002,20/11/2017,A001,7

我想要的输出与此类似:

and what I want as an output is similar to this :

0001;<some header text>;200171120
A001;10
Q003;13
H001;8
0002;<some header text>;200171120
P003;12
A001;7

因此每个详细信息部分均按与其相关的员工ID进行分组

So that each detail section is grouped by the Employee ID it relates to

在使用import-csv后，我尝试用管道传输组对象(员工ID")，但是我无法使其正常工作

I have tried piping the group-object ("Employee ID") after using an import-csv ... but I can't get it to work correctly

任何帮助，不胜感激！

推荐答案

我认为这可以满足您的需求:

I think this does what you need:

$CSV | Group-Object 'Employee ID' | ForEach-Object {

    $EntryDate = (Get-Date ($_.Group.'Entry Date' | Select -First 1) -F 'yyyyMMdd')

    "{0};<some header text>;{1}" -f $_.Name, $EntryDate

    ForEach ($Item in $_.Group) {
        "{0},{1}" -f $Item.'Product Code',$Item.'Amount Due'
    }
}

说明:

• 使用Group-Object将结果按Employee ID分组，然后使用ForEach-Object遍历集合.
• 从组中的第一个条目获取条目日期，使用Get-Date将其转换为日期对象，然后将其格式化为年/月/日(此部分假定您不在乎是否有其他/不同的内容)日期).
• 使用{0}，{1}作为通过-f传递的变量的占位符，输出所需的标头字符串(有多种方法可以执行此操作，但这似乎最适合您的情况).
• 使用ForEach遍历Group属性，该属性包含分组的项目.再次使用字符串替换以所需的格式输出产品代码和应付金额字段.

• Uses Group-Object to group the results by Employee ID and then ForEach-Object to iterate through the collection.
• Gets the Entry date from the first entry in the group, converts it to a date object with Get-Date and then formats it as year/month/day (this part assumes you don't care if there are other/differing dates in the collection).
• Outputs the header string you wanted, using {0},{1} as placeholders for the variables passed via -f (there are multiple ways to do this but this seemed tidiest for your scenario).
• Uses ForEach to iterate through the Group property, which contains the grouped items. Again using string replacement to output the product code and amount due fields in the format you desired.

这篇关于CSV导入文件的Powershell分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！