extracting data based on common field in multiple files. 1

[awklearner]

Hello everyone.

Just start learning awk. Don't know what to do.Please Help me.
My Question is

(Part1)
I have some data files.
example

Balance1.txt
AA 10
BB 11
CC 34

Balance2.txt
CC 11
AA 10
BB 13

Balance3.txt
BB 11
AA 10
CC 13

I don't know how many Balance files is going to use but I know all files have same number of records and they don't have duplicate records.

What I am trying to print out is

awk -f printout.awk Balance1.txt Balance2.txt

Name Balance1 Balance2
AA 10 10
BB 11 13
CC 34 11

(part2)
if the record is missing in the file, the field value will be zero.
for example,
Balance4.txt
AA 11
DD 20

awk -f printout2.awk Balance1.txt Balance2.txt Balance4.txt

Name Balance1 Balance2 Balance4
AA 10 10 11
BB 11 13 0
CC 34 11 0
DD 0 0 20


I think part2 is more difficult than part1.In part1, I tried to do with arrays but I didn't get it. Thanks in advance.

 

[awklearner]

Me again. Sorry about the format of output. I hope you understand what I mean.

 

[PHV]

A starting point (the formatting is up to you):
awk '
FNR==1{f[n++]=FILENAME}
{++a[$1];b[$1,FILENAME]=$2}
END{
printf "Name"
for(j=0;j<n;++j) printf "\t%s",f[j]
printf "\n"
for(i in a){
printf "%s",i
for(j=0;j<n;++j) printf "\t%d",b[i,f[j]]
printf "\n"
}
}
' Balance*.txt

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[awklearner]

That is quick man. I just posted it and I got the answer. Are you sure you are human? ;-)
Anyway,I am testing now.
Thanks,dude.

 

[awklearner]

Thank for your help,PHV.

slight problem. The result is correct but the output is wrong order.
What I mean is all the record from 1st file should display first and fllow by second file,third file,etc.

Example
Name Balance1 Balance2 Balance4
AA 10 10 11
BB 11 13 0
CC 34 11 0
DD 0 0 20

I am working on this as well. If you give me some guideline, it would be most appreciated.Thanks in advance.

 

[PHV]

Something like this ?
awk '
FNR==1{f[n++]=FILENAME}
#{++a[$1];b[$1,FILENAME]=$2}
{if(!($1 in a)){x[m++]=$1;++a[$1]};b[$1,FILENAME]=$2}
END{
printf "Name"
for(j=0;j<n;++j) printf "\t%s",f[j]
printf "\n"
for(k=0;k<m;++k){
i=x[k];printf "%s",i
for(j=0;j<n;++j) printf "\t%d",b[i,f[j]]
printf "\n"
}
}
' Balance*.txt

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244