Need to Replace multiple spaces between PIPE symbols with no space

Question

Given a pipe-delimited file:

1|123|A |Normal Behaviour Exhibit 
2|345|B|Embedded|delimiter  
3|678|D|dimension 1"
4| |   | | nvalue
5| | | |Missing cvalue

I need to use Unix tools like sed or awk to replace all occurrences of | | with ||. There can be more than one space between the | symbols as in the 4th row above.

Expected output:

1|123|A |Normal Behaviour Exhibit 
2|345|B|Embedded|delimiter  
3|678|D|dimension 1"
4|||| nvalue
5||||Missing cvalue

I tried using the following sed command but it did not achieve the expected result:

sed 's/| *|/||/g' S.txt > S1.txt

1|123|A |Normal Behavior Exhibit  
2|345|B|Embedded|delimiter  
3|678|D|dimension 1"
4||   || nvalue
5|| ||Missing cvalue
 

Answer 1

Using any sed:

$ sed 's/|  *|/||/g; s/|  *|/||/g' file
1|123|A |Normal Behaviour Exhibit
2|345|B|Embedded|delimiter
3|678|D|dimension 1"
4|||| nvalue
5||||Missing cvalue

or using any awk:

$ awk '{while( sub(/\| +\|/,"||") );} 1' file
1|123|A |Normal Behaviour Exhibit
2|345|B|Embedded|delimiter
3|678|D|dimension 1"
4|||| nvalue
5||||Missing cvalue

Answer 2

With perl, you can replace all |<spaces> with | provided they're followed with a | using the (?=...) look-ahead regexp operator:

perl -pe 's/\| +(?=\|)/|/g' your-file

perl also has a -i option for in-place editing.

Answer 3

Using awk:

awk 'BEGIN{OFS=FS="|"}{for (i=1; i<=NF; i++) if ($i ~ /^ +$/) $i=""}1' file
1|123|A |Normal Behaviour Exhibit 
2|345|B|Embedded|delimiter  
3|678|D|dimension 1"
4|||| nvalue
5||||Missing cvalue

Answer 4

Using Miller to read this as a pipe-delimited index-numbered (NIDX) file, cleaning up all whitespace and writing the result to standard output (use -I to do the change in the file "in-line"):

$ mlr --nidx --fs pipe clean-whitespace file
1|123|A|Normal Behaviour Exhibit
2|345|B|Embedded|delimiter
3|678|D|dimension 1"
4||||nvalue
5||||Missing cvalue

This applies the built-in clean-whitespace operation on each record. This trims flanking whitespace and squeezes multiple whitespaces into single spaces within each field.

This also strips the flanking whitespace from otherwise non-empty fields, like the 3rd field of the first record or the last field in the 4th record. If that's not wanted, you will have to loop over the fields of each record as if you were using awk:

$ mlr --nidx --fs pipe -S put 'for (k,v in $*) { $[k] = sub(v,"^[[:space:]]+$","") }' file
1|123|A |Normal Behaviour Exhibit
2|345|B|Embedded|delimiter
3|678|D|dimension 1"
4|||| nvalue
5||||Missing cvalue

This does an explicit loop over each record's keys and values and empties each field with only space-like characters using a substitution. The regular expression ^[[:space:]]+$ (which we re-use below) matches a string that contains one or more space-like characters (only).

Since mentioning awk:

$ awk 'BEGIN { FS=OFS="|" } { for (i=1; i<=NF; ++i) sub(/^[[:space:]]+$/,"",$i) }; 1' file
1|123|A |Normal Behaviour Exhibit
2|345|B|Embedded|delimiter
3|678|D|dimension 1"
4|||| nvalue
5||||Missing cvalue

The above is a straight translation of the last Miller operation into awk, using an explicit loop over each field in each record, emptying any record that only consists of space-like characters. Compared to the Miller command, there is just a little bit of extra set-up code for using pipes as both input and output separators (the BEGIN block).

Doing this sed is definitely doable, but a bit problematic (a naive substitution with s/RE//g across each line would have issues with overlapping matches if it tries to match the pipe delimiters around each field) and would likely result in an unwieldy series of sed expressions, especially if you also want to handle the first and last fields of each record. A sed-like approach with an in-line Perl substitution, using lookahead etc., would probably be easier to construct, but not easier to read.

Answer 5

The first match consumes the pipe at the end that needs to be part of the second match.

I'd just loop-replace until no more matches are left instead of using the g modifier:

sed -e ': loop' -e 's/|  *|/||/' -e 't loop'

Note that I use two spaces in the pattern, so || does not match.