Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

merging fields from 2 different files.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting merging fields from 2 different files.
# 1  
Old 10-30-2008
merging fields from 2 different files.
File 1

Code:
3337304	2	4	DH.ER@TORONTO.CA	20080504					04622
3337305	2	4	A@C.COM	20080504					04622
3337306	2	4	JO@NET.NET	20080504					04622
3337307	2	4	L@GMAIL.COM	20080504					05344
2479201	2	2	ORY@YAHOO.COM	20080504					05344

File 2

Code:
4	4000522	3337304	4	6269	13549	137269038	20080426	6691	1042	390287049	SA	163162	13549	0	20080426	1042	20080505	1701		       555.00	       166.50	       388.50	         0.00		00		0104956435	4	20070319				00000	100008227	390283374								
4	4000523	3337305	4	6275	13555	135101962	20080426	6691	1226	390284677	SA	465200	13555	0	20080426	1226	20080507	2016		       408.95	       123.80	       285.15	         0.00	62039	00		0104872095	1	20080426	20100426	Y	20080426	00411	100015109	390312052								
4	4000524	3337306	4	6275	13555	137274900	20080426	6691	1438	390301435	SA	465200	13555	0	20080426	1438	20080507	2018		       428.95	       169.99	       258.96	         0.00		00		0104875342	4	20080322	20100322	Y	20080426	00411	100015113	390335031								
4	4000525	3337307	4	6276	13556	137268880	20080426	6691	1056	390314433	SA	449670	13556	0	20080426	1056	20080506	1120	2	       374.95	       202.15	       172.80	         0.00		00		0104930873	1	20080426	20090426	Y	20080426	00411	100006090	390314457								
4	4000526	3337308	4	6288	13561	137269435	20080426	6691	1206	390278019	SA	139843	13561	0	20080426	1206	20080505	1600		       414.95	       203.48	       211.47	         0.00		00		0105089444	1	20080426	20090426			00000	100012928	390321420								
4	4000527	3337309	4	6289	13562	132830861	20080426	6691	1128	390279579	SA	142915	13562	0	20080426	1128	20080503	1231		       404.95	       121.49	       283.46	         0.00		00		0104864879	1	20080426	20090426	Y	20080426	00411	100010719	390298972								
4	4000528	3337310	4	6309	13575	137270406	20080426	6691	1216	390330438	SA	154470	13575	0	20080426	1216	20080504	1627		       394.98	       197.49	       197.49	         0.00		00		0105364043	1	20080426	20090426			00000	100008152	390335243								
4	4000529	3337311	4	6333	13592	137276521	20080426	6691	1422	390372742	SA	175507	13592	0	20080426	1422	20080507	1837	2	       449.95	       224.98	       224.97	         0.00		00		0105069685	4	20080425	20090425			00000	100006458	390373705								
4	4000530	3337312	4	6338	13596	137281221	20080426	6691	1557	390406853	SA	196577	13596	0	20080426	1557	20080503	1232		       779.00	       233.70	       545.30	         0.00		00		0105313921	4	20080424		Y	20080426	00411	100010313	390407361								
4	4000531	3337313	4	6361	13606	137271658	20080426	6691	1243	390331137	SA	463630	13606	0	20080426	1243	20080505	1600		       404.95	       173.98	       230.97	         0.00		00	677570985	0105238043	1	20080426	20090426	Y	20080426	00411	100017434	390343104


Attached are two files file1.txt and file2.txt. I need to add ( field #4 from file2.txt) at the end in file1.txt
by comparing field #3 and field # 1 from file1.txt to field #1 and field #3 of file2.txt.
I need to add field #4 from file2.txt for only those records whose field #3 is 1,3 or 4 and
still append other records without adding field #4 from file2.txt

Hope i am clear.

Thanks
# 2  
Old 10-30-2008
This is a frequently asked question. Search the forum for "NR==FNR" to find similar solutions.
# 3  
Old 10-30-2008
Okay, scratch that. :-) I just tried that search myself and it finds nothing, which is strange, because I'm sure there are multiple solutions like this. So I'll write another one here and tag it as a "faq". Back in a moment!
# 4  
Old 10-30-2008
Try this:

Code:
awk '
        # when FNR==NR it means we are processing the first input file,
        # in this case file2
        NR==FNR {
                # store file2 information in an array indexed by a key
                # made up of the two fields we are interested in
                file2[$1,$3]=$4
                next
        }
        # processing the second (or subsequent) files, in this case file1
        {
                # only want to append file2 data if $3 is 1, 3 or 4
                if ($3 == 1 || $3 == 3 || $3 == 4)
                        print $0,file2[$3,$1]
                else
                        print $0
        }
' file2 file1

# 5  
Old 10-31-2008
Output is exactly what i want,
one question is, the variable that gets added, can we have that tab seperated instead of space?

Thanks
# 6  
Old 11-02-2008
Yes, add the following to the invocation of awk:

Code:
awk -v OFS='\t' '

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Is there a UNIX command that can compare fields of files with differing number of fields?

Hi, Below are the sample files. x.txt is from an Excel file that is a list of users from Windows and y.txt is a list of database account. $ head -500 x.txt y.txt ==> x.txt <== TEST01 APP_USER_PROFILE USER03 APP_USER_PROFILE TEST02 APP_USER_EXP_PROFILE TEST04 APP_USER_PROFILE USER01 ... (3 Replies)
Discussion started by: newbie_01
3 Replies

2. Shell Programming and Scripting

Merging fields in CSV

Hi experts, I have a csv file which has one field (ID) repeated multiple times with corresponding other field values. I need to convert this file in a format where for a ID all other values has to be present in single field. For Eg : Here in below file ID 1 is repeated 3 times with different... (7 Replies)
Discussion started by: bharathbangalor
7 Replies

3. Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Dear List, I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

4. Shell Programming and Scripting

Merging CSV fields based on a common field

Hi List, I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So: File 1:... (1 Reply)
Discussion started by: landossa
1 Replies

5. Shell Programming and Scripting

Add fields in different files only if some fields between them match

Hi everybody (first time posting here) I have a file1 that looks like > 1,101,0.1,0.1 1,26,0.1,0.1 1,3,0.1,0.1 1,97,0.5,0.5 1,98,8.1,0.218919 1,99,6.2,0.248 2,101,0.1,0.1 2,24,3.1,0.147619 2,25,23.5,0.559524 2,26,34,0.723404with 762 lines.. I have another 'similar' file2 > ... (10 Replies)
Discussion started by: murpholinox
10 Replies

6. Shell Programming and Scripting

Merging two files with same name

Hello all, I have limited experience in shell scripting. Here goes my question: I have two directories that have same number of files with same file names i.e. consider 2 directories A and B. Both directories have files 1.txt, 2.txt...... I need to merge the file 1.txt of A with file 1.txt... (5 Replies)
Discussion started by: jaysean
5 Replies

7. Shell Programming and Scripting

Merging two files by comparing three fields

Hi Experts, I need your timely help. I have a problem with merging two files. Here my situation : Here I have to compare first three fields from FILE1 with FILE2. If they are equal, I have to append the remaining values from FILE2 with FILE1 to create the output. FILE1: Class ... (3 Replies)
Discussion started by: Hunter85
3 Replies

8. Shell Programming and Scripting

Merging fields --- Join is not working

Hi GUYS sorry for putting simple query. I have tried the methods posted previously in this site but I'm unable to join the similar values in different columns of different files. I used sort -u file1 and join but no use.?? I'm attaching my inputfiles.Plz chek them I have two files. 1st file... (10 Replies)
Discussion started by: repinementer
10 Replies

9. Shell Programming and Scripting

Merging fields --- Help me plz

INPUT have a file with 2 columns. evry set in a column ends with a symbol //. the first one with something like chr, chr no, chromosome name, cell no. cell no. etc and the second column has values belong to the first columnlike chr Xy, 22, 345,22222 etc. Some clumns have repeated but not... (4 Replies)
Discussion started by: bogu0001
4 Replies

10. Shell Programming and Scripting

merging two files

Friends, os: redhat enterprise linux/SCO UNIX5.0 I have two files and I would like to merge on given key value. Now I have tried with join commd but it does not supporte multiple delimiters. and if records length is not fixed. join -a1 5 -a2 1 -t -o file1 file2 > outname Can any... (7 Replies)
Discussion started by: vakharia Mahesh
7 Replies
Login or Register to Ask a Question