How to merge two files with unique values matching.?

Shell Programming and Scripting
1

I have one script as below:

#!/bin/ksh
Outputfile1="/home/OutputFile1.xls"
Outputfile2="/home/OutputFile2.xls"
InputFile1="/home/InputFile1.sql"
InputFile2="/home/InputFile2.sql"
echo "Select hobby, class, subject, sports, rollNumber from Student_Table" >> InputFile1
echo "Select rollNumber ,First_name, Last_name from Name_Table" >>InputFile2
isql -Uusername -Ppassword -Ddatabase -Sserver_name -iInputFile1 -oOutputfile1
isql -Uusername -Ppassword -Ddatabase -Sserver_name -iInputFile2 -oOutputfile2

So after running the script i have two output files OutputFile1 and OutputFile2,one feild is common between both the files which is rollNumber

OutputFile1.xls:

hobby  class  subject  sports  rollNumber
dance  11     science  cricket   1
dance  12     science  cricket   3
dance  10     science  cricket   1
dance  11     science  cricket   2
dance  11     science  cricket   1
dance  11     science  cricket   2

OutputFile2.xls:

rollNumber First_name          last_name
1               sonali           kumari
2               anni             Das
3               Rini             kumari

Now i want a third file which merges both the OutputFile1 and OutputFile2 with the unique rollNumbers and gets the below output:

hobby  class  subject  sports  rollNumber   First_name        last_name
dance  11     science  cricket   1              sonali        kumari
dance  12     science  cricket   3              Rini          kumari        
dance  10     science  cricket   1              sonali        kumari
dance  11     science  cricket   2              anni          Das
dance  11     science  cricket   1              sonali        kumari
dance  11     science  cricket   2              anni          Das

So it matches the second file with the first file and finds the unique number and merges the first_name and last_name with the appeopriate roll number.

2

Hello Sharma331,

Following may help you in same.

awk 'FNR==NR{A[$1]=$2 OFS $3;next} ($5 in A){print $0 OFS A[$5]}' outputfile2.xls OFS="\t" outputfile1.xls

Output will be as follows.

hobby  class  subject  sports  rollNumber       First_name last_name
dance  11     science  cricket   1      sonali kumari
dance  12     science  cricket   3      Rini kumari
dance  10     science  cricket   1      sonali kumari
dance  11     science  cricket   2      anni Das
dance  11     science  cricket   1      sonali kumari
dance  11     science  cricket   2      anni Das

Thanks,
R. Singh

3

Merging the two output files on the shell level should not be considered the smartest approach as SQL has the means - and is designed to do exactly that - to do the merge immediately in a query. Try like (my SQL has become somewhat rusty, so there may be smarter queries):

Select hobby, class, subject, sports, S.rollNumber, First_name, Last_name 
from Student_Table S, Name_Table N where S.rollnumber=N.rollnumber
4

Hi Rudi,

Actually the query which i provided was dummy query in my actual query i am using the output of the first query in to the second one. The exact query is given below:

select substr(OBJECT_NAME,3,9) as NUMBER, ID,
CREATION_DATE, CONTENT_TYPE, CREATOR_NAME from DOCUMENT where 
CONTENT_TYPE in ('mp3','ra', 'wma', 'wav', 'wave') and 
CREATION_DATE between 
'2014-05-01 00:00:00.0' and '2014-05-31 23:59:59.0'


select pin_num, frst_nme, last_nme from rsrc_t where pin_num in (pin number list from above query which is CREATOR_NAME )

Note:- both the queries are done from two different servers as well as DBs (one being DB2 and one Sybase)