Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk help or any other tool to sort between two files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk help or any other tool to sort between two files
# 1  
Old 01-31-2013
awk help or any other tool to sort between two files
Experts Good day,

I want to sort two files f1 & f2 to matching with f1's first field with f2's 3rd field like to get in a result file :
I tried with join but getting wrong result, I think there must be something with awk or other unix tool:




cat f1
Code:
MYQCI63   srvcmi12
D7QDI     srvcmi13
D7QCM     srvcmi12
CFLQRC    srvcmi14
FDQCI48   srvcmi12
FDQCI52   srvcmi12
KUQCM     srvcmi12
KUQQU     srvcmi12
QIQCI81   srvcmi12
QOQDI     srvcmi12
QOQCM     srvcmi15
UAQQU     srvcmi12
TR7FUDR   srvcmi12
TRQFUDR   srvcmi16
TRQFUFQ   srvcmi12


cat f2
Code:
10.8.40.12    srvenb9p        MYQCI63
10.8.40.21    srvenbh3        D7QDI
10.8.40.137   srvenbh2        D7QCM
10.8.40.38    srvenbbi        DFLQR11
10.8.40.40    srvenb9h        HDQCI52
10.8.40.241   srvenbah        IUQCM
10.8.40.23    srvenbbi        CFLQRC
10.8.40.21    srvenb9y        FDQCI48
10.8.40.39    srvenb9y        GDQCI44
10.8.40.34    srvenb9h        FDQCI52
10.8.40.201   srvenbah        KUQCM
10.8.40.88    srvenb9q        KUQQU
10.8.40.15    srvenbay        QIQCI81
10.8.40.13    srvenbaj        QOQDI
10.8.40.17    srvenban        QOQCM
10.8.40.89    srvenb9y        GDQCI45
10.8.40.38    srvenb9q        UAQQU
10.8.40.74    srvenb8d        TR7FUDR
10.8.40.28    srvenbst        TRQFUDR
10.8.40.221   srvenb66        TRQFUFQ


The output to be look like this: (taken only first 5 lines for example).
Code:
10.8.40.12    srvenb9p        MYQCI63	MYQCI63   srvcmi12
10.8.40.21    srvenbh3        D7QDI	D7QDI     srvcmi13
10.8.40.137   srvenbh2        D7QCM	D7QCM     srvcmi12
10.8.40.23    srvenbbi        CFLQRC	CFLQRC    srvcmi14
10.8.40.21    srvenb9y        FDQCI48	FDQCI48   srvcmi12


Thanks a lot,
# 2  
Old 01-31-2013
Hi, this is pretty standard stuff, if you use the search function of unix.com:
Code:
awk 'NR==FNR{A[$3]=$0; next} {$0=A[$1] "\t" $0}1' f2 f1

This User Gave Thanks to Scrutinizer For This Post:
rveri
# 3  
Old 01-31-2013
Scrutinizer, this works perfectly awk , thanks

Earlier I was trying with join, but it was giving few matches only, so wondered ,
Code:
join -j1 1 -j2 3 -o 2.1 2.2 2.3 1.1 1.2 f1 f2

I dont know whats wrong with this one, but it produces few lines only n output instead of all matches. Thanks for the help.

---------- Post updated at 02:08 PM ---------- Previous update was at 02:02 PM ----------

This works ,
Code:
awk 'NR==FNR{A[$3]=$0; next} {$0=A[$1] "\t" $0}1' f2 f1

Thank you.

---------- Post updated at 02:50 PM ---------- Previous update was at 02:08 PM ----------

Scrutinizer,
Unable to understand this part, could you please explain,:

Code:
awk 'NR==FNR{A[$3]=$0

We are matching A[$3] to $1 of file f1 , but you have mentioned $0 , . I am not getting it.
# 4  
Old 01-31-2013
Quote:
Originally Posted by rveri
[..]
Scrutinizer,
Unable to understand this part, could you please explain,:

Code:
awk 'NR==FNR{A[$3]=$0

We are matching A[$3] to $1 of file f1 , but you have mentioned $0 , . I am not getting it.
This means: When reading the first file ( only then is FNR==NR ) , Create an array element in associative array "A" with index "$3" and content $0.

--
Join, try:
Code:
join -1 1 -2 3 -o "2.1 2.2 0 0 1.2" <(sort -b f1) <(sort -bk3 f2)

The <( .. ) construct works in recent bash and ksh93. Otherwise you would need to use intermediate files...
# 5  
Old 01-31-2013
Quote:
Originally Posted by rveri
Earlier I was trying with join, but it was giving few matches only, so wondered ,
Code:
join -j1 1 -j2 3 -o 2.1 2.2 2.3 1.1 1.2 f1 f2

I dont know whats wrong with this one, but it produces few lines only n output instead of all matches. Thanks for the help.
That is because the files f1 and f2 need to be sorted before being fed to join...
Code:
sort -k1,1 f1 > f1.out && sort -k3,3 f2 | join -j1 1 -j2 3 -o 2.1 2.2 2.3 1.1 1.2 f1.out -

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Gnu tool; sed awk echo etc to prepend or append string to a file

Looking to add text to a file, example File example; nodegroups: check-hosts: L@host.domain.com,host2.domain.com,host3.domain.com I need to take a file with a one line list of hosts separated by commas host.domain.com,host2.domain.com,host3.domain.comand prepend the string " ... (6 Replies)
Discussion started by: bash_in_my_head
6 Replies

2. Shell Programming and Scripting

sort the files based on timestamp and execute sorted files in order

Hi I have a requirement like below I need to sort the files based on the timestamp in the file name and run them in sorted order and then archive all the files which are one day old to temp directory My files looks like this PGABOLTXML1D_201108121235.xml... (1 Reply)
Discussion started by: saidutta123
1 Replies

3. UNIX for Advanced & Expert Users

Script to sort the files and append the extension .sort to the sorted version of the file

Hello all - I am to this forum and fairly new in learning unix and finding some difficulty in preparing a small shell script. I am trying to make script to sort all the files given by user as input (either the exact full name of the file or say the files matching the criteria like all files... (3 Replies)
Discussion started by: pankaj80
3 Replies

4. UNIX for Dummies Questions & Answers

Command-line tool for .gif files

Hello! I'm searching for a little (as basic as possible) command -line tool for viewing gif animations. I found gThumb and animate (imagemagick), but they both have a little problem... gthumb: no possibility of changing the background when viewing in fullscreen animate: no possibility of... (0 Replies)
Discussion started by: al0x
0 Replies

5. Shell Programming and Scripting

noob question - is awk the tool to clean dirty text files?

Hi, nevermind. I think I've found the answer. It appears I was looking for index, match, sub, and gsub. I want to write a shell script that will clean the html out of a bunch of files and format the data for import into excel. Awk seems like a powerful tool, but it seems oriented to... (1 Reply)
Discussion started by: yogert909
1 Replies

6. Shell Programming and Scripting

Need help with awk tool - finding text between two patterns

This is regarding using awk tool to find lines matching between 2 patterns. cat file | awk '/pat1/,/pat2/' But it's not working as expected in the following case. If pat1 also comes after pat2 then it's matching whole file after pat1. e.g. # > cat -n file 1 First line... (3 Replies)
Discussion started by: anand_bh
3 Replies

7. Shell Programming and Scripting

AWK or KSH : Sort, Group and extract from 3 files

Hi, I've the following two CSV files: File1.csv File2.csv Class,Student# Student#,Marks 1001,6001 6002,50 1001,6002 6001,60 1002,7000 ... (3 Replies)
Discussion started by: Matrix2682
3 Replies

8. Shell Programming and Scripting

Shell Programing with awk Tool

I'm working on a program in with shell programming and it needs to be able to delete the contents of files in the Home directory on certain days of the week.(like tuesday). Can anyone help me with this? Thanks in advance, Taffy. (7 Replies)
Discussion started by: Taffy
7 Replies

9. UNIX for Dummies Questions & Answers

download files from direct links tool?

Hello all I wander of there is small utility that gives me the possibility to download direct links to specific folder say if i have http://www.blah.com/foo.java and I like to download the foo.java without opening the browser and such... (2 Replies)
Discussion started by: umen
2 Replies
Login or Register to Ask a Question