Linux – How to count the number of rows that have data in a pipe-delimited file

This GNU awk script should do the job:

$ awk 'NR==1 { size=NF;$1=$1;OFS="\t";print;next } 
{ for(i=2;i<=NF;i++) {id[$1]=$1; record[$1,i-1]+=$i} } 
END { PROCINFO["sorted_in"]="@ind_num_asc"
  for(i in id){ printf("%s\t",i);
    for(j=1;j<size;j++) printf("%s\t",record[i,j]);
    printf("\n");
  }
} ' data.txt > out.txt
$ cat out.txt
Id  a   b   c   d   e
1   6   9   12  15  18  
2   15  18  21  24  27  
3   34  38  42  46  50  
4   23  25  27  29  31  
5   27  29  31  33  35

Edit:

Here is a version that preserves the original row ordering instead of sorting the ids:

$ awk 'NR==1 { size=NF;$1=$1;OFS="\t";print;next }
{ if(o[$1]==0) o[$1]=NR
  for(i=2;i<=NF;i++) {record[$1,i-1]+=$i} }
END { PROCINFO["sorted_in"]="@val_num_asc"
  for(i in o){ printf("%s\t",i)
    for(j=1;j<size;j++) printf("%s\t",record[i,j])
    printf("\n") }
}'

Let's first fix the split lines, neglecting the split columns:

$ grep -v "^\s*[^ ]*$" file | grep -o "[^ ]*" | paste - - - - - -
G00PFMA1    transition_readonly 2   cifs    0.0.0.0/0   any
G00PFMA7    transition_export_policy_1  1   nfs 10.58.91.134    sys
G00PFMA7    transition_export_policy_1  2   nfs bmczone.tsy.fm. sys
G00PFMA7    transition_export_policy_1  3   nfs inf01mz2    sys

Explanation:

• Filter the lines containing only a single element:

  grep -v "^\s*[^ ]*$" file
  

• Put all items on a separate line

  grep -o "[^ ]*"
  

• Put them back together to six columns per row

  paste - - - - - -
  

I got a hacky full solution that might be good enough for your needs but is neither nice nor very portable. It assumes, that the only column getting split is column #5 and that we'll always have 6 columns.

{
print_items(){
    # print if there are 6 elements
    if [ $# = 6 ]; then
      echo "$@"
    # print if there are 7 elements, but merge element 5 and 7 before printing
    elif [ $# = 7 ]; then
      set -- "${@:1:4}" "${5}${7}" "${@:6:1}"
      echo "$@"
    fi
}
items=()
while IFS= read -r line; do
    # Get start position of first item
    start_position=$(grep -o "^ *" <<< "$line" | wc -c)
    # if start_position is 0 then create new array items with elements in the line
    if [ $start_position = 0 ]; then
        # when new line starts, print previous line
        print_items "${items[@]}"
        items=( $line )
    # if start_position is not 0, add the elements in the line to the existing items array
    else
        items+=( $line )
    fi
    # Print items
done < file
# print last line
print_items "${items[@]}"
} | column -t

Output:

G00PFMA1  transition_readonly         2  cifs  0.0.0.0/0                          any
G00PFMA7  transition_export_policy_1  1  nfs   10.58.91.134                       sys
G00PFMA7  transition_export_policy_1  2  nfs   bmczone.tsy.fm.hypovereinsbank.de  sys
G00PFMA7  transition_export_policy_1  3  nfs   inf01mz2                           sys