Powershell Export to csv - dynamic daterange-CodePudding

I have a csv with user and datetime

Users DHMS          
----- ----          
A     22/12/21 05:02:00
B     22/12/21 05:10:00
C     22/12/21 06:30:00
D     23/12/21 12:30:00
A     23/12/21 12:35:00
B     23/12/21 12:45:00
C     26/12/21 10:32:00
D     28/12/21 11:15:00
A     29/12/21 14:17:00
B     29/12/21 14:25:00

Is there a simple way to extract from each row a new csv with the rows beetween -30 minutes / 30 minutes

I try to do this

$culture = [Globalization.CultureInfo]::InvariantCulture
$Data = Import-Csv '.\test.csv' 

$Data | ForEach-Object {

    $DHMS = [DateTime]::ParseExact($($_.DHMS), 'dd/MM/yy HH:m:s', $culture)

    
$DHMSmoins = $DHMS.AddMinutes(-30)
$DHMSplus =  $DHMS.AddMinutes(30)


$path = ".\"   $($_.Users)  "\time.csv"


$Data | where { $DHMS  -ge $DHMSmoins  -and $DHMS -le $DHMSplus }
$Data | Export-Csv $path

}

but problems :

1/ daterange export is bad

2/ there is several users so only the last user's row is exported. Is there a way to concatenate the differents csv in one

Hope to be more clear for Tomalak

In A folder a csv with

A     22/12/21 05:02:00
B     22/12/21 05:10:00
D     23/12/21 12:30:00
A     23/12/21 12:35:00
B     23/12/21 12:45:00
A     29/12/21 14:17:00
B     29/12/21 14:25:00

in B folder a csv with

A     22/12/21 05:02:00
B     22/12/21 05:10:00
D     23/12/21 12:30:00
A     23/12/21 12:35:00
B     23/12/21 12:45:00
A     29/12/21 14:17:00
B     29/12/21 14:25:00

In C folder a csv with

C     22/12/21 06:30:00
C     26/12/21 10:32:00

In D folder a csv with

D     23/12/21 12:30:00
A     23/12/21 12:35:00
B     23/12/21 12:45:00
D     28/12/21 11:15:00

CodePudding user response：

Task as per the comments:

one folder per user ("grouped by user") with a CSV file that contains all the events of that user, plus half an hour of "context" before and after each (independently of which user they belong to).

Here's how I would do it:

$rows = Import-Csv '.\test.csv' -Delimiter ";" | foreach {
    [pscustomobject]@{
        date = [DateTime]::ParseExact($_.DHMS, 'dd/MM/yy HH:m:s', [Globalization.CultureInfo]::InvariantCulture)
        row = $_
    }
}

$rows | group { $_.row.Users } | foreach {
    $user = $_.Name
    $filename = ".\$user\time.csv"

    # get context events (n.b.: this will contain duplicates!)
    $events = $_.Group | foreach {
        $start = $_.date.AddMinutes(-30)
        $until = $_.date.AddMinutes(30)
        ($rows | where { $_.date -ge $start -and $_.date -le $until }).row
    }

    # consolidate duplicates
    $index = @{}
    $uniqueEvents = foreach ($event in $events) {
        if (-not $index.ContainsKey($event)) {
            $index[$event] = $true
            $event
        }
    }

    Write-Host $filename -ForegroundColor Cyan
    Write-Host ($uniqueEvents | ConvertTo-Csv -Delimiter ";" -NoTypeInformation) -Separator "`r`n"  -ForegroundColor DarkCyan
    # $uniqueEvents | Export-Csv $filename -Delimiter ";" -NoTypeInformation
}

which prints the desired output for your sample data:

.\A\time.csv
"Users";"DHMS"
"A";"22/12/21 05:02:00"
"B";"22/12/21 05:10:00"
"D";"23/12/21 12:30:00"
"A";"23/12/21 12:35:00"
"B";"23/12/21 12:45:00"
"A";"29/12/21 14:17:00"
"B";"29/12/21 14:25:00"
.\B\time.csv
"Users";"DHMS"
"A";"22/12/21 05:02:00"
"B";"22/12/21 05:10:00"
"D";"23/12/21 12:30:00"
"A";"23/12/21 12:35:00"
"B";"23/12/21 12:45:00"
"A";"29/12/21 14:17:00"
"B";"29/12/21 14:25:00"
.\C\time.csv
"Users";"DHMS"
"C";"22/12/21 06:30:00"
"C";"26/12/21 10:32:00"
.\D\time.csv
"Users";"DHMS"
"D";"23/12/21 12:30:00"
"A";"23/12/21 12:35:00"
"B";"23/12/21 12:45:00"
"D";"28/12/21 11:15:00"