A Powershell script that BillPay merchants can use to clean their bill file. This script would need to be tested and, if there are other data concerns/errors, it might need to be modified for a particular BillPay merchant.
- Removes any non-ASCII characters
- Strips out any stray “ characters
- Strips out any stray commas that may potentially cause a problem
Usage syntax: .\sanitizeps.bat {filename}
Sanitizeps.bat: (This is the batch file wrapper)
@echo off
powershell .\sanitize.ps1 %1
del .\%1-1
del .\%1-2
del .\%1-3
del .\%1-4
del .\%1-5
sanitize.ps1: (This is the Powershell script)
# Read in file name
param( [string] $a )
# read file, remove non-standard ASCII chars, write to temp file 1
(Get-Content -path .\$a) -Replace '^ -~', '' | Out-File -encoding ASCII .\$a-1
# read temp file 1, replace "," with curly brace - will add back later - and write to temp file 2
(Get-Content -path .\$a-1) -Replace '","', "{" | Out-File -encoding ASCII .\$a-2
# read temp file 2, eliminate any stray ", write to temp file 3
(Get-Content -path .\$a-2) -Replace '"', "" | Out-File -encoding ASCII .\$a-3
# read temp file 3, eliminate any stray commas - except for 1st line - and write to temp file 4
(Get-Content -path .\$a-3) | Foreach-Object { if ($_.Readcount -le 1) { $_ } else { $_.Replace(',', '') } } | Out-File -encoding ASCII .\$a-4
# read temp file 4, replace curly brace with ",", write to temp file 5
(Get-Content -path .\$a-4) -Replace '{', '","' | Out-File -encoding ASCII .\$a-5
# finally, read temp file 5 and add " to beginning and end of all lines EXCEPT the first and write to out file
(Get-Content -path .\$a-5) | Foreach-Object { if ($_.Readcount -le 1) { $_ } else { -join('"', $_, '"') } } | Out-File -encoding ASCII .\$a-out