3

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
12
  • If there is a new line after cc, why is cccc afterwards written in the same line?
    – pLumo
    Jun 14, 2019 at 10:49
  • How can we know when a row is bad? Can we assume that if a row has less than 4 fields, it should be joined to the next row? How can we know if a newline is "in a field" as you say or not? How can we tell if that cc on the last line should be appended to the end of line 3 or to the end of the 3rd field of line 3? Are the field lengths also always constant at 4 characters?
    – terdon
    Jun 14, 2019 at 11:04
  • Please show a hex dump (or similar) of your input data that includes some broken lines, e.g. od -c -tx1 inputfile. It may be important to see exactly how the data looks like. What program creates the input data?
    – Bodo
    Jun 14, 2019 at 11:21
  • @terdon That is a faithful representation of the input I have. I did not make this and I can't change how it is handled to me but the data is in this shape. What you ask is part of the problem Jun 14, 2019 at 12:12
  • 2
    OK, but can you answer any of those questions? How can we tell if that cc on the last line should be appended to the end of line 3 or to the end of the 3rd field of line 3? Are the field lengths also always constant at 4 characters?
    – terdon
    Jun 14, 2019 at 12:24

1 Answer 1

5

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
2
  • Could you please write a brief explanation of this solution? Jun 14, 2019 at 12:57
  • Your assumptions are correct and this works perfectly. Thank you! Jun 14, 2019 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.