Replace extra spaces, tab and new line between delimiter

Question

Data output file is having multiple line of records separated by new line. In each row, the columns are separated with delimiter |.

Trying to replace all extra whitespaces, tabs, newline characters in each column between delimiter. Each row data will end with "&*"

Data:

char1   |  char2 |
        char3|char  4 &*
char11
       |  char   12 |char13|char14 &*
char21           |  char22 |char23|  char24 &*

Roles:

• Trailing Spaces/Tabs need to be removed; then
• Join the lines if they starts/ends with a |
• Squeeze repeated whitespaces.
• Skip new line after "&*"

Result:

char1|char2|char3|char 4
char11|char 12|char13|char14
char21|char 22|char23|char24

Code what I have now is for replacing tab, need to change this to have new line and extra space.

sed  -i 's/[ \t]\+|/|/g' DataStats0914.txt

Answer 1

With GNU sed:

sed -E '
    :a /\s*&\*\s*$/ !{ N; s/\n//; ta; };
    # read "N"ext line and join (s/\n//) except "!" lines that ends with "&*" chars;
    s/&\*//g;
    # also remove these chars "&*" too 
    s/\s*\|\s*/|/g;
    # remove whitespaces around "|" char as well
' <(tr -s '\t ' ' ' <infile)

comments-free command below:

sed -E '
    :a /\s*&\*\s*$/ !{ N; s/\n//; ta; }; 
    s/&\*//g;       
    s/\s*\|\s*/|/g;
' <(tr -s '\t ' ' ' <infile)