Support Services

Script: Clean BillPay File of Embedded " Characters

Dept. Process Owner: Technical SupportCreated Date: 07/15/2022
CSA: Mandy CarpenterVersion #: 2.0
Approved By: Ana Noble - Created by Luke IdaszewskiLast Updated: 04/08/2018 
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 

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.