When Excel messes up your CSVs

excel_to_posix_date.ps1

# Converting Date Entries in a CSV file back to POSIX style (after being mucked up by MS Excel)

# Note: The first column is for Dates

Param(
    [string]$file,
    [switch]$Detailed
)

$tempfile = New-TemporaryFile 

Get-Content -Path $file | Set-Content $tempfile.FullName

$text = Get-Content -Path $file

$text |
ForEach-Object {
    $entry = $_.substring(0, $_.indexof(","))
    if ($entry -match "^\d{2}/\d{2}/\d{4}$") {
        $values = $entry.split("/")
        $newDate = $values[2] + "-" + $values[1] + "-" + $values[0]
        $_ = $_.replace($entry, $newDate)
    }
    $_
} |
Set-Content -Path $file

if ($Detailed) {
    Write-Host "A backup of the original file can be found at $tempfile"
}