Merge files using AWK


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge files using AWK
# 1  
Old 02-15-2011
Merge files using AWK

I want to merge data from 2nd file to 1st file based on 1st column

Code:
 
File1
====
data1,12,comp1
data1,13,comp2
data3,14,,

Code:
 
File2
====
data1,11,host1,lit
data2,11,host2,lit3
data3,11,host3,lit4

Code:
 
Required Ouput
===========
data1,12,comp1,11,host1,lit
data1,13,comp2,11,host1,lit
data3,14,,11,host3,lit4
data2,,,11,host2,lit3

File1 is suppose to have 3 columns( one or more could be empty)
I don't want to merge rows with same column 1 to be merged in the same line( in case of data1 in the example )

Thanks
Mickey
# 2  
Old 02-15-2011
nawk -f grey.awk file2 file1

grey.awk:
Code:
BEGIN {
  FS=OFS=","
}
FNR==NR {for(i=2;i<=NF;i++) f2[$1]=($1 in f2)?f2[$1] OFS $i:$i;next}
$1 in f2{
   $0=$0 OFS f2[$1]
   proc[$1]
   print
}
END {
  for (i in f2)
    if  (!(i in proc))
      print i OFS OFS OFS  f2[i]
}


Last edited by vgersh99; 02-15-2011 at 06:40 PM.. Reason: tweaked the logic....
# 3  
Old 02-15-2011
In case AWK isn't a hard requirement, you can probably accomplish your goal with the join command (which requires that the files be pre-sorted on the join field, just like your sample data):
Code:
join -a1 -a2 -t, file1 file2

Regards,
Alister
# 4  
Old 02-16-2011
thanks vgresh , works fine

will give join a try too ..not sure if it will work with the conditions i got ,,thanks though

mickey
# 5  
Old 02-16-2011
Code:
awk -F, 'NR==FNR{p=$1;sub(p FS,x);A[p]=$0;next}$0=$0 A[$1]' OFS=, file2 file1

# 6  
Old 03-03-2011
@vgresh99 .... how do i modify urs to get the info from file1 as it is .. even if column 1 from file 1 does not exist in file2 ...thx
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge columns from two files using awk

I have two csv files : say a.csv, b.csv a.csv looks like this : property1,property2,100 property3,property4,200 In a.csv, the combination of column1 and column2 will be unique b.csv looks like this property1,property2, 300, t1 property1,property2, 400,t2 property3, property4,800,t1... (2 Replies)
Discussion started by: Lakshmikumari
2 Replies

2. Shell Programming and Scripting

Merge two files on awk/shell

Hi, i have two files like these: FILE 1 00:0f:35:1b:0c:00 1402691094.750049000 00:0f:35:1b:0c:00 1402691087.474893000 44:d3:ca:fd:a2:08 1402691091.865127000 30:e4:db:c1:df:de 1402691090.192464000 FILE 2_ 00:0F:35 Cisco Systems, Inc 30:E4:DB Cisco Systems, Inc I need a file 3, that... (5 Replies)
Discussion started by: bertiko
5 Replies

3. Shell Programming and Scripting

awk merge two files

file1 AAA3:WWW1:DDD1:XXX8:DDD2:XXX9 AAA6:WWW2:FFF1:XXX130:FFF1:XXX104:FFF1:XXX16 AAA7:WWW3:ZZZ1:XXX4:ZZZ2:XXX5:ZZZ3:XXX6:ZZZ4:XXX7file2 XXX8:EEE1:EEE2 XXX9:KKK1:KKK2 XXX130:OOO1:OOO2 XXX104:PPP1:PPP2 XXX16:RRR1:RRR1 XXX4:UUU1:UUU2 XXX5:III1:III2 XXX7:JJJ1:JJJ2Result... (2 Replies)
Discussion started by: vikus
2 Replies

4. Shell Programming and Scripting

awk match and merge with 2 files

Dear Awk experts! I have been trying to get a one liner for a match and merge operation, but having difficulties as I'm an awk newb. I always get stuck on the match and merge with 2 files. I have 2 files as follows: File 1: field 1,field 2,field 3,field 4,field 5,field 6,field 7,field... (6 Replies)
Discussion started by: landossa
6 Replies

5. Shell Programming and Scripting

merge two files with awk

I have two file like follows. I want to merge them according the first field of file1. The separator of file1 is tab, while the second one is ",". I tried some code, but can't get the results. File1: AABB 6072 28 5922 BBCC 316 147 162 CCDD 907 71 231 File2: CCDD,hTRBV12-4,hTRBJ2-3,319895... (7 Replies)
Discussion started by: xshang
7 Replies

6. Shell Programming and Scripting

Merge 2 csv files with awk

I have 2 files pipe delimted and want to merge them based on a key e.g file 1 123$aaa$yyy$zzz 345$xab$yzy$zyz 456$sss$ttt$foo 799$aaa$ggg$dee file 2 123$hhh 345$ddd 456$xxx 888$zzz so if the key is the first field, and the result should be the common key between file 1 and 2 (6 Replies)
Discussion started by: loloAix
6 Replies

7. Shell Programming and Scripting

how to merge selective content between files using awk?

Hello, I'm new to awk. I've 2 files where H stands for header and T for trailer. The number following T gives the record count in a file. file 1 looks like this: H|A|B|C 1|2|3 1|2|4 2|3|5 T|3 file 2 looks like this: H|A|B|C 4|5|6 7|8|9 T|2 Need to merge the above 2 files such... (2 Replies)
Discussion started by: skumre
2 Replies

8. Shell Programming and Scripting

merge two files into one file use awk

Hi, guys. I have one question: I have two files: passwd and shadow (the number of records in these files are not equal)the contents of them are below: passwd: ************** ftp:x:24:24: sshd:x:71:65: uucp:x:10:14: brownj:x:5005:1000: sherrys: x :5006:1000: ... ************* ... (2 Replies)
Discussion started by: daikeyang
2 Replies

9. Shell Programming and Scripting

Merge files of differrent size with one field common in both files using awk

hi, i am facing a problem in merging two files using awk, the problem is as stated below, file1: A|B|C|D|E|F|G|H|I|1 M|N|O|P|Q|R|S|T|U|2 AA|BB|CC|DD|EE|FF|GG|HH|II|1 .... .... .... file2 : 1|Mn|op|qr (2 Replies)
Discussion started by: shashi1982
2 Replies

10. Shell Programming and Scripting

merge two files using awk

Hi Guys, I wonder whether is possible to merge two files using awk. I have two files one with 7 columns and another one with 9 columns and the first column on both files is identical so will be my key to merge the files. Any ideas.Thanks in advance. Harby. (2 Replies)
Discussion started by: hariza
2 Replies
Login or Register to Ask a Question