Recently I was tasked with a file cleansing exercise. The endeavor looked simple, but the solution was not, I needed to prepare a file for processing, but wanted to first proof concept my approach so I opted to utilizing this opportunity to learn PoweShell.
More about the task: I was supplied with a series of CSV files in which the data looked like
123,ABC,XYZ
"1,2,3", ABC, "XY,Z"
123,"A,BC", XYZ
As you can see this is something I think we may call alternating double-quotes comma delimited file with embedded commas, I simply needed to remove the occasional quotes and replace the delimiter to pipe to be able to process the file preserving the original quotes. You may notice, I blogged in the previous post I was able to achieve the same using MS SSIS Script Transformation which basically uses some .Net code. To be fair, I did not come up with the algorithm, turned out my colleague Danny T. had a very extensive experience in textual data processing, so I was just the implementer.
The script turned out to work as expected, but alas there is a limitation to PowerShell’s get-content Cmdlet on how long data file it can digest. So the script may not be bale to process the entire input file. I guess the workaround is in using a revised version that utilizes the .Net stream class, hope somebody would share it with me someday.
So here is the script:
1: # Author: Arthur Zubarev
2: # Notes: Removes " and replaces the delimiter to |
3: # In DOS batch usage:
4: # Powershell.exe "& 'D:\PSScripts\RemoveQuotes-ReplaceDelim.ps1'" -InputCSV ""D:\Input.csv"" -NonInteractive -WindowStyle hidden -ExecutionPolicy RemoteSigned
5:
6: param([string]$InputCSV = "")
7:
8: # Temp assignment!
9: $InputCSV = "D:\Input.csv"
10:
11: $process_yes_no = 0
12:
13: if ( $InputCSV -eq "" ) {
14: Write-Host "Input parameter is missing. Usage: .\RemoveQuotes-ReplaceDelim.ps1 -InputCSV " -ForegroundColor red -BackgroundColor yellow
15: Exit
16: }
17:
18: function GetScriptDirectory {
19: $Invocation = (Get-Variable MyInvocation -Scope 1).Value
20: Split-Path $Invocation.MyCommand.Path
21: }
22:
23: Write-Host "Processing" $InputCSV -foregroundcolor green -backgroundcolor black
24:
25: # make the "fixed" file up
26: $fixed_file = Join-Path(GetScriptDirectory) "fixed.csv";
27:
28: Write-Host "Saving fixed file to" $fixed_file -foregroundcolor yellow -backgroundcolor black
29:
30: # read the input csv as binary
31: $reader = [System.IO.File]::OpenText($InputCSV)
32: try {
33: for(;;) {
34: $to_repair_t = $reader.ReadLine()
35: if ($to_repair_t -eq $null) {
36: break
37: }
38: $to_repair = get-content -encoding byte $to_repair_t
39: $fixed = New-Object System.Collections.ArrayList
40: $byteEncoder = New-Object System.Text.ASCIIEncoding
41: $in_quotes = 0
42:
43: foreach ($c in $to_repair) {
44: # toggle in-quotes flag
45: if ($c -eq 34) {
46: $in_quotes = !$in_quotes;
47: $process_yes_no = 1
48: }
49:
50: # replace comma in quotes with ~
51: if ($c -eq 44 -and $in_quotes) {
52: foreach ($b in $byteEncoder.GetBytes("~")) {
53: [void]$fixed.Add($b);
54: }
55: continue;
56: }
57:
58: # pass through the bytes
59: [void]$fixed.Add($c);
60: }
61:
62: if ($process_yes_no -eq 1 ) {
63: # write-out to fixed csv
64: set-content -encoding byte -path $fixed_file -value $fixed
65:
66: # carry on with the commas replacements
67: (Get-Content $fixed_file) |
68: Foreach-Object {$_ -replace ",", "|"} |
69: Set-Content $fixed_file
70:
71: # next do ~ replacements
72: (Get-Content $fixed_file) |
73: Foreach-Object {$_ -replace "~", ","} |
74: Set-Content $fixed_file
75:
76: # last fix step " removals
77: (Get-Content $fixed_file) |
78: Foreach-Object {$_ -replace """", ""} |
79: Set-Content $fixed_file
80:
81: # Swap the files
82: Move-Item $fixed_file $InputCSV -Force -ErrorAction SilentlyContinue
83:
84: #$File_Name = split-path $InputCSV -Leaf # in case the file name needs to be captured
85: }
86: else {
87: Write-Host "No processing was necessary."
88: }
89: }}
90: finally {
91: $reader.Close()
92: }
93:
94: Write-Host "Processing complete." -BackgroundColor DarkGreen -ForegroundColor yellow