Script: Clean BillPay File of Embedded " Characters

Modified on Mon, 27 Mar, 2023 at 10:02 AM

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