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.?

👤 Login to reply

    #1  
Old 11-06-2014
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 11-06-2014
RavinderSingh13 RavinderSingh13 is offline Forum Advisor  
Registered User
 
Join Date: May 2013
Last Activity: 21 July 2018, 10:58 AM EDT
Location: Chennai
Posts: 2,750
Thanks: 623
Thanked 1,316 Times in 1,183 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 11-06-2014
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 21 July 2018, 12:24 PM EDT
Location: Aachen, Germany
Posts: 13,082
Thanks: 452
Thanked 4,017 Times in 3,693 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 11-06-2014
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
👤 Login to reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 11:08 AM
Grep to find matching patern and return unique values Siva SQL UNIX for Dummies Questions & Answers 5 05-03-2013 09:38 AM
Merge two files matching columns fhluque Shell Programming and Scripting 2 02-27-2013 09: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 11:23 AM
Need to find only unique values for a given tag across the files sudheshnaiyer UNIX for Dummies Questions & Answers 8 09-03-2007 12:53 AM



All times are GMT -4. The time now is 05:07 PM.

Unix & Linux Forums Content Copyright©1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password





Not a Forum Member?
Forgot Password?