[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
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?
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 !!
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
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