Reformat delimited file with rows splitted into multiple lines

Question

I have an input like this:

FIELD1   FIELD2   FIELD3   FIELD4
aaaa   bbbb   cccc   dddd
eeee   ffff
           gggg   hhhh
iiii   jjjj   kk   llll
              kk

It should be a space separated list of records, but some have been wrongly written.

Correct rows are like the first row. Other rows either have a newline in the middle (second row) or a field was written as it had a newline inside a field (third field of third row).

What I would like to do is to obtain an ordered output like this:

FIELD1   FIELD2   FIELD3   FIELD4
aaaa   aaaa   aaaa   aaaa
bbbb   bbbb   bbbb   bbbb
cccc   cccc   cccc   cccc

I guess that if I can fix a row like ROW2, fixing ROW3 would be similar but I can't understand how to have some tool like awk or sed see more than one row at a time and, for example, replace "\n\s*bbbb" with " bbbb" and fix ROW2.

EDIT:

This is a snippet of the real data I have:

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
                                              hypovereinsbank.de
G00PFMA7     transition_export_policy_1 3
                                     nfs      inf01mz2              sys

Fields are separated by spaces. ROW1 is how every line should be, but ROW2 and ROW4 have a newline between FIELD3 and FIELD4 and ROW3 seems to have a newline inside FIELD5. This is probably due to the fact that this is the result of a copy and paste from an Excel file, where you can have a newline inside a field.

EDIT2:

With this snippet of text the correct output would be:

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

Answer 1

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