Using PowerShell to add values on matching string and output to a new file-CodePudding

I have a flat file with 3 columns Date, Page, and Page Count.

Each page has 2 versions of that page. A secure page and a non secure version(nlbwc)

I want to be able to take all the entries in the file add the page counts for that specific date, and output the file with a generic page name

So for example, The data below

2021-12-06 00:00:00,secure/WebPage1,1806

2021-12-13 00:00:00,secure/WebPage1,1563

2021-12-06 00:00:00,nlbwc/WebPage1,938

2021-12-13 00:00:00,nlbwc/WebPage1,894

2021-12-06 00:00:00,secure/WebPage2,1533

2021-12-13 00:00:00,secure/WebPage2,1351

2021-12-06 00:00:00,nlbwc/WebPage2,450

2021-12-13 00:00:00,nlbwc/WebPage2,360

Would Yield

2021-12-06 00:00:00,WebPage1,2744 (1806 938)

2021-12-13 00:00:00,WebPage1,2457 (1563 894)

2021-12-06 00:00:00,WebPage2,2744 (1533 450)

2021-12-13 00:00:00,WebPage2,2457 (1351 360)

(The 2 separate values for secure and nlbwc added together based on the date)

This output would be dumped into a csv

What is the best way to accomplish this?

Kindest Regards

CodePudding user response：

Assuming your data file looks like this:

Date,Page,Page Count
2021-12-06 00:00:00,secure/WebPage1,1806
2021-12-13 00:00:00,secure/WebPage1,1563
2021-12-06 00:00:00,nlbwc/WebPage1,938
2021-12-13 00:00:00,nlbwc/WebPage1,894
2021-12-06 00:00:00,secure/WebPage2,1533
2021-12-13 00:00:00,secure/WebPage2,1351
2021-12-06 00:00:00,nlbwc/WebPage2,450
2021-12-13 00:00:00,nlbwc/WebPage2,360

For this you need to use Group-Object twice and loop through:

# if the data does not have headers, provide them with parameter 
#  -Header 'Date','Page','Page Count'
$result = Import-Csv -Path 'D:\Test\data.csv' | Group-Object {($_.Page -split '/')[1]} | ForEach-Object {
    $pageName = $_.Name
    foreach ($pageGroup in ($_.Group | Group-Object Date )) {
        [PsCustomObject]@{
            'Date'       = $pageGroup.Name
            'Page'       = $pageName
            'Page Count' = ($pageGroup.Group | Measure-Object 'Page Count' -Sum).Sum
        }
    }
}

# show on screen
$result | Format-Table -AutoSize

# save to new csv file
$result | Export-Csv -Path 'D:\Test\totals.csv' -NoTypeInformation

Result on screen:

Date                Page     Page Count
----                ----     ----------
2021-12-06 00:00:00 WebPage1       2744
2021-12-13 00:00:00 WebPage1       2457
2021-12-06 00:00:00 WebPage2       1983
2021-12-13 00:00:00 WebPage2       1711

^{P.S>=. You made an error in the desired output with regards to the total Page Count numbers}