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"
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s