Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Joining Two Files Using Awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Joining Two Files Using Awk
# 1  
Old 03-15-2010
Joining Two Files Using Awk
Hi All,
I am new to awk program. But i have got some assignment on awk.

The problem is:

i have two files file1 and file2. Both files have same structure. First i have to join both files on filed1,field2 and field3 and then for matching records i want to perform some calculation like:
Code:
filed_tmp1=file1.filed4-file2.field4
field_tmp2=file1.filed5-file2.field5
field_tmp3=file1.filed6-file2.field6

and output file should be like for matching records

Filed1|Field2|Field3|filed_tmp1|filed_tmp1|filed_tmp1|Field7

and for non-matching records:

FIeld1|Field2|Field3|Field4|Field5|Field6|Field7

all from file1
ex:
File1
Code:
12345|JADE1|1|12|120|234.45|0|0|0|0|0|0
45678|JADE3|1|12|120|234.45|0|0|0|0|0|0
98765|JADE2|1|12|120|234.45|0|0|0|0|0|0

File2

Code:
12345|JADE1|1|6|60|134.45|0|0|0|0|0|0
45678|JADE3|1|5|50|134.45|0|0|0|0|0|0

Outfile:

Code:
12345|JADE1|1|6|60|100|0|0|0|0|0|0
45678|JADE3|1|7|70|100|0|0|0|0|0|0
98765|JADE2|1|12|120|100|0|0|0|0|0|0

I Hope i am clear to all.
Thanks in advance

---------- Post updated at 07:10 PM ---------- Previous update was at 06:01 PM ----------

Code:
nawk -F\| 'BEGIN {
while ((getline < "bcd.txt") > 0)
f2array[$1] = $0
OFS="|"}
{if (f2array[$1])
{
var1=$4
var3=$5
var5=$6
print f2array[$1] > "temp"
getline < "temp"
var2=$4
var4=$5
var6=$6
out1 = var1 - var2
out2 = var3 - var4
out3 = var5 - var6
print $1"|"$2"|"$3"|"out1"|"out2"|"out3"|"$7"|"$8"|"$9"|"$10"|"$11"|"$12
}
else
 print $0
}' abc.temp_tmp

Last edited by pludi; 03-15-2010 at 10:58 AM.. Reason: Please use code tags!!!
# 2  
Old 03-15-2010
Welcome to Unix.com forum. Please read the Forum Rules before posting any homework/assignment Smilie
Code:
awk 'BEGIN{FS=OFS="|"}NR==FNR{a[$1$2]=$4;b[$1$2]=$5;c[$1$2]=$6;next}{$4=$4-a[$1$2];$5=$5-b[$1$2];$6=$6-c[$1$2]}1' file2 file1



---------- Post updated at 12:08 PM ---------- Previous update was at 12:07 PM ----------

Use gawk, nawk or /usr/xpg4/bin/awk on Solaris.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Issue with awk when joining two files when field has '-' hyphen

Dear Community; I need to join two files but I am facing issues. 1st file has multiple columns. Primary (1st) columns has unique values. There are other columns out of which some has non-ascii characters as well (other language). Example File below: 1-1001JRL,BiRecurring... (5 Replies)
Discussion started by: mystition
5 Replies

2. Shell Programming and Scripting

Joining files using awk not extracting all columns from File 2

Hello All I'm joining two files using Awk by Left outer join on the file 1 File 1 1 AA 2 BB 3 CC 4 DD File 2 1 IND 100 200 300 2 AUS 400 500 600 5 USA 700 800 900 (18 Replies)
Discussion started by: venkat_reddy
18 Replies

3. Shell Programming and Scripting

Please help me in joining two files

I have two files with the below contents : sampleoutput3.txt 20150202;hostname1 20150223;hostname2 20150716;hostname3 sampleoutput1.txt hostname;packages_out_of_date;errata_out_of_date; hostname1;11;0; hostnamea;12;0; hostnameb;11;0; hostnamec;95;38; hostnamed;440;358;... (2 Replies)
Discussion started by: rahul2662
2 Replies

4. Shell Programming and Scripting

Joining 2 Files

File "A" (column names: Nickname Number GB) Nickname Number GB PROD_DB0034 100A 16 ASMIL1B_DATA_003 100B 16 PSPROD_0000 1014 36 PSPROD_0001 100D 223 ..... File "B" (column names: TYPE DEVICE NUMBER SIZE) TYPE DEVICE NUMBER SIZE 1750500 hdisk2 100A 16384 1750500 hdisk3 ... (4 Replies)
Discussion started by: Daniel Gate
4 Replies

5. Shell Programming and Scripting

Help with joining files and adding headers to files

Hi, I have about 20 tab delimited text files that have non sequential numbering such as: UCD2.summary.txt UCD45.summary.txt UCD56.summery.txt The first column of each file has the same number of lines and content. The next 2 column have data points: i.e UCD2.summary.txt: a 8.9 ... (8 Replies)
Discussion started by: rrdavis
8 Replies

6. Shell Programming and Scripting

Joining Three Files

Hi guys, I have three files which needs to be joined to a single file. File 1: Col a, Col b, Col c File 2: Col 1a, Col 1b File 3: Col 2a, Col 2b Output: Col 1a, Col 2a, Col a, Col b, Col c. All the files are comma delimited. I need to join Col b with Col 1b and need to... (17 Replies)
Discussion started by: mac4rfree
17 Replies

7. Shell Programming and Scripting

Joining 3 AWK scripts to avoid use "temp" files

Hi everyone, Looking for a suggestion to improve the below script in which I´ve been working. The thing is I have 3 separated AWK scripts that I need to apply over the inputfile, and for scripts (2) and (3) I have to use a "temp" file as their inputfile (inputfile_temp and inputfile_temp1... (2 Replies)
Discussion started by: cgkmal
2 Replies

8. Shell Programming and Scripting

awk joining lines

Hello, I'm trying to write a piece of code in awk, which should be able recognize by some regexps two lines and then join them together (maybe write them without \n would be enough, I don't know).. the thing is that every line in the file i'm working with starts with some number, for example: ... (4 Replies)
Discussion started by: midin
4 Replies

9. Shell Programming and Scripting

Need Help with Joining Command or AWK

Hello. I have 2 files. File 1: 1692 @00000000201@00000205@20090402@000015000 1692 @00000000202@00000206@20090402@000010000 263157760@00000000401@00000201@20090402@000056250 510584583@00000000300@00000207@20090402@000030000 571764164@00000000100@00000128@20090402@000000500... (12 Replies)
Discussion started by: crazyhpux
12 Replies

10. Shell Programming and Scripting

Help with joining two files

Greetings, all. I've got a project that requires I join two data files together, then do some processing and output. Everything must be done in a shell script, using standard unix tools. The files look like the following: File_1 Layout: Acct#,Subacct#,Descrip Sample: ... (3 Replies)
Discussion started by: rjlohman
3 Replies
Login or Register to Ask a Question