I'm new of UNIX shell scripting. I'm recently generating a excel report in UNIX(file with delimiter is fine). How should I make a script to do it?

1 file to join comes from output of one UNIX command, the second from another UNIX command, and third from a database query. The key columes of all these 3 files come from a mapping file. It's basically as following:

MappingFile:
===========
job1 a_name1
job2 c_name2
job3 b_name3
job4 e_name4

file1_toJOIN:
============
b_name3 12:00:03 15:00:09
e_name4 10:30:00 11:00:00
c_name2 09:40:00 10:12:00
a_name1 22:00:00 23:00:00

file2_toJOIN:
=============
job2 tom 22
job1 dan 18
job4 jim 25
job3 kim 20

the spreadsheet format to generate:
===========================
job1 22:00:00 23:00:00 01:00:00 dan 18
......

Appreciate your help!

try this

for i in `cat map | awk 'BEGIN { FS = " " }; { print $1}'`
do
mapkey1=$i
mapkey2=`cat map | grep $mapkey1 `
mapkey2=`echo $mapkey2 | awk 'BEGIN { FS = " " }; { print $2}'`
file1key=`cat file1 | grep $mapkey2`
file1key=`echo $file1key | awk 'BEGIN { FS = " " }; { print $2,$3}'`
file2key=`cat file2 | grep $mapkey1`
file2key=`echo $file2key | awk 'BEGIN { FS = " " }; { print $2,$3}'`
echo $i $file1key $file2key
done


map - your map file
file1 - your file1_toJOIN
file2 - file2_toJOIN:

The o/p i got is :
job1 22:00:00 23:00:00 dan 18
job2 09:40:00 10:12:00 tom 22
job3 12:00:03 15:00:09 kim 20
job4 10:30:00 11:00:00 jim 25

Appreciate both of you.

I've tried the first script. It works perfectly. I feel a bit difficult to understand array, as a shell script beginner. But I'll try tomorrow, as it looks really compact. I'd like to use in the future.

#Opening the file
for i in `cat map | awk 'BEGIN { FS = " " }; { print $1}'`
do

#Column1 in map file is key to file2
mapkey1=$i

mapkey2=`cat map | grep $mapkey1 `

#Column2 in map file is key to file1

mapkey2=`echo $mapkey2 | awk 'BEGIN { FS = " " }; { print $2}'`

#Selecting values from file1 based on mapkey2 from map file
file1key=`cat file1 | grep $mapkey2`
file1key=`echo $file1key | awk 'BEGIN { FS = " " }; { print $2,$3}'`

#Selecting values from file2 based on mapkey1 from map file
file2key=`cat file2 | grep $mapkey1`
file2key=`echo $file2key | awk 'BEGIN { FS = " " }; { print $2,$3}'`

#Diplay the results
echo $i $file1key $file2key
done

Thank you, Prasperl.

I used ksh, for some reason, it gave me awk syntex error. After I removed field seperator, it let me through. Do you know the reason?

Could you please post the code u used and the error message?