Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merge Two Files based on First column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge Two Files based on First column
# 1  
Old 04-09-2009
Merge Two Files based on First column
Hi,

I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like:

File one:
Code:
110001   abc  efd
110002   fgh   dfg
110003   jdo   jsw

File Two:
Code:
110001  abc hty
110001  jko  lop
110002  jhk  klo
110003  jui   poi

Output File:
Code:
110001   abc  efd
110001   abc  hty
110001   jko   lop
110002   fgh   dfg
110002   jhk   klo
110003   jdo   jsw
110003   jui    poi

First column of first file is always unique but there are multiple entries in second file for first column.And we need to write all entries matching with first column of both files in third file.


I tried to see other solution given to merge file within the forum but could not get any good match. I would really appreciate if anyone can help me out.

Regards,
Neeraj
Last edited by Yogesh Sawant; 04-09-2009 at 07:08 AM.. Reason: added code tags
# 2  
Old 04-09-2009
try:
Code:
sort file1 file2

# 3  
Old 04-09-2009
Thanks for your reply.But the output is not coming in correct order. i tried with different option like -r but still it is not coming in correct order , i want.

Thanks
Neeraj
# 4  
Old 04-09-2009
try sort -t' ' -k1,1 file1 file2
# 5  
Old 04-09-2009
Thanks buddy. But still the order is not correct. The lines from both files got together correctly but order is not correct.

for example:

input1:
Code:
123  abc
456  def

input2:
Code:
123  ijk
123  ijk
456  lmn
456  lmn

output using sort command is :
Code:
456  def
456  lmn
456  lmn
123  abc
123  ijk
123  ijk

But i need output as:
Code:
123  abc
123  ijk
123  ijk
456  def
456  lmn
456  lmn

Hope you understand my point.

Thanks
Neeraj
Last edited by Yogesh Sawant; 04-10-2009 at 04:34 AM.. Reason: added code tags
# 6  
Old 04-09-2009
seems to be working fine for me...

Code:
$ cat file1
123 abc
456 def
 
$ cat file2
123 ijk
123 ijk
456 lmn
456 lmn
 
$ sort file1 file2
123 abc
123 ijk
123 ijk
456 def
456 lmn
456 lmn

$ sort -r file1 file2
456 lmn
456 lmn
456 def
123 ijk
123 ijk
123 abc

# 7  
Old 04-09-2009
Thanks buddies for your responses.I got the real mistake i have made.Actually why you got the correct order not me , it is because of i have not mentioned the real files to you.i just took dummy example to make my point.

real scenario is like this:

input1:
Code:
1000349856	0001950	
1000350045	0001950

input2:
Code:
1000349856	00001	
1000350045	00001	
1000350045	00002

Output after using "sort input1 input2 " is:
Code:
1000349856	00001
1000349856	0001950
1000350045	00001	
1000350045	00002
1000350045	0001950

but it should be:
Code:
1000349856	0001950
1000349856	00001
1000350045	0001950
1000350045	00001	
1000350045	00002

This is , i believe due to sorting of second column also. But i need to merge files only based on first column only.

Thanks again

Neeraj
Last edited by Yogesh Sawant; 04-10-2009 at 04:35 AM.. Reason: added code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Linux

Merge two files based on matching criteria

Hi, I am trying to merge two csv files based on matching criteria: File description is as below : Key_File : 000|ÇÞ|Key_HF|ÇÞ|Key_FName 001|ÇÞ|Key_11|ÇÞ|Sort_Key22|ÇÞ|Key_31 002|ÇÞ|Key_12|ÇÞ|Sort_Key23|ÇÞ|Key_32 003|ÇÞ|Key_13|ÇÞ|Sort_Key24|ÇÞ|Key_33 050|ÇÞ|Key_15|ÇÞ|Sort_Key25|ÇÞ|Key_34... (3 Replies)
Discussion started by: PK29
3 Replies

2. Shell Programming and Scripting

awk - Merge two files based on one key

Hi, I am struggling with the an awk command to merge two files based on a common key. I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended File1 COL1|COL2|COL3|COL4|COL5|COL6|COL7... (3 Replies)
Discussion started by: Ads89
3 Replies

3. UNIX for Dummies Questions & Answers

How to merge two tables based on a matched column?

Hi, Please excuse me , i have searched unix forum, i am unable to find what i expect , my query is , i have 2 files of same structure and having 1 similar field/column , i need to merge 2 tables/files based on the one matched field/column (that is field 1), file 1:... (5 Replies)
Discussion started by: karthikram
5 Replies

4. Shell Programming and Scripting

Merge files based on columns

011111123444 1234 1 20000 011111123444 1235 1 30000 011111123446 1234 3 40000 011111123447 1234 4 50000 011111123448 1234 3 50000 File2: 011111123444,Rsttponrfgtrgtrkrfrgtrgrer 011111123446,Rsttponrfgtrgtr 011111123447,Rsttponrfgtrguii 011111123448,Rsttponrfgtrgtjiiu I have 2 files... (4 Replies)
Discussion started by: vinus
4 Replies

5. Shell Programming and Scripting

Merge files based on the column value

Hi Friends, I have a file file1.txt 1|ABC|3|jul|dhj 2|NHU|4|kil|eu 3|hjd|34|hfd|43 file2.txt 1||3|KING|dhj 2|NHU||k| 3|hjd|34|hd|43 i want to merge file1.txt file2.txt based on the column null values in file2.txif there are any nulls in column values , (5 Replies)
Discussion started by: i150371485
5 Replies

6. Shell Programming and Scripting

Help with merge two file based on similar column content

Input file 1: A1BG A1BG A1BG A1CF A1CF BCAS BCAS A2LD1 A2M A2M HAT . . Input file 2: A1BG All A1CF TEMP (5 Replies)
Discussion started by: perl_beginner
5 Replies

7. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

8. Shell Programming and Scripting

merge two two txt files into one file based on one column

Hi, I have file1.txt and file2.txt and would like to create file3.txt based on one column in UNIX Eg: file1.txt 17328756,0000786623.pdf,0000786623 20115537,0000793892.pdf,0000793892 file2.txt 12521_74_4.zip,0000786623.pdf 12521_15_5.zip,0000793892.pdf Desired Output ... (5 Replies)
Discussion started by: techmoris
5 Replies

9. Shell Programming and Scripting

merge rows based on a common column

Hi guys, Please guide me if you have a solution to this problem. I have tried paste -s but it's not giving the desired output. I have a file with the following content- A123 box1 B345 bat2 C431 my_id A123 service C431 box1 A123 my_id I need two different outputs- OUTPUT1 A123... (6 Replies)
Discussion started by: smriti_shridhar
6 Replies

10. Shell Programming and Scripting

Merge files based on key

Hi Friends, Can any one help me with merging these file based on two columns : File1: A|123|99|SAMS B|456|95|GEORGE D|789|85|HOVARD File2: S|123|99|NANcY|6357 S|123|99|GREGRO|83748 A|456|95|HARRY|827|somers S|456|95|ANTONY|546841|RUDOLPH|7263 B|456|95|SMITH|827|BOISE STATE|834... (3 Replies)
Discussion started by: sbasetty
3 Replies
Login or Register to Ask a Question