CODEWITHSUNDEEP
powershellcsv
brokencrow
brokencrow

Reputation: 57

updating column value for exported csv powershell

I have following code which is working correctly. Although I now need to modify the output in one specific column, so I can sort by this column correctly.

Here is my code:

$inputFile  = "C:\Data\expPasswords\expPasswords.csv"
$outputFile = "C:\Data\expPasswords\expPasswordsUp.csv"

$result = Import-Csv $inputFile |
            Select-Object @{ Name = 'Account'; Expression = { $_.Account  } },
                          @{ Name = 'Days until Expiry'; Expression = { $_.'time until password expires' } },
                          @{ Name = 'Email address'; Expression = { $_.'email address'  } }

# output on screen
$result | Sort-Object -Property 'Days until Expiry' | Format-Table -AutoSize

# output to csv
$result | Sort-Object -Property 'Days until Expiry' | Export-Csv -Path $outputFile -NoTypeInformation

I need to sort by the 'Days until Expiry' column. Although makes it hard when the output is as below:

0 minutes
0 minutes
1 day and 19 hours
1 day and 2 hours
1 day and 20 hours
1 day and 23 hours
13 hours
2 days
20 hours

Basically, what I would like to do is:
- If less than 1 day, make the value: Today
- Remove the hours and minutes blocks.
- So if it is 13 hours, make the value: Today
- If the value is 1 day and 1 hours and 35 minutes, make the value: 1 day

Any assistance will be greatly appreciated. ;-)

Upvotes: 1

Views: 506

Answers (3)

IT M
IT M

Reputation: 447

I think the following might be of help (you will need to edit some of it, off course):

$Timings = @("0 minutes","0 minutes","1 day and 19 hours","1 day and 2 hours","1 day and 20 hours","1 day and 23 hours","13 hours","2 days","20 hours")

foreach ($Timing in $Timings) {
   $Output = $null
   if ($Timing -like "* minutes") {$Output = 0}
    elseif ($Timing -like "* Day and * hours") {$Output = [int](($Timing).Split(' day')[0])}
    elseif ($Timing -like "* hours") {$Output = 0}
   else {$Output = [int](($Timing).Split(' day')[0]) }

   switch ($Output) {
   0 {$Result = "Today"}
   1 {$Result = "Tomorrow"}
   default {$Result = "Over $Output Days"}
   }

   Write-Output "$timing ==> $Result"
}

Upvotes: 1

Theo
Theo

Reputation: 61028

Its a shame you should spend time to make some sense out of this rather foolish output, but of course it can be done. Basically, all you want to do is find out if the string starts with a number followed by the word 'day' or 'days' and cut off all the rest. If this is not the case, the returned value should be 'Today'.

The easiest way to do that I think is by using switch -Regex.

Try

$inputFile  = "C:\Data\expPasswords\expPasswords.csv"
$outputFile = "C:\Data\expPasswords\expPasswordsUp.csv"

$result = Import-Csv $inputFile | ForEach-Object {
    $daysLeft = switch -Regex ($_.'time until password expires') {
        '^(\d+ days?)' { $matches[1] }
        default { 'Today' }
    }
    [PsCustomObject]@{
        'Account'           = $_.Account
        'Days until Expiry' = $daysLeft
        'Email address'     = $_.'email address'
    }
} | Sort-Object -Property 'Days until Expiry'

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

# output to csv
$result | Export-Csv -Path $outputFile -NoTypeInformation

Regex details:

^           Assert position at the beginning of the string
\d          Match a single character that is a “digit” (any decimal number in any Unicode script)
   +        Between one and unlimited times, as many times as possible, giving back as needed (greedy)
\ day       Match the character string “ day” literally (case sensitive)
s           Match the character “s” literally (case sensitive)
   ?        Between zero and one times, as many times as possible, giving back as needed (greedy)

Seeing your comment, I would suggest adding a real DateTime object to sort on.

Something like this:

$today = (Get-Date).Date

$result = Import-Csv 'D:\test.csv' | ForEach-Object {
    $expiryString = $_.'time until password expires'
    $expiryDate   = $today
    if ($expiryString -match '(\d+)\s*day')    { $expiryDate = $expiryDate.AddDays([int]$matches[1]) }
    if ($expiryString -match '(\d+)\s*hour')   { $expiryDate = $expiryDate.AddHours([int]$matches[1]) }
    if ($expiryString -match '(\d+)\s*minute') { $expiryDate = $expiryDate.AddMinutes([int]$matches[1]) }
    if ($expiryString -match '(\d+)\s*second') { $expiryDate = $expiryDate.AddSeconds([int]$matches[1]) }

    $daysLeft = if ($expiryDate.Date -eq $today) { 'Today' } else { ($expiryDate - $today).Days}

    [PsCustomObject]@{
        'Account'           = $_.Account
        'Email address'     = $_.'email address'
        'Days until Expiry' = $daysLeft
        'Expiration Date'   = $expiryDate
    }
} | Sort-Object -Property 'Expiration Date'

# output on screen
$result

Output:

Account Email address         Days until Expiry Expiration Date  
------- -------------         ----------------- ---------------  
User1   [email protected] Today             6-4-2020 0:00:00 
User6   [email protected] Today             6-4-2020 0:03:00 
User8   [email protected] Today             6-4-2020 13:00:00
User4   [email protected] Today             6-4-2020 20:00:00
User9   [email protected] 1                 7-4-2020 2:00:00 
User2   [email protected] 1                 7-4-2020 19:00:00
User5   [email protected] 1                 7-4-2020 20:00:00
User7   [email protected] 1                 7-4-2020 23:00:00
User3   [email protected] 2                 8-4-2020 0:00:00

If you don't want that new property 'Expiration Date' in your output, simply filter it away with:

$result | Select-Object * -ExcludeProperty 'Expiration Date'

Upvotes: 2

iRon
iRon

Reputation: 23623

The constrains you defined will likely make it more confusing. I would just convert it to a [TimeSpan] structure which makes it easy to sort:

$Result = ConvertFrom-Csv @'
"Account","Days until Expiry",  "Email address"
"Account1","0 minutes",         "[email protected]"
"Account2","1 day and 19 hours","[email protected]"
"Account3","2 days",            "[email protected]"
"Account4","20 hours",          "[email protected]"
"Account5","1 day and 20 hours","[email protected]"
"Account6","3 minutes",         "[email protected]"
"Account7","1 day and 23 hours","[email protected]"
"Account8","13 hours",          "[email protected]"
"Account9","1 day and 2 hours", "[email protected]"
'@


Function ConvertTo-TimeSpan([String]$String) {
    $Days    = If ($String -Match '\d+(?=\s*day)')    {$Matches[0]} Else {0}
    $Hours   = If ($String -Match '\d+(?=\s*hour)')   {$Matches[0]} Else {0}
    $Minutes = If ($String -Match '\d+(?=\s*minute)') {$Matches[0]} Else {0}
    $Seconds = If ($String -Match '\d+(?=\s*second)') {$Matches[0]} Else {0}
    New-TimeSpan -Days $Days -Hours $Hours -Minutes $Minutes -Seconds $Seconds
}

$Result | Sort @{e = {ConvertTo-TimeSpan $_.'Days until Expiry'}}

Result:

Account  Days until Expiry  Email address
-------  -----------------  -------------
Account1 0 minutes          [email protected]
Account6 3 minutes          [email protected]
Account8 13 hours           [email protected]
Account4 20 hours           [email protected]
Account9 1 day and 2 hours  [email protected]
Account2 1 day and 19 hours [email protected]
Account5 1 day and 20 hours [email protected]
Account7 1 day and 23 hours [email protected]
Account3 2 days             [email protected]

Upvotes: 0

Related Questions