1

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. ;-)

Improve this question
3
  • can you modify the source that creates the 1st CSV file? the ... foolish ... decision to corrupt data with "pretty print" stuff should be avoided whenever possible - and you are seeing the reason right now. [grin] Commented Apr 6, 2020 at 5:15
  • Unfortunately, the csv is generated from a third-party application, which I have no control over, I need to manipulate the data after the original export. The end result is then added to a HTML email, which needs the "pretty print" as a report Commented Apr 6, 2020 at 5:36
  • thank you for the "why" ... and you have my condolences over the too-early "humanizing" of the data. [grin] Commented Apr 6, 2020 at 13:30

3 Answers 3

Reset to default
2

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'
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

really thought this one would work, but unfortunately the result that was displayed was a single line with one record of null values for Account and Email address and 5 days in the Days until expiry. Will have to double check what I have changed to make sure it is setup correctly. Is there another way that I can set a rule as follows: minutes then hours then days, this way at least I could get the order correct. At the moment, it sorts by the first digit; e.g 7 hours would come after 2 days, where it should check if it is minutes, hours or days after the number before setting the order.
totally agree with you too. Very foolish output to have to deal with.
@brokencrow Sorry, was a bit in a hurry and pasted wrong. Code updated now and I have added an example output.
Always comes through with the goods. You are a code ninja :-) All working. Thank you
@brokencrow Thanks for the nice feedback!
1

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"
}
Improve this answer

Comments

0

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]
Improve this answer

4 Comments

I have tested this one too, but unfortunately I can not manually add the values I have to work with. The original result set is all column values in a csv file, which are going to change.
Yes, I have taken that in consideration. I did a little update to show it with your properties. What exactly "Doesn't seem to work correctly" ?
this code is slightly different from before, I will test it this way and let you know how I go
I didn't change the function but just the input to show how it works. To better understand the Timespan structure (which is sortable on a much finer scale!), you might also add a new column like: $Result | Select *,@{n='Timespan until Expiry'; e={ConvertTo-TimeSpan $_.'Days until Expiry'}} | Sort 'Timespan until Expiry'

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.