powershell - 更新导出的csv powershell的列值

Question

我有以下代码可以正常工作。
尽管我现在需要在一个特定的列中修改输出，所以我可以正确地按此列排序。

这是我的代码:

$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

我需要按“到期前的天数”列进行排序。虽然在输出如下所示时比较困难:

0分钟
0分钟
1天19小时
1天2小时
1天20小时
1天23小时
13小时
2天
20小时

基本上，我想做的是:
-如果少于1天，请输入值:今天
-删除小时和分钟块。
-因此，如果是13小时，请输入值:今天
-如果值为1天1小时35分钟，则设置为:1天

任何帮助将不胜感激。 ;-)

Answer 1

您应该花一些时间来从这个相当愚蠢的输出中了解一些信息，这很可惜，但是当然可以做到。
基本上，您要做的就是找出字符串是否以数字开头，后跟单词“day”或“days”，然后截断所有其余部分。如果不是这种情况，则返回的值应为“Today”。

我认为最简单的方法是使用switch -Regex。

尝试

$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

正则表达式详细信息:

^           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

输出:

Account Email address         Days until Expiry Expiration Date  ------- -------------         ----------------- ---------------  User1   user1@yourcompany.com Today             6-4-2020 0:00:00 User6   user6@yourcompany.com Today             6-4-2020 0:03:00 User8   user8@yourcompany.com Today             6-4-2020 13:00:00User4   user4@yourcompany.com Today             6-4-2020 20:00:00User9   user9@yourcompany.com 1                 7-4-2020 2:00:00 User2   user2@yourcompany.com 1                 7-4-2020 19:00:00User5   user5@yourcompany.com 1                 7-4-2020 20:00:00User7   user7@yourcompany.com 1                 7-4-2020 23:00:00User3   user3@yourcompany.com 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'