2

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
 
4
  • 2
    please update the question with the code you've tried and the (wrong) results generated by your code
    – markp-fuso
    Sep 20 at 20:55
  • Why some rows have 4 columns when some other have 5 ? Is it a bug or a feature ? Sep 20 at 21:20
  • @GillesQuénot Yes . The file is Messy with varying No. of columns on each row
    – pchegoor
    Sep 20 at 23:57
  • Standard regexes do not consider overlapping matches (this is true in practically all programming languages and tools). Some libraries have options to switch to searching overlapping matches however you probably won't find built-in way to replace overlapping matches... because it's not well defined what should be the result given that part of the text ends up in multiple replacements.
    – Bakuriu
    Sep 22 at 17:11

5 Answers 5

7

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
1
  • 2
    In case somebody's wondering why this works, but OP's command doesn't: sed won't match part of a replace for the next search. Most people probably rely on this behavior all the time and don't realize it.
    – Joshua
    Sep 21 at 18:12
6

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.

5

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
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.

1

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.

1
  • For the generic case, you may also want to empty out the first and last fields.
    – Kusalananda
    Sep 21 at 10:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .