CODEWITHSUNDEEP
powershell
Darren_B
Darren_B

Reputation: 25

Removing blank space in columns in pipe delimited file in powershell

I have an issue where I need to remove blank spaces in columns 3 and 4 which may or may not exist in a pipe delimited text file using powershell.

The input file looks like this :

COLMABQ1|02112017|001000 08248|BQ|Name|
COLMABP1|02112017|00100009693|B P|Name|
COLAL3|02112017|001000 12032|C D|Name|
COLMAAO|02112017|00100014915|AO|Name|
COLAL1H|02112017|00100 017939|C D|Name|

I need the output file to look like this :

COLMABQ1|02112017|00100008248|BQ|Name|
COLMABP1|02112017|00100009693|BP|Name|
COLAL3|02112017|00100012032|CD|Name|
COLMAAO|02112017|00100014915|AO|Name|
COLAL1H|02112017|00100017939|CD|Name|

The nearest I have come to solving it so far is converting the file to a .csv file, replacing every | with a ",", running the code below against columns 3 & 4 then changing all the "," back to |

$headers = 1..5|%{"H{0}" -f $_}
$Csv = Import-Csv $infile -Header $Headers
$Csv|ft -auto

ForEach ($Row in $Csv) {
 $Row.H3 = $Row.H3 -Replace ' '
}
$CSV | ConvertTo-CSV -NoType | Select -Skip 1 | Set-Content $outfile

Even this doesn't work exactly as I'd like and I'm convinced there must be a far easier way to do this...but 2 day's worth of Googling seems to suggest otherwise!

Any help anyone can give with this would be gratefully received as it's driving me insane.

Upvotes: 0

Views: 712

Answers (3)

mjolinor
mjolinor

Reputation: 68243

One possibility:

Get-Content $infile |
 ForEach-Object {
    $parts = $_.split("|")
    $parts[2] = $parts[2].replace(" ","")
    $parts[3] = $parts[3].replace(" ","")
    $parts -join "|"
 } | Add-Content $Outfile

Upvotes: 1

Manu
Manu

Reputation: 1742

If only columns 3 & 4 have blank spaces :

(Get-Content $infile) -replace '\s+' | Set-Content $infile

Upvotes: 0

Vincent K
Vincent K

Reputation: 1346

(Get-Content 'C:\Vincent imp\Test\Test.txt') -replace '(^.*\|.*\|.*) (.*\|.*\|.*\|)','$1$2' -replace '(^.*\|.*\|.*\|.*) (.*\|.*\|)','$1$2'

COLMABQ1|02112017|00100008248|BQ|Name|    
COLMABP1|02112017|00100009693|BP|Name|    
COLAL3|02112017|00100012032|CD|Name|    
COLMAAO|02112017|00100014915|AO|Name|    
COLAL1H|02112017|00100017939|CD|Name|

Upvotes: 0

Related Questions