Splitting record into multiple records by appending values from an input field (AWK)

Hello,

For the input file, I am trying to split those records which have multiple values seperated by '|' in the last input field, into multiple records and each record corresponds to the common input fields + one of the value from the last field.

I was trying with an example on this forum using awk: (split records into multiple records)

Though I understood that example, but has not been able to solve for my case.

I am running on Ubuntu 10.10.

Could you assist me in my problem ?

Thank you!

Input:

11686 19151 25489 45012
11686 19151 3257 29286 3209|6774|8928|20459|37185
11686 19151 39792 21367 38922 65000
11686 19151 9002 31200 41654|64999|65000|65002|65011|65012|65014|65100|65200|65500

Desired Output:

11686 19151 25489 45012
11686 19151 3257 29286 3209
11686 19151 3257 29286 6774
11686 19151 3257 29286 8928
11686 19151 3257 29286 20459
11686 19151 3257 29286 37185
11686 19151 39792 21367 38922 65000
....

Hi imtiaz99,

Try with perl. It's a great tool for this kind of tasks:

$ cat infile
11686 19151 25489 45012
11686 19151 3257 29286 3209|6774|8928|20459|37185
11686 19151 39792 21367 38922 65000
11686 19151 9002 31200 41654|64999|65000|65002|65011|65012|65014|65100|65200|65500
$ perl -lane '
    if ( index( $F[ $#F ], q[|] ) > -1 ) { 
        @f = split /\|/, $F[ $#F ]; 
        for ( @f ) { 
            printf qq[%s %s\n], qq{@F[0..(@F-2)]}, $_ 
        } 
    } 
    else { 
        printf qq[%s\n], $_ 
    }
' infile
11686 19151 25489 45012
11686 19151 3257 29286 3209
11686 19151 3257 29286 6774
11686 19151 3257 29286 8928
11686 19151 3257 29286 20459
11686 19151 3257 29286 37185
11686 19151 39792 21367 38922 65000
11686 19151 9002 31200 41654
11686 19151 9002 31200 64999
11686 19151 9002 31200 65000
11686 19151 9002 31200 65002
11686 19151 9002 31200 65011
11686 19151 9002 31200 65012
11686 19151 9002 31200 65014
11686 19151 9002 31200 65100
11686 19151 9002 31200 65200
11686 19151 9002 31200 65500
1 Like

Thanks birei for your answer. I haven't worked with Perl before, but I will take your advice as I am working on data analysis and network graphs..

Thanks !

Hi

 sed -e 's/\(.*\) \(.*\)/echo "\1 "{\2}/' -e 's/|/X,/g' File | sh | sed -e 's/X /\n/g' -e 's/[{}]//g'

Guru.

Using awk...

awk '{n=split($NF,a,/\|/); for(i=1;i<=n;i++){$NF=a; print}}' file1 > file2
1 Like