Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

AWK compare/merge

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK compare/merge
# 1  
Old 03-15-2011
AWK compare/merge
File1

Code:
 
2917,`0722,RDF1+TDEV,90(6),33,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`0781,RDF1+TDEV,100(5),33,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e

File2
Code:
 
2917,`0722,RDF1+TDEV,90(6),03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`0781,RDF1+TDEV,100(5),03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A2,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A6,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e

Output needed
Code:
 
2917,`0722,RDF1+TDEV,90(6),33,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`0781,RDF1+TDEV,100(5),33,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A2,2-way,44,,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A6,2-way,44,,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e

Basically we need to this
compare $7 from 1st file to $6 in 2nd
Append the line if $7 ( file1) = $6 (file2) but $2 (file1) != $2 (file2)

Also add a "," to replace a missing column in file2 to make sure the output file has same columns in all lines -- thx
# 2  
Old 03-15-2011
Code:
awk 'NR==FNR{$4=$4",";R[$6,$2]=$0; next}{delete R[$7,$2]}1;END{for(l in R) print R[l]}' FS="," OFS="," file2 file1

# 3  
Old 03-15-2011
Thx for quick reply ... one issue ,output also have lines from file2 where $7 from file1 does not match $6 from file2

for example if file2 is
Code:
 
2917,`0722,RDF1+TDEV,90(6),03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`0781,RDF1+TDEV,100(5),03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A2,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A6,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03AB,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c99c6e88,BL_db00p01e

i am getting this output
Code:
 
2917,`0722,RDF1+TDEV,90(6),33,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`0781,RDF1+TDEV,100(5),33,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03AB,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c99c6e88,BL_db00p01e
2917,`03A2,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e
2917,`03A6,2-way,44,03E:0_12E:0,10000000c96c4af3_10000000c96c6e88,BL_db00p01e

There should not be a line with 03AB
# 4  
Old 03-15-2011
Code:
awk 'NR==FNR{$4=$4",";R[$6,$2]=$0;next}
{H[$7];delete R[$7,$2]}1;
END{for(l in R)if(split(l,h,SUBSEP)&&h[1] in H)print R[l]}' FS="," OFS="," file2 file1

# 5  
Old 03-16-2011
Thanks a lot Chubler ..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare and merge two big CSV files

Hi all, i need help. I have two csv files with a huge amount of data. I need the first column of the first file, to be compared with the data of the second, to have at the end a file with the data not present in the second file. Example File1: (only one column) profile_id 57036226... (11 Replies)
Discussion started by: SirMannu
11 Replies

2. Shell Programming and Scripting

Compare two files and merge into third

Hello: Newbie with Awk. Trying to compare two files and merge data based on CID. Please see the input file format and desired output. Any help is appreciated. TIA Input File1 CID1 --- TYP1 --- DCN1 --- INDATE1 --- IN-DATA1 CID2 --- TYP2 --- DCN2 --- INDATE2 --- IN-DATA2 CID3 ---... (6 Replies)
Discussion started by: wincrazy
6 Replies

3. Shell Programming and Scripting

Compare and Merge files

Hi All, I have two different files as shown below separated by a "|". I need to compare the first column from both the files and if they match merge both the columns. File 1 "S00172012"|"CHRONIC RENAL FAILURE"|""|"I" "S00159962"|"SUBENDO INFRC-INIT EPISD"|""|"I" "S00255303"|"BENIGN... (6 Replies)
Discussion started by: nua7
6 Replies

4. Shell Programming and Scripting

Another awk merge

File1 01C6,039E,RDF1+TDEV,120(2),1507_3RAID5,33 01C8,0E46,RDF1+TDEV,200(8),1507_3RAID5,33 01D4,045E,RDF1+TDEV,8,1507_3RAID5,33 01D5,045F,RDF1+TDEV,8,1507_3RAID5,33 File2 01C6,AA,BB,CC 01C8,,,NN 01D5,BB,KK, 0122,GG,, Ouput ... (2 Replies)
Discussion started by: greycells
2 Replies

5. Shell Programming and Scripting

Merge files using AWK

I want to merge data from 2nd file to 1st file based on 1st column File1 ==== data1,12,comp1 data1,13,comp2 data3,14,, File2 ==== data1,11,host1,lit data2,11,host2,lit3 data3,11,host3,lit4 Required Ouput (5 Replies)
Discussion started by: greycells
5 Replies

6. Shell Programming and Scripting

Shell Scripting: Compare pattern in two files and merge the o/p in one.

one.txt ONS.1287677000.820.log 20Oct2010 ONS.1287677000.123.log 21Oct2010 ONS.1287677000.456.log 22Oct2010 two.txt ONS.1287677000.820.log:V AC CC EN ONS.1287677000.123.log:V AC CC EN ONS.1287677000.820.log:V AC CC EN In file two.txt i have to look for pattern which column one... (17 Replies)
Discussion started by: saluja.deepak
17 Replies

7. Shell Programming and Scripting

merge with awk

Hi all, I have got the following merge problem file a a 0001 something a 0002 something a 0003 something a 0004 something file b b 0001 1111111111111 b 0002 2222222222222 b 0003 3333333333333 b 0004 4444444444444 File c should look like this (8 Replies)
Discussion started by: stinkefisch
8 Replies

8. UNIX for Dummies Questions & Answers

compare columns from 2 files and merge

Dear all, Being new to Unix i have a problem. I have 2 files: File 1: 118,1,0,2,3,0,5,0.3,0,0.3,0.6,1 118,2,1,2,2,0,5,0.4,0,0.4,0.4,1 118,4,2,0,3,0,5,0.7,0,0.3,0.6,1 118,6,4,1,0,0,5,0.8,0,0.2,0,1 File 2: 118,1,BFGL-NGS-109695,3610326,0,18,1,0.556,0.389,0.056,0.25,0.8183... (2 Replies)
Discussion started by: samwilkinson
2 Replies

9. Shell Programming and Scripting

compare the column from 3 files and merge that line

I have 3 file, each of has got 80000 records. file1.txt ----------------------- ABC001;active;modify;accept; ABC002;notactive;modify;accept; ABC003;notactive;no-modify;accept; ABC004;active;modify;accept; ABC005;active;no-modify;accept; file2.txt ---------------------------... (8 Replies)
Discussion started by: ganesh_mak
8 Replies

10. Shell Programming and Scripting

Compare two files and merge columns in a third

Hi, I'm working with snmp, with a little script I'm able to obtain from a switch a list with a couple of values with this format Port Mac 1 00:0A:0B:0C:0D:0E .... (hundred of entries) Now with a simple arp on a router I am able to obtain another list 00:0A:0B:0C:0D:0E... (20 Replies)
Discussion started by: CM64
20 Replies
Login or Register to Ask a Question