Focal Point
[CLOSED] Zipping and email the EXL2K format file in FOCUS language

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/438106523

December 26, 2009, 01:47 PM
Rajiv Garg
[CLOSED] Zipping and email the EXL2K format file in FOCUS language
I have produced a file in EXL2K format in FOCUS language. My requirement is to zip the above produced file and email to some user. I have produced the file successfully. I have zipped the file using following in JCL


-INDD(INFILE)
-ARCHOUTDD(OUTFILE)
-METHOD(MAXIMUM)



Then i email the file using the following in JCL

%XMITIP * -
SUBJECT 'TEST FILE' -
FROM (TEST@GMAIL.COM) -
MSGDD STEPS -
ADDRESSFILEDD ADDR -
FORMAT BIN/ZIP -
FILEDD (NHPRENW)


But when i get the file in my email, i can't be able to open the file in Excel format. I am getting junk data. Can somebody please help me on where i am doing wrong?

This message has been edited. Last edited by: Kerry,


FOCUS version 7.6.7.
Windows
Excel
December 28, 2009, 11:02 AM
ira
Are you using OWA or outlook for your email? Can you try to save to another format (e.g. html or pdf) and does that come out? You have not shown the code you used to generate the request in WebFOCUS. Insert your code within brackets. What op. sys are you using? What release of WebFOCUS are you using?


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
December 28, 2009, 12:55 PM
BlueZone
Rajiv - Try this format. We have been using this for a while.
  
%XMITIP (&EMLTO.EVAL) -                        
SUBJECT "&SBJCT.EVAL " -                       
FROM &EMLFRM.EVAL -                            
MSGDD MSG -                                    
FILEDD outddNM FILENAME &ATCH_NM.EVAL FORMAT TXT 
//MSG DD *                                     
Attached is your requested report.
//*                                            


All parameter input into the JCL step has been parameterized, so this can be a -INCLUDE module from where ever.
The &ATCH_NM variable could be something like ABCfilename.XLS


Hope this helps.
Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
December 28, 2009, 02:07 PM
Rajiv Garg
Hi ira and Blue zone, I am using mainframe focus and not webfocus. The requirement is to produce an excel sheet with multiple tabs and then zip the excel sheet and then email the excel sheet..I can't mail the excel sheet directly to user without zipping that. Below is the code i have used to create the excel sheet with multiple tabs and i am successful in creating the excel sheet with multiple tabs. The problem comes when i zip the excel sheet and mail that using the JCL for zip and email i already gave. I got the zip file but when i opened the zip file and the excel file in it, it contains junk data in it.

 
TABLE FILE B5482OUP
PRINT RCD.ID                  NOPRINT
      VENDOR.ID            AS      'VENDOR-ID'
      GROUP.ID             AS      'GROUP-ID'
      SUB.GROUP.ID         AS      'SUB-GROUP-ID'
      REN.CYCLE.DT         AS      'REN-CYCLE-DT'
      REN.EFF.DT           AS      'REN-EFF-DT'
      RATE.CALC.DT         AS      'RATE-CALC-DT'
      EMP.NAME             AS      'EMP-NAME'
      BILL.TYPE.CD         AS      'BILL-TYPE-CD'
      COBRA.IND            AS      'COBRA-IND'
      EE.CHOICE.IND        AS      'EE-CHOICE-IND'
      FL.MINI.COBRA.IND    AS      'FL-MINI-COBRA-IND'
      MRRF.FCT             AS      'MRRF-FCT'
      PRIME.POL.NUM        AS      'PRIME-POL-NUM'
BY GROUP.ID                NOPRINT
BY SUB.GROUP.ID            NOPRINT
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=5, TITLETEXT='GENERAL', $
TYPE=TITLE, COLOR=BLACK, $
ENDSTYLE
WHERE RCD.ID EQ '1'
ON TABLE HOLD AS HOLD1 FORMAT EXL2K OPEN
END
-*
TABLE FILE B5482OUQ
PRINT RCD.ID                      NOPRINT
      GROUP.ID                  AS  GROUP-ID'
      SUB.GROUP.ID              AS  SUB-GROUP-ID'
      REN.PLN.PRGPLN.CD         AS  REN-PLN-PRGPLN-CD'
      REN.PLN.HLTH.TYP.CD       AS  REN-PLN-HLTH-TYP-CD'
      REN.RX.PLN.CD             AS  REN-RX-PLN-CD'
      REN.PLN.EE.CHOICE.IND     AS  REN-PLN-EE-CHOICE.IND'
      REN.PLN.EE.RT             AS  REN-PLN-EE-RT'
      REN.PLN.EE.SPO.RT         AS  REN-PLN-EE-SPO-RT'
      REN.PLN.EE.CHLD.RT        AS  REN-PLN-EE-CHLD-RT'
      REN.PLN.FMLY.RT           AS  REN-PLN-FMLY-RT'
      REN.PLN.MEDC.EE.RT        AS  REN-PLN-MEDC-EE-RT'
      REN.PLN.MEDC.SPO.RT       AS  REN-PLN-MEDC-SPO-RT'
      REN.PLN.MEDC.CHLD.RT      AS  REN-PLN-MEDC-CHLD-RT'
      REN.PLN.EE.SPO.MEDC.RT    AS  REN-PLN-EE-SPO-MEDC-RT'
      REN.PLN.EE.CHLD.MEDC.RT   AS  REN-PLN-EE-CHLD-MEDC-RT'
      REN.PLN.EE.SPO.CHLD.MEDC.RT   AS 'REN-PLN-EE-SPO-CHLD-MEDC-RT'
      REN.PLN.BT5.MEDC.ML.EE.RT  AS  'REN-PLN-BT5-MEDC-ML-EE-RT'
      REN.PLN.BT5.MEDC.FL.EE.RT  AS  'REN-PLN-BT5-MEDC-FL-EE-RT'
      REN.PLN.BT5.MEDC.ML.SPO.RT AS 'REN-PLN-BT5-MEDC-ML-SPO-RT'
      REN.PLN.BT5.MEDC.FL.SPO.RT AS 'REN-PLN-BT5-MEDC-FL-SPO-RT'
      REN.PLN.BT5.MEDC.CHLD.RT   AS 'REN-PLN-BT5-MEDC-CHLD-RT'
BY GROUP.ID           NOPRINT
BY SUB.GROUP.ID       NOPRINT
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=5,   $
TITLETEXT='RENPLAN & RATES',                   $
TYPE=TITLE, COLOR=BLACK, $
ENDSTYLE
WHERE RCD.ID EQ '2'
ON TABLE HOLD AS HOLD1 FORMAT EXL2K OPEN
END
TABLE FILE B5482OUR
PRINT RCD.ID                       NOPRINT
      GROUP.ID                        AS  'GROUP-ID'
      SUB.GROUP.ID                    AS 'SUB-GROUP-ID'
      REN.PLN.PRGPLN.CD               AS 'REN-PLN-PRGPLN-CD'
      REN.PLN.BT5.AGE.MIN             AS 'REN-PLN-BT5-AGE-MIN'
      REN.PLN.BT5.AGE.MAX             AS 'REN-PLN-BT5-AGE-MAX'
      REN.PLN.BT5.ML.EE.ONLY.RT       AS 'REN-PLN-BT5-ML-EE-ONLY-RT'
      REN.PLN.BT5.ML.EE.SPO.RT        AS 'REN-PLN-BT5-ML-EE-SPO-RT'
      REN.PLN.BT5.ML.EE.CHLD.RT       AS 'REN-PLN-BT5-ML-EE-CHLD-RT'
      REN.PLN.BT5.ML.EE.FMLY.RT       AS 'REN-PLN-BT5-ML-EE-FMLY-RT'
      REN.PLN.BT5.FL.EE.ONLY.RATE     AS 'REN-PLN-BT5-FL-EE-ONLY-RATE'
      REN.PLN.BT5.FL.EE.SPO.RT        AS 'REN-PLN-BT5-FL-EE-SPO-RT'
      REN.PLN.BT5.FL.EE.CHLD.RT       AS 'REN-PLN-BT5-FL-EE-CHLD-RT'
      REN.PLN.BT5.FL.EE.FMLY.RT       AS 'REN-PLN-BT5-FL-EE-FMLY-RT'
BY GROUP.ID                        NOPRINT
BY SUB.GROUP.ID                    NOPRINT
BY REN.PLN.BT5.AGE.MIN             NOPRINT
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=5,   $
TITLETEXT='REN PLAN.BT5 RATES',                $
TYPE=TITLE, COLOR=BLACK, $
ENDSTYLE
WHERE RCD.ID EQ '3'
ON TABLE HOLD AS HOLD1 FORMAT EXL2K OPEN
END
TABLE FILE B5482OUS
PRINT RCD.ID                 NOPRINT
      GROUP.ID                     AS 'GROUP-ID'
      SUB.GROUP.ID                 AS 'SUB-GROUP-ID'
      OPT.NUM                      AS 'OPT-NUM'
      OPT.PLN.PRGPLN.CD            AS 'OPT-PLN-PRGPLN-CD'
      OPT.PLN.HLTH.TYP.CD          AS 'OPT-PLN-HLTH-TYP-CD'
      OPT.RX.PLN.CD                AS 'OPT-RX-PLN-CD'
      OPT.PLN.EE.CHOICE.IND        AS 'OPT-PLN-EE-CHOICE-IND'
      OPT.PLN.EE.RT                AS 'OPT-PLN-EE-RT'
      OPT.PLN.EE.SP.RT             AS 'OPT-PLN-EE-SP-RT'
      OPT.PLN.EE.CHLD.RT           AS 'OPT-PLN-EE-CHLD-RT'
      OPT.PLN.FMLY.RT              AS 'OPT-PLN-FMLY-RT'
      OPT.MEDC.ACT.EE.RT           AS 'OPT-MEDC-ACT-EE-RT'
      OPT.MEDC.ACT.SPO.RT          AS 'OPT-MEDC-ACT-SPO-RT'
      OPT.MEDC.ACT.CHLD.RT         AS 'OPT-MEDC-ACT-CHLD-RT'
      OPT.PLN.EE.SPO.MEDC.RT       AS 'OPT-PLN-EE-SPO-MEDC-RT'
      OPT.PLN.EE.CHLD.MEDC.RT      AS 'OPT-PLN-EE-CHLD-MEDC-RT'
      OPT.PLN.EE.SPO.CHLD.MEDC.RT  AS 'OPT-PLN-EE-SPO-CHLD-MEDC-RT'
      OPT.PLN.BT5.MEDC.ML.EE.RT    AS 'OPT-PLN-BT5-MEDC-ML-EE-RT'
      OPT.PLN.BT5.MEDC.FL.EE.RT    AS 'OPT-PLN-BT5-MEDC-FL-EE-RT'
      OPT.PLN.BT5.MEDC.ML.SPO.RT   AS 'OPT-PLN-BT5-MEDC-ML-SPO-RT'
      OPT.PLN.BT5.MEDC.FL.SPO.RT   AS 'OPT-PLN-BT5-MEDC-FL-SPO-RT'
      OPT.PLN.BT5.MEDC.CHLD.RT     AS 'OPT-PLN-BT5-MEDC-CHLD-RT'
BY GROUP.ID                     NOPRINT
BY SUB.GROUP.ID                 NOPRINT
BY OPT.PLN.PRGPLN.CD            NOPRINT
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=5,   $
TITLETEXT='OPT. PLANS AND RATES',              $
TYPE=TITLE, COLOR=BLACK, $
ENDSTYLE
WHERE RCD.ID EQ '4'
ON TABLE HOLD AS HOLD1 FORMAT EXL2K OPEN
END
TABLE FILE B5482OUT
PRINT RCD.ID                       NOPRINT
      GROUP.ID                      AS 'GROUP-ID'
      SUB.GROUP.ID                  AS 'SUB-GROUP-ID'
      OPT.NUM                       AS 'OPT-NUM'
      OPT.PLN.PRGPLN.CD             AS 'OPT-PLN.PRGPLN.CD'
      OPT.PLN.BT5.AGE.MIN           AS 'OPT-PLN.BT5-AGE-MIN'
      OPT.PLN.BT5.AGE.MAX           AS 'OPT-PLN.BT5-AGE-MAX'
      OPT.PLN.BT5.ML.EE.ONLY.RT     AS 'OPT-PLN.BT5-ML-EE-ONLY-RT'
      OPT.PLN.BT5.ML.EE.SPO.RT      AS 'OPT-PLN.BT5-ML-EE-SPO-RT'
      OPT.PLN.BT5.ML.EE.CHLD.RT     AS 'OPT-PLN.BT5-ML-EE-CHLD-RT'
      OPT.PLN.BT5.ML.EE.FMLY.RT     AS 'OPT-PLN.BT5-ML-EE-FMLY-RT'
      OPT.PLN.BT5.FL.EE.ONLY.RATE   AS 'OPT-PLN.BT5-FL-EE-ONLY-RATE'
      OPT.PLN.BT5.FL.EE.SPO.RT      AS 'OPT-PLN.BT5-FL-EE-SPO-RT'
      OPT.PLN.BT5.FL.EE.CHLD.RT     AS 'OPT-PLN.BT5-FL-EE-CHLD-RT'
      OPT.PLN.BT5.FL.EE.FMLY.RT     AS 'OPT-PLN.BT5-FL-EE-FMLY-RT'
BY GROUP.ID                        NOPRINT
BY SUB.GROUP.ID                    NOPRINT
BY OPT.PLN.PRGPLN.CD               NOPRINT
BY OPT.PLN.BT5.AGE.MIN             NOPRINT
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=5,   $
TITLETEXT='OPT. PLANS. BT5 RATES',             $
TYPE=TITLE, COLOR=BLACK, $
ENDSTYLE
WHERE RCD.ID EQ '5'
ON TABLE HOLD AS HOLD1    FORMAT EXL2K CLOSE
END 



FOCUS version 7.6.7.
Windows
Excel
January 03, 2010, 03:45 PM
Waz
As this is a Mainframe, is the Excel file in EBCDIC format ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

January 08, 2010, 10:00 AM
<JG>
Waz has the most likely cause.

Mainframes operate using EBCDIC so ABCDE will look like ÁÂÃÄÅÆÇ
When sent unconverted to a windows ASCII platform.

By zipping the file before mailing it might go down in the wrong internal format
and it is dependant on the target platform unzip tool to convert.

It is possible that the Windows Zip software that you use is unable to do the conversion.
So use the same product on windows as on the Mainframe.

You do not say what Zip utility you are using on the mainframe, if it's PKZIP or SecureZip
then you might want to add DATA_TYPE(TEXT) to the zip process