Powershell -用分组项替换.csv中的行

Question

我有下面的场景。包含订单的.csv文件。包含多个项目的订单位于不同的行中。我将按命令id和sku对行进行分组，以便在导出到.csv之前对某些列执行和

下面有下面的代码，它执行分组和和，并可以写出结果以分离.csv

我需要做的是通过用加总行替换原始行来追加原始.csv文件。任何帮助都非常感谢

例如：

示例

​

$output = @()

# Import .CSV and group on amazon-order-id and sku
# Filter group to only give lines with multiple occurances of each sku per order

Import-Csv D:\Exports\Test\AMAZON\*.csv | Group-Object amazon-order-id, sku | Where-Object {$_.Count -gt 1} | 

# Loop through group object. Take first line of each group and place in $new variable
# Using dot notation, sum required columns and add rows to $output variable

ForEach-Object {

$new = $_.Group[0].psobject.Copy()

$new.'quantity-shipped' = ($_.Group | Measure-Object quantity-shipped -Sum).Sum
$new.'item-price' = ($_.Group | Measure-Object item-price -Sum).Sum
$new.'item-tax' = ($_.Group | Measure-Object item-tax -Sum).Sum
$new.'shipping-price' = ($_.Group | Measure-Object shipping-price -Sum).Sum
$new.'shipping-tax' = ($_.Group | Measure-Object shipping-tax -Sum).Sum
$new.'gift-wrap-price' = ($_.Group | Measure-Object gift-wrap-price -Sum).Sum
$new.'gift-wrap-tax' = ($_.Group | Measure-Object gift-wrap-tax -Sum).Sum
$new.'item-promotion-discount' = ($_.Group | Measure-Object item-promotion-discount -Sum).Sum
$new.'ship-promotion-discount' = ($_.Group | Measure-Object ship-promotion-discount -Sum).Sum

$output += $new

}

#Select all group members and export to .csv file

$output | select * | Export-Csv D:\Exports\Test\AMAZON\Import_Me.csv -not

​

Answer 1

我假设您希望将公共行分组为单个行，并且只剩下那些分组的单行和不常见的行。

$CSVFiles = Get-ChildItem -Path D:\Exports\Test\AMAZON -File -Filter '*.csv' | Where Extension -eq '.csv'
$output = foreach ($csv in $CSVFiles) {
    $csvOutput = Import-Csv $csv.FullName
    $group = $csvOutput | Group-Object amazon-order-id, sku
    $group | Where Count -gt 1 | Foreach-Object {
        $_.Group[0].'quantity-shipped' = ($_.Group | Measure-Object quantity-shipped -Sum).Sum
        $_.Group[0].'item-price' = ($_.Group | Measure-Object item-price -Sum).Sum
        $_.Group[0].'item-tax' = ($_.Group | Measure-Object item-tax -Sum).Sum
        $_.Group[0].'shipping-price' = ($_.Group | Measure-Object shipping-price -Sum).Sum
        $_.Group[0].'shipping-tax' = ($_.Group | Measure-Object shipping-tax -Sum).Sum
        $_.Group[0].'gift-wrap-price' = ($_.Group | Measure-Object gift-wrap-price -Sum).Sum
        $_.Group[0].'gift-wrap-tax' = ($_.Group | Measure-Object gift-wrap-tax -Sum).Sum
        $_.Group[0].'item-promotion-discount' = ($_.Group | Measure-Object item-promotion-discount -Sum).Sum
        $_.Group[0].'ship-promotion-discount' = ($_.Group | Measure-Object ship-promotion-discount -Sum).Sum
    }
    $group | Foreach-Object { $_.Group[0] } # Output to variable only
    $group | Foreach-Object { $_.Group[0] } | Export-Csv $csv.FullName -NoType
}
$output

解释：

$CSVFiles是CSV文件的集合。我们需要能够一个接一个地锁定他们，知道要更新哪个文件。使用foreach循环将每个文件作为目标，当前文件为$csv。

因为$csvOutput是CSV文件作为PSCustomObjects数组的内容，所以我们可以用Foreach-Object更新每个对象，并且它将反映在$csvOutput中。

$group被分配给每个CSV文件的Group-Object输出。这里使用一个变量将分组操作最小化为每个文件一次。首先，在有多个匹配的每个组上执行修改。使用您的逻辑，分组中的第一个对象将被修改。Foreach-Object用于遍历所有分组。

一旦完成了对一个CSV文件的所有修改，就会输出$group (这包括多个组和单个组)，并且使用另一个Foreach-Object只选择分组中的第一个对象($_.Group[0])，这也适用于单个对象分组。该输出被传递到Export-Csv以更新适当的文件。

$output列出修改后的所有CSV内容。