merging files

Shell Programming and Scripting
1

Thanks in advance
I have 2 files having key field in each.I would like to join both on common key.I have used join but not sucessful.
The files are attached here .
what i Want in the output is on the key field SLS OFFR .
I have used join commd but not successful.
File one

SNO NAME ID NO SLS OFFR
122 JACOB a1232 0002522
PUMBA a1535 0002522
STMBA c1456 0002522
111 SMITH b1233 0002513
124 JHON a1256 0002532
SWMNA a2145 0002532
327 GAOLP c3783 0002518
853 GLODY c2579 0002518

FILE TWO

NAME SLS OFFR
SUPHYA 0002533
SOTRKY 0002546
UBERTS 0002514
MANDYS 0002518
MAICHS 0002524

output required is:OUT PUT FILE
NAME SLS OFFR
SUPHYA 0002522 122 JACOB a1232
PUMBA a1535
STMBA c1456
SOTRKY 0002513 111 SMITH b1233
UBERTS 0002532 124 JHON a1256
SWMNA a2145
MANDYS 0002518 853 GLODY c2579
327 GAOLP c3783

join -1 4 -2 2 -t ' ' -o 1.1 1.2 1.3 1.4 file1 file2 > newfile

Can any one help please,Thanks in advance for your help.

2

Hi,
You can use paste command
In this case you can also use join command as the fields are same

Regards,
Prakash

3

Join will help if the files are sorted. Also you have in your file1 rows, that have not 4 fields/columns but only 3.

Also in your desired output your first line looks like:

but in your input file1, the only line that has "a1232" in it, has the key "0002522". The line of file2 that matches and contains "SUPHYA", has "0002533" as key... so how should that work. The desired output doesn't not work with the input files. I see no logic behind the output or maybe it is screwed up because it is not in [ code ] [ /code ] tags, idk.
If there is no match, you still want the line of file2 printed out, it seems, sometimes, not always.

Maybe you are better off using awk and to check if $NF of file1 matches with $2 or $NF of file2.

Something is screwed up in the desired output file, at least for me.

EDIT: Also in the attached file there is only the content of file2 as far as I can see.

@prakashreddy
He used join already and without sorting it won't work anyway. Also a simple paste will help nothing to get that desired output. Paste can't join on fields. Or maybe you can show how to do that.

4

Thanks Prakash amd Zaxxon for your reply ,
sorry for my typo for suphya 0002533 it is 0002522. sorry for that.

Secondly in the first file there are four fields but where the SLS OFFR
are the same the first field is not supplied in . That is where the
problems comes.There are NAME ID SLS OFFR But SNO is not
there and there I required the help is SLS OFFR IS SAME BUT THE
SNO is not given there the no. of fields are three and not four,otherwise
where the SNO is there the field count are four .I have given proper space
and aligned but in reality it is not showing the proper alignments.

Can we still join , logically NO , but through the shell script is only hope .

But thanks once again for your help.

5

I will just add to simplyfy it more
file 1[LEFT][/LEFT]
1 abc jonny 1234
cde jigsw 1234
efg hary 1234
2 hjk nilof 2345
klm sumpr 2345

file 2[LEFT][/LEFT]
john 1234
hanf 2345

output file [LEFT][/LEFT]
john 1 abc jonny 1234
cde jigsw 1234
efg hary 1234
hanf 2 hjk nilof 2345
klm sumpr 2345

Here the file1 has 4th field and file 2 has 2nd field common.But the problem
is when the second line has only three field in file 1 , that is where the join
and paste command fails.

Is it possible?? Please guide ?? Thanks for your guidence.

6

I will just add to simplyfy it more

file 1
1      abc   jonny      1234
       cde   jigsw      1234
       edg   hary       1234
2      hjk   nilof      2345
       klm   sumpr      2345

[/CODE]

file 2
john     1234
hanf     2345

output file  
john   1        abc   jonny       1234
                cde   jigsw       1234
                efg    hary       1234
hanf   2        hjk    nilof      2345
                klm     sumpr     2345

Here the file1 has 4th field and file 2 has 2nd field common.But the problem
is when the second line has only three field in file 1 , that is where the join
and paste command fails.

Is it possible?? Please guide ?? Thanks for your guidence.

7

Different record types/layouts in one file always causes more problems than it's worth. I think that your easiest solution would be to (1) modify your file1 so that all line contain all 4 fields - you can do this either in the program which produces file1, or by using awk; (2) use join or paste to combine the 2 files.