Need to pre process a file from within a Unix Shell Script

[gschoser]

I have a file that is be created by another process and then resides in a Unix directory. Before FTPing this file I need to delete some of the data from the file. Is there a UNIX command that I can use to get the log in the correct format before ftping the file. I currently am using a UNIX shell script for all of my pre processing and would like to put in some UNIX commands to pre process the files.

Here is a sample of the file:

SQL> Select
  2  substr(con_home_ph_num,1,10) AS Chan_Addr,
  3      TO_CHAR(X_RCCL_ENTRY_DATE,'MM/DD/YYYY') As EBR_date,
  4      substr(x_rccl_brand,1,3) AS Brand,
  5      'Phone' AS Channel,
  6      substr(con_person_uid,1,21) AS Src_Id,
  7      substr(x_rccl_source,1,23) AS EBR_source,
  8     'Inquiry' AS EBR_Type
  9  FROM
 10  siebel.eim_contact
 11  WHERE
 12  con_home_ph_num is not null
 13     and con_home_ph_num > '201'
 14     and length (con_home_ph_num) = '10'
 15     and con_home_ph_num <> '0'
 16     and con_home_ph_num <> '1111111111'
 17     and con_home_ph_num <> '9999999999'
 18     and con_home_ph_num <> '0000000000'
 19     and con_home_ph_num NOT LIKE '+%'
 20     and con_home_ph_num NOT LIKE '0%'
 21     and con_home_ph_num NOT LIKE '123%'
 22     and con_home_ph_num <> '2222222222'
 23     and x_rccl_call_type is null
 24     and x_rccl_brand = 'CCI'
 25  /
CHAN_ADDR ,EBR_DATE  ,BRA,CHANN,SRC_ID               ,EBR_SOURCE             ,EBR_TYP
----------,----------,---,-----,---------------------,-----------------------,-------
6199944572,12/09/2005,CCI,Phone,Prospect - 7333956   ,CTI                    ,Inquiry
8157910644,12/11/2005,CCI,Phone,Prospect - 7331785   ,CTI                    ,Inquiry
9545240500,12/11/2005,CCI,Phone,Prospect - 7331775   ,CTI                    ,Inquiry

I need for the file to look like the following before sending it out:

SOURCE_NAM,BRA,CHANN,EBR_TYP
----------,---,-----,-------
Siebel CVP,RCI,Phone,Inquiry

CHAN_ADDR ,EBR_DATE ,BRA,CHANN,SRC_ID   ,EBR_SOURCE     ,EBR_TYP
----------,---------,---,-----,---------,---------------,-------
2017738684,24-APR-06,RCI,Phone,1-2RGQ1B ,New_CVP_Contact,Inquiry
2035252916,23-APR-06,RCI,Phone,1-2REA1I ,New_CVP_Contact,Inquiry
2058351393,23-APR-06,RCI,Phone,1-2REOMS ,New_CVP_Contact,Inquiry
2059074406,21-APR-06,RCI,Phone,1-2RCNPW ,New_CVP_Contact,Inquiry

 

[BocaBurger]

You might comsider an EAI program such as IBM WebSphere DataStage TX to do this. It can validate the data, do the transformations and FTP the file automatically.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[schu]

Firstly
I am not to sure where your header information comes from...
SOURCE_NAM,BRA,CHANN,EBR_TYP
----------,---,-----,-------
Siebel CVP,RCI,Phone,Inquiry

If it is the same everytime I guess you can cat or echo it to the beginning of the file.

The second part can be extracted using

cat test.txt | awk '# {BEGIN tmode=0; }
/\\/ { tmode=1; next; }
{
if ( tmode == 1 ) { print $0;}
}
#'

I assumed you want the data after the line containing the backslash (\)
The script looks for a backslash "\\" and prints everything after it. You can modify the pattern to what every you like.