34 lines
1.1 KiB
PowerShell
34 lines
1.1 KiB
PowerShell
$csv = Import-Csv .\Missing_financial_documents_201603.csv -Delimiter ';'
|
|
|
|
$startDate = Get-Date
|
|
$endDate = Get-Date 'January 1, 1970'
|
|
|
|
$result = foreach($entry in $csv) {
|
|
$date = [datetime]::ParseExact($entry.documentdate, 'dd-MM-yyyy', [cultureinfo]::InvariantCulture)
|
|
|
|
if($date -gt $endDate) { $endDate = $date }
|
|
if($date -lt $startDate) { $startdate = $date }
|
|
|
|
[pscustomobject]@{
|
|
PTORefNumber = $entry.ptorefnumber
|
|
MissingDocument = $entry.MissingDocument
|
|
DocumentType = & {
|
|
switch($entry.MissingDocument.Substring(0,1)) {
|
|
"F" { "Invoice" }
|
|
"K" { "CreditNote" }
|
|
"R" { "Reminder" }
|
|
"B" { "Payment" }
|
|
"E" { "Interest" }
|
|
default {
|
|
Write-Host "Skipping $($entry.ptorefnumber):$($entry.MissingDocument)"
|
|
continue
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
$result | Export-Csv -Delimiter ';' -NoTypeInformation -NoClobber -Path "MissingNo.csv"
|
|
|
|
Get-Content -Path "MissingNo.csv" | Select -Skip 1 | % { $_ -replace '"','' } | Out-File -FilePath 'MissingNo.Processed.csv' |