SQS Notes ETL Script

.gitignore

# Don't push anything that isn't specified in this file
*
!.gitignore
!.gitattributes
!etl.ps1

etl.ps1

Clear-Host;
$ErrorActionPreference = "Stop";
$VerbosePreference = "Continue";

$outputFile = Join-Path $PSScriptRoot "notes.sql";
$outputRejectionFile = Join-Path $PSScriptRoot "notes-rejected.csv";
"" | Set-Content -Path $outputRejectionFile -Encoding utf8NoBOM;

$sql = "INSERT INTO notes (timestamp, user_id, channel_id, nick, message)`r`nVALUES`r`n";

function Format-SqlString([string]$inputString) {
    return $inputString -replace "\\", "\\" -replace "'", "\'";
}

Get-Content -Path "notes.csv" | ConvertFrom-Csv -Delimiter "," -Header path, filename, size, type, date1, date2, date3, int1, int2, note | Select-Object -Property filename, note | % {
    $rejected = $False;
    $time = "";
    $nick = "";
    $message = "";
    $fnSplit = $_.filename -split '\.';
    $dateFromlogName = $fnSplit[1];

    $pTime = "\((\d{1,2}:\d{2}\.\d{2})\)";
    $pNick = "(?:(?:\s+?|\s+?->\s+?))+?(?:[<|\*|\-]{1}(.+?)[>|\*|\-]{1})";
    $pMsg = "\s+(?:!notes )?(.+)";
    $pattern = "$pTime$pNick$pMsg";
    if ($_.note -imatch $pattern -and $matches) {
        $time = $matches[1];
        $date = [DateTime]::ParseExact("$dateFromLogName $time", "yyyyMMdd H:mm.ss", $null);
        $nick = $matches[2];
        $message = $matches[3];

        if ($null -eq $message) {
            $rejected = $True;
        }
    }
    else {
        $rejected = $True;
    }

    if (!$rejected) {
        @{
            Date    = $date;
            Nick    = $nick;
            Message = $message.Trim();
        }
    }
    else {
        # Output to rejected file
        $_ | ConvertTo-Csv -NoTypeInformation -UseQuotes Never | Select-Object -Skip 1 | Add-Content -Path $outputRejectionFile -Encoding utf8NoBOM | Out-Null;
    }
} | Group-Object -Property Message | % {
    $_.Group | Select-Object -Property Nick, Message, Date -First 1 | Sort-Object -Property Date
} | `
    Select-Object Date, Nick, Message -Skip 1 | `
    Sort-Object -Property Date| `
    ForEach-Object {
        # 07/02/2000 15:21:00
        $sql += "    (STR_TO_DATE('$($_.Date)', '%m/%d/%Y %H:%i:%s'), NULL, NULL, '$(Format-SqlString $_.Nick)', '$(Format-SqlString $_.Message)'),`r`n";
    };

# EOF clean up
$sql = $sql.TrimEnd(",`r`n");
$sql += ";";
$sql | Set-Content -Path $outputFile -Encoding utf8NoBOM;