Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

How to merge two files with unique values matching.?

Shell Programming and Scripting


Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 11-06-2014   -   Original Discussion by Sharma331
Sharma331's Unix or Linux Image
Sharma331 Sharma331 is offline
Registered User
 
Join Date: Jun 2014
Last Activity: 20 January 2016, 8:23 AM EST
Location: chennai, India
Posts: 52
Thanks: 15
Thanked 1 Time in 1 Post
How to merge two files with unique values matching.?

I have one script as below:


Code:
#!/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:


Code:
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:


Code:
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:


Code:
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.
Sponsored Links
    #2  
Old Unix and Linux 11-06-2014   -   Original Discussion by Sharma331
RavinderSingh13's Unix or Linux Image
RavinderSingh13 RavinderSingh13 is offline Forum Advisor  
Registered User
 
Join Date: May 2013
Last Activity: 24 February 2018, 1:51 AM EST
Location: Chennai
Posts: 2,697
Thanks: 601
Thanked 1,282 Times in 1,152 Posts
Hello Sharma331,

Following may help you in same.



Code:
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.


Code:
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
Sponsored Links
    #3  
Old Unix and Linux 11-06-2014   -   Original Discussion by Sharma331
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 23 February 2018, 3:15 PM EST
Location: Aachen, Germany
Posts: 12,167
Thanks: 371
Thanked 3,752 Times in 3,447 Posts
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):

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

    #4  
Old Unix and Linux 11-06-2014   -   Original Discussion by Sharma331
Sharma331's Unix or Linux Image
Sharma331 Sharma331 is offline
Registered User
 
Join Date: Jun 2014
Last Activity: 20 January 2016, 8:23 AM EST
Location: chennai, India
Posts: 52
Thanks: 15
Thanked 1 Time in 1 Post
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:


Code:
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)
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
How to merge two or more fields from two different files where there is non matching column? karthikram Shell Programming and Scripting 3 09-06-2013 12:08 PM
Grep to find matching patern and return unique values Siva SQL UNIX for Dummies Questions & Answers 5 05-03-2013 10:38 AM
Merge two files matching columns fhluque Shell Programming and Scripting 2 02-27-2013 10:58 AM
Compare values in two files. For matching rows print corresponding values from File 1 in File2. Santoshbn Shell Programming and Scripting 10 05-21-2012 12:23 PM
Need to find only unique values for a given tag across the files sudheshnaiyer UNIX for Dummies Questions & Answers 8 09-03-2007 01:53 AM



All times are GMT -4. The time now is 04:08 AM.