Shell Programming and Scripting

Hello ALL,

I really need help to grep data and store in particular format. I am struggling to write that script .Please help me to solve this problem otherwise i will loose my job...

I have to compare 2 files and generate the staticts of the data.
First file product.dat contains 2 column .
TestName ProductName
/daily/db/network YRS
/daily/db/org SiCat

2 second file is consolidated file and it contains data and fails for Host and Version and Test Name.

I need to print the log in this format

Host Version SiCat YRS DB OPC
Animator 10 2 3 6 7

We need the data format for the second file...

/users/testsuite/db/SiCat/2006-08-24/10/kracherl/run.log
! daily/DB/attach/attach_redundancy.exp:
! daily/DB/help/help.exp:
! daily/DB/hole_fail_rate/hfr-circular.exp:
! daily/DB/markerfile/cws.exp:
! daily/DB/markerfile/pd_hist.exp:
! daily/nanoCAD/edit/and_test_edit.exp:
! daily/nanoCAD/edit/poly_edit.exp:
/users/testsuite/db/SiCat/2006-08-24/10_64/limo/run.log
! daily/DB/attach/attach_redundancy.exp:
! daily/DB/hole_fail_rate/hfr-circular.exp:
! daily/DB/markerfile/cws.exp:
! daily/DB/markerfile/hole_enc.exp:
! daily/DB/markerfile/metal_island2.exp:
! daily/OPC/FitTool/fittool1.exp:
! daily/OPC/Simulator/bitmaps_1.exp:
! daily/OPC/Simulator/hier_ai_coarse.exp:
/users/testsuite/db/SiCat/2006-08-24/10_fast/via2/run.log
! daily/DB/attach/attach_redundancy.exp:
! daily/DB/file_io/save/save_cif.exp:
! daily/DB/hole_fail_rate/hfr-circular.exp:
! daily/DB/markerfile/cws.exp:
! daily/DB/markerfile/hole_enc.exp:
! daily/OPC/manuver/manuver_psd1.exp:
! daily/nanoCAD/edit/and_test_edit.exp:
! daily/nanoCAD/edit/poly_edit.exp:
/users/testsuite/db/SiCat/2006-08-24/7/optimator/run.log
! daily/DB/CmdLn/no_license.exp:
! daily/DB/add_prim/add_path.exp:
! daily/DB/core/check_db.exp:
! daily/DB/grid_measure/gm_area.exp:
! daily/DB/grid_measure/gm_edge_length.exp:
! daily/DB/grid_measure/gm_pattern_density.exp:
! daily/DB/grid_measure/gm_silicide_trans.exp:
! daily/DB/hole_fail_rate/hfr-insane.exp:
! daily/DB/hole_fail_rate/hfr-normal.exp:
! daily/DB/info/info.exp:
! daily/DB/mc_ca/mc_ca.exp:
! daily/DB/measure/measure_window.exp:
! daily/DB/silicide_transition/silicide_fkt.exp:
! daily/DB/smash/smash_bug3665.exp:
! daily/DB/smash/smash_bug5039.exp:
/users/testsuite/db/SiCat/2006-08-24/8/optimator/run.log
! daily/DB/SFM/scanHoleConnect.exp:
! daily/DB/add_prim/add_path.exp:
! daily/DB/attach/attach_density.exp:
! daily/DB/attach/attach_property-insane.exp:
! daily/DB/attach/attach_property.exp:

The failuer record contains "!" in first column .
The row which doesnt contain ! , in that row 6 and 7 column is the version and Host name.
Product name are YRS , SiCat OPC PSd and nanocad

O/p i need this way

Version | Host | YRS | SiCat | OPC | PSD |nanocad
10 | Krachel | 2 | 4 | 5 | 0 | 1
10_64 | limo | 3 | 2 | 1 | 1 | 0


The first file data contains 2 columns

daily/DB/attach/attach_redundancy.exp SiCat
daily/DB/help/help.exp YRS
daily/DB/hole_fail_rate/hfr-circular.exp YRS


I have compare fails from first file with second file first column and print the total no of fails as per the product .

I can hardcored the Product name in shell script because product name are SiCat ,YRS ,OPC and PSD .

Please do help me

please can someone help me to solve this .