Visit Our UNIX and Linux User Community


awk NR==FNR compare 2 files produce a 3rd


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk NR==FNR compare 2 files produce a 3rd
# 1  
Old 03-16-2009
awk NR==FNR compare 2 files produce a 3rd

hi, i have two files, both with 3 columns, the 3rd column has common values between the two files and i want to produce a 3rd file with 4 columns.

file 1

a, ,b c

file 2

a, b ,d

I want to compare the 3rd value and if a match print to file 3 with the 3 columns from the first file plus the entitlement column from file 2 added as 4th column , delimited.

file 3

a, b, c, d

I have been trying things like this borrowed off other posts but its outputing completely wrong, ive never really used awk that much

awk 'NR==FNR{x[$3]=$2;next}$3 in x&&$2=x[$3]' OFS="," FS="," file2 file1

Last edited by borderblaster; 03-18-2009 at 10:04 AM..
# 2  
Old 03-16-2009
Code:
nawk -F, '
{ if ( FILENAME == "file1" )
        _[$3]=$0
        else
        _[$3]=_[$3]","$1 }
END { for ( i in _ ) print _[i] }
' file1 file2

# 3  
Old 03-17-2009
When I run this I get a syntax error, I only have awk on the server as well

awk -F, '{ if ( FILENAME == "file1.txt" )_[$3]=$0 else _[$3]=_[$3]","$1 }END { for ( i in _ ) print _[i] }' file1.txt file2.txt
awk: cmd. line:1: { if ( FILENAME == "file.txt" )_[$3]=$0 else _[$3]=_[$3]","$1 }END { for ( i in _ ) print _[i] }
awk: cmd. line:1: ^ syntax error
# 4  
Old 03-17-2009
Try this:

Code:
awk -F, 'NR==FNR{a[$3]=$1;next}$3 in a {$4=a[$3]}1' OFS="," file2 file1

Use nawk or /usr/xpg4/bin/awk on Solaris if you get errors.

Regards
# 5  
Old 03-17-2009
Code:
$ cat a.txt
cust1, ac1, 100
cust2, ac2, 200
$ cat b.txt
100000, cust1, 100
2000000, cust2, 200
$ join -a 1 -a 2 -1 3 -2 3 -t"," -o 1.1 1.2 1.3 2.1 a.txt b.txt
cust1, ac1, 100,100000
cust2, ac2, 200,2000000

Mind you, both files need to be sorted on the key column (stbid). In this case a.txt and b.txt are already sorted.

Last edited by rikxik; 03-17-2009 at 07:00 AM.. Reason: sort requirement
# 6  
Old 03-17-2009
franklin, ive realised i need to compare two columns, i.e. if both customer id and account id match in both files then append packageid to the end of the relevent line in the first file. Can that be done?
# 7  
Old 03-17-2009
Post an example of both files. You have not specify a colomn for the account id in your second file.

Regards

Previous Thread | Next Thread
Test Your Knowledge in Computers #607
Difficulty: Medium
In MySQL 8.0 CREATE TABLE t1 (c1 JSON); is not a valid statement.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk: Assigning a variable to be the value of FNR at a certain line

Sorry for the probably strangely worded title but I don't really know how else to put it. Background context: Post processing LAMMPS simulation data. tl;dr: I'm making two spheres collide, every defined timestep the simulation outputs a bunch of data including total energy of the particles,... (10 Replies)
Discussion started by: ThomasP
10 Replies

2. Shell Programming and Scripting

Explanation of FNR in this awk script

To merge mutiple *.tab files as: file1.tab rs1 A A rs2 A A rs3 C C rs4 C Cfile2.ind rs1 T T rs2 T T rs3 G G rs4 G Gand file3.tab rs1 B B rs2 B B rs3 L L rs4 L LOutput: file1.tab file2.tab file3.tab AA TT BB AA TT BB CC GG LL CC GG ... (4 Replies)
Discussion started by: yifangt
4 Replies

3. Shell Programming and Scripting

awk --> selective printout with FNR

Hi everybody! need some awk-support. i want a line-selective printout of a file. wat i normally will do with ... awk ' FNR==8' sample.txt But now i need the data from line 8, 10 and the following data from line13 to 250 wich is not end of the file. I tried allready to combine it but without... (2 Replies)
Discussion started by: IMPe
2 Replies

4. Shell Programming and Scripting

Tip: alternative for NR==FNR in awk

Example: $ cat file1 2 3$ cat file2 1 2 3 4 5 6The following awk script works like a charm, NR==FNR is true for file1, the remainder runs for file2: awk ' NR==FNR {A; next} ($1 in A) ' file1 file2 2 3Now have an empty file1: >file1and run the awk script again. The result is empty... (8 Replies)
Discussion started by: MadeInGermany
8 Replies

5. Shell Programming and Scripting

Compare 3rd column in 2 files

I have the following 2 files. File 1 08FB,000192602673,10000000c9a6b240 0121,000192602673,20000025b550101f 0121,000192602673,20000025b550100f 08FA,000192602673,10000000c9a6b240 File 2 18F2,000195702363,10000000c9a6b240 18F3,000195702363,10000000c9a6b240... (2 Replies)
Discussion started by: kieranfoley
2 Replies

6. Shell Programming and Scripting

awk to compare 2nd and 3rd field and print the differences

need a one liner to compare 2nd and 3rd field and print values that are not matched in 2nd field Input col 2 col 3 1.1.1.1 11.11.11.11 8.8.8.8 0.0.0.0 3.3.3.3 2.2.2.2 7.7.7.7 3.3.3.3 5.5.5.5 1.1.1.1 4.4.4.4 6.6.6.6 9.9.9.9 output 7.7.7.7 ... (12 Replies)
Discussion started by: chidori
12 Replies

7. Shell Programming and Scripting

Awk FNR==NR question

awk -F'' 'FNR==NR {a=$2; next} {$1=a} 1' $useralias ${entries} >> ${entries}_2 Hi, Is there anyway to alter this command so that if it does not find a match it will just leave the line alone instead of replacing what it doesn't find with a blank space? (4 Replies)
Discussion started by: Jazmania
4 Replies

8. UNIX for Dummies Questions & Answers

awk NR==FNR output control

Hi Guys, I have two files: f1: A B C D E F G H f2: A X Y Z f1 has 48000 lines, and f2 has 68. I have been matching f1 $3 to f2 $1, and getting f3: A A B C D E F G I would like f3 too look like this: A X Y Z A B C D E F G (2 Replies)
Discussion started by: heecha
2 Replies

9. Shell Programming and Scripting

Shell script that will compare two config files and produce 2 outputs 1)actual config file 2)report

Hi I am new to shell scripting. There is a requirement to write a shell script to meet follwing needs.Prompt reply shall be highly appreciated. script that will compare two config files and produce 2 outputs - actual config file and a report indicating changes made. OS :Susi linux ver 10.3. ... (4 Replies)
Discussion started by: muraliinfy04
4 Replies

10. Shell Programming and Scripting

Awk: different between NR and FNR

As I know: FNR: The ordinal number of the current record in the current file. NR: The ordinal number of the current record from the start of input. I don't understand really differency between NR and FNR. Who can explain it for me? And give me an example. Thanks (1 Reply)
Discussion started by: anhtt
1 Replies

Featured Tech Videos