PowerShell: dates and -replace

I sometimes need to do a naive replacement of dates in a typical UK format to a typical US format.

For example, I might want to take

The Battle of Hastings took place on 14/10/1066 between Harold and William. The war between the 2 was triggered by Edward the Confessor’s death on 12/06/1066.

, and convert it to:

The Battle of Hastings took place on 10/14/1066 between Harold and William. The war between the 2 was triggered by Edward the Confessor’s death on 06/12/1066

or to…

The Battle of Hastings took place on 10-14-1066 between Harold and William. The war between the 2 was triggered by Edward the Confessor’s death on 06-12-1066

This line achieves the second transformation, assuming the text is in history.txt. Note the single quotes.

$(Get-Content -Path .\history.txt) -replace '([0-9]{2})/+([0-9]{2})/+([0-9]{4})','$2-$1-$3'

psregex02

Gist.

Credit to Don Jones for a similar example.

NotePad++: ¬†As this is essentially Regex after all, I will mention that you can do the same kind of thing in NotePad++ and indeed in anything that has a Regex capability. The NotePad++ case looks like this, where the “Find what” pattern is exactly as for PowerShell, and the “Replace with” differs a bit: [\2/\1/\3]

psregex03

Advertisements

PowerShell: date formats

I want to take this content:

The schoolmaster was leaving the village,12/20/2014 16:00:22,and everybody,12/20/2005 16:00:22, seemed sorry.
The miller at Cresscombe lent,05/20/2002 12:30:22,him the small white tilted,12/20/2004 16:00:22, cart and
horse to carry his goods to the city,05/20/2002 03:10:02, of his ,11/20/2014 16:00:22,destination, about twenty
miles off, such a vehicle proving of ,05/20/1966 08:00:22,quite sufficient ,08/20/2014 16:00:22,size for the blah blah blah

See the dates in US format? I want to do a naive transposition into e.g. GB format, so 20/12/2014 etc in this case.

DateFormatPs01DateFormatPs02DateFormatPs03

function Write-Status ($message, $value) {
 if ($value -ne $null) {
 $value = "[" + $value + "]"
 }
 Write-Host "$(Get-Date) [$message] $value" -BackgroundColor White -ForegroundColor Black
}

function Backup-File($path) {
 $id = [System.Guid]::NewGuid()
 $backupFile = "$env:temp\$id.csv"
 Write-Status "Backup file" $backupFile
 Copy-Item $path $backupFile
}

function Validate-File ($path) {
 $locale = $Host.CurrentUICulture.Name
if ($locale -ne "en-GB") {
 Write-Status "Running in the [$locale] locale. OK"
 exit
 }
 if ($(Test-Path $path) -ne $true) {
 Write-Status "File [$path] was not found"
 exit
 }
Write-Status "Locale" $locale
 Write-Status "Data file" $path
}

# Given a CSV record, parse it for data fields (naive), and invert the day and month.
# This only works with the patterns e.g. 31/12/2001 and 12/31/2001
# Return the record with those inversions.
function Convert-DatesInRecord($record) {
 if ($record.length -eq 0) {
 return
 }
 $tempSrcRecord = $record.split(",")
 $first = $true
$tempSrcRecord | ForEach-Object {
 $tempSrcField = $_
 if ($tempSrcField[2] -eq "/" -and $tempSrcField[5] -eq "/") {
 # naive // style date format...
 $tempTargetDate = $tempSrcField.Substring(3,2) + "/" + 
 $tempSrcField.Substring(0,2) + "/" + $tempSrcField.Substring(6)
 $tempSrcField = $tempTargetDate
 }
 if ($first) {
 $outputRecord = $tempSrcField
 $first = $false
 } else {
 $outputRecord = $outputRecord + "," + $tempSrcField
 }
 }
 $outputRecord + "`r`n"
}

function Convert-DatesInFile($path) {
 Clear-Host
 Write-Status "Conversion starting"
 Validate-File $path
 Backup-File $path
$outputFileContent = ""
 Get-Content $path | ForEach-Object {
 $record = $_
 $outputFileContent += Convert-DatesInRecord $record
 }
 $outputFileContent | Set-Content $path
 Write-Status "Conversion complete"
}

# Execute this...
Convert-DatesInFile -path "c:\sandbox\powershell\BasicFile.csv"