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

You must log in to answer this question.

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