Look up column in a flat file

Shell Programming and Scripting
1

Here is on more go ! Need a shortcut for my problem !

problem is i have a look_update with fixed sequence of column
that is :

        MANDT:SERAIL:SERSCHA:SEREX:EQTYP:BSTVP

I will be getting data in a flat file having same number of column but the sequence could be different in each time

for example
SERAIL MANDT SERSCHA SEREX EQTYP BSTVP
510 1 f g s r
510 2 g S t
510 3 g S 2
510 4 g S 2
510 Z001 g S
510 Z004 f gr S 2
510 ZPM1 f er M 1
510 ZPM2 76 M 1

here the sequence of the column is not what it in look up string
it should be in the sequence of look up string .
I want to arrange the data of this table in look up sequence and put it into another filnal_data file

each time the clientdata may come in differnt sequence but i have arrange the column in the sequence of lookup_string and append it to final table

Hope u all got my problem ! please assist

2

jambesh can you explain again and clearly that will help us to understand and you will get more accurate answer

3

Dhruv,
Here is the problem again ..
I have a fixed look up file whose column are in fixed sequence say..
"MANDT SERAIL SERSCHA SEREX EQTYP BSTVP"
i have stored this sequence in a variable ..
Now

  I  am getting data file whose column sequence  could be differnt from that i have mention in the look up string .

I want to resuffle this data file according to the column sequence of the look_up string.

data file column sequence could be

case -1 sequence
----------

SERSCHA SEREX EQTYP BSTVP MANDT SERAIL
333 4343 fdfd fdfdf dssds fdfdf
343 343 rere 43 fdf 4343

case -2 sequence :
-------------------
SEREX EQTYP BSTVP MANDT SERAIL SERSCHA
121 3232 323 ddd sd 223

see the sequence of these file each time sequence of column varies .
the final reaange of these file should be based on the column of the look up[ string always.

Ok
1

Now i want to resuffle this data file so that the column starting with

MANDT should come first then SERAIL ...then SERSCHA SEREX EQTYP

and the final resuffle data should append to a file name say final_data.txt

4 
awk -v str="MANDT SERAIL SERSCHA SEREX EQTYP BSTVP" '
NR == 1 {
    n=split( str , arr , " " )
    for( i = 1; i <= n ; ++i )
        arr_ac[$i]=i
    for( i = 1; i <= n ; ++i )
        col=arr_ac[arr]
    print str 
}
NR > 1 {
    for(i = 1; i <= n ; ++i )
        printf("%s ",$col)
    printf("\n")
}
' file >> final_data.txt
5

check this post
rearrange file based on lookup file

6

here's an alternative in Python:

def transpose(matrix):        
        return [[matrix[y][x] for y in range(len(matrix))]for x in range(len(matrix[0]))]


all = open("input.txt").readlines()
lookupstring = ['MANDT', 'SERSCHA','SERAIL' , 'SEREX', 'EQTYP', 'BSTVP']
listing = [ i.split() for i in all ] 
results =  transpose(listing)
final= [ r for items in lookupstring for r in results if items == r[0] ]
for i in transpose(final):
        print ','.join(i)

Input:

MANDT SERAIL EQTYP SERSCHA SEREX BSTVP
510   hsgdfs 44    sercha  sex1  bst233
510   bg     89    fg      23    98
510   gh     89    we      sew   mn

Output:

/home>python test.py
MANDT,SERSCHA,SERAIL,SEREX,EQTYP,BSTVP
510,sercha,hsgdfs,sex1,44,bst233
510,fg,bg,23,89,98
510,we,gh,sew,89,mn