View previous topic :: View next topic
|
Author |
Message |
swarnasuneel
New User
Joined: 20 Apr 2012 Posts: 20 Location: chennai
|
|
|
|
In one of my file its (spool report job).it extracts all the tables there is a report of the summary of the tables in the file.my req is to copy the summary report to a excel sheet.can any one provide solution.. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
swarnasuneel wrote: |
In one of my file its (spool report job).it extracts all the tables there is a report of the summary of the tables in the file.my req is to copy the summary report to a excel sheet.can any one provide solution.. |
How is your doubt related to topic ??
Please eloborate. |
|
Back to top |
|
|
swarnasuneel
New User
Joined: 20 Apr 2012 Posts: 20 Location: chennai
|
|
|
|
I want to copy the report from ps to excel .if possible |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Try to create a Ps file having comma seprated values |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Quote: |
I want to copy the report from ps to excel .if possible |
Of course it is possible, depending upon a few definitions.
1. If you want to copy the sequential file on the mainframe to an excel worksheet on the mainframe, you have two choices: (1) if your site does not have one, purchase one of the products for the mainframe that can generate Excel code and run your file through it, or (2) spend a year (or two) converting the 1000+ page file specification that Microsoft provides for Excel files into code to generate your own Excel worksheet on the mainframe.
2. If you merely want to be able to access the data in Excel, your best bet is to convert the sequential file into a comma-delimited file, download that to a PC running Excel, and let Excel convert the comma-delimited file into a worksheet. |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
swarnasuneel,
What you can do is to format output file to CSV and later FTP so that it can be opened with excel. Is that what you want to do?
Please post sample input and expected output along with RECFM/LRECL for the input file. Also provide rules for processing and rules to identify target records (how to identify table name etc).
Thanks, |
|
Back to top |
|
|
swarnasuneel
New User
Joined: 20 Apr 2012 Posts: 20 Location: chennai
|
|
|
|
in my file it contains the data as like below
xxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxx
vvvvvvvv
bbbbb
processing options;
tablename rows
abc 4523
def 523
before processing thereis some data we want to copy from the processing options to the o/p.the o/p may be a notepad or excel file.can we copy a file to notepad from a specified pont???pls help me |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
swarnasuneel,
Nothing is possible until you explain the rules.
Sorry but I have no idea what you are trying to explain with your posted sample input data and words like "some data".
Thanks, |
|
Back to top |
|
|
swarnasuneel
New User
Joined: 20 Apr 2012 Posts: 20 Location: chennai
|
|
|
|
sorry,
in my file data is like this
jobxxxx cputime 196.0 total elapsed time 200.8
//nxxxxx job,class
//step1
//
//
relationships
processing options
table name noof rows
tab1 9018
tab2 2000
my thing i want to copy to the output from the point where 'processing options' starts in the file.To a notepad or excel.. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Quote: |
To a notepad or excel |
Decide which there is a big difference! |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you try to implement as was mentioned earlier. . .
Quote: |
Try to create a Ps file having comma seprated values |
Actually, you would probably NOT use commas, but someother character that will not appear in the data (i.e. a backslash "\" or a tilde "~")
Once the file has been created, pull it from the mainframe to the pc using ftp (or download it to a remote ftp server). |
|
Back to top |
|
|
swarnasuneel
New User
Joined: 20 Apr 2012 Posts: 20 Location: chennai
|
|
|
|
To a notepad. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
There is NO notepad on the mainframe. . .
You need to pay attention. . . .
Create the delimited file and download it to the pc.
Then it can be opened in notepad or anything else. It is directly usable by Excel. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I have assumed how your input data should be and have got the output in CSV format and all you need to do is FTP or send this output file as attachment in CSV format so that you would be able to see the output as reading an excel
Code: |
//IN DD *
JOBXXXX CPUTIME 196.0 TOTAL ELAPSED TIME 200.8
//NXXXXX JOB,CLASS
//STEP1
//
//
RELATIONSHIPS
PROCESSING OPTIONS
TABLE NAME NOOF ROWS
TAB1 9018
TAB2 2000
//OUT DD DSN=&C2,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//OUT2 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN) USING(CTL1)
COPY FROM(IN) USING(CTL2)
COPY FROM(OUT) TO(OUT2) USING(CTL3)
//CTL1CNTL DD *
INREC FIELDS=(1,80,SEQNUM,8,ZD,START=1)
OUTFIL FNAMES=CTL2CNTL,
INCLUDE=(3,18,CH,EQ,C'PROCESSING OPTIONS'),
OUTREC=(C' OUTFIL FNAMES=OUT,STARTREC=',81,8,80:X)
//CTL2CNTL DD DSN=&C1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//CTL3CNTL DD *
INREC IFTHEN=(WHEN=(3,18,CH,EQ,C'PROCESSING OPTIONS'),
OVERLAY=(13:C',')),
IFTHEN=(WHEN=(3,44,CH,EQ,C'TABLE NAME NOOF ROWS'),
OVERLAY=(13:C',')),
IFTHEN=(WHEN=(7,1,CH,EQ,C' '),OVERLAY=(7:C','))
SORT FIELDS=COPY
OUTREC FIELDS=(1,80)
/* |
This is a tested code
Output is
Code: |
PROCESSING,OPTIONS
TABLE NAME,NOOF ROWS
TAB1,9018
TAB2,2000 |
You might need to change your input and control statements accordingly
There are experts who can produce much better results provided you were able to frame your doubt properly
Also the sort is much simpler when you produce the report as comma seperated value |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Just keep in mind that if the data might contain a(ny) comma(s), using a comma for the delimiter will fail when the data is processed on the pc. . . |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Agreed Dick |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Also, rather than having to shift the data with JCL statements to the right, you can consider using DD DATA, like this example. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Thanks Bill |
|
Back to top |
|
|
hailashwin
New User
Joined: 16 Oct 2008 Posts: 74 Location: Boston
|
|
|
|
Please forgive my ignorance, but I wonder why a simple FTP from the mainframe would involve delimiting the file unless asked to.
Even if we would need to, for a notepad, wouldnt it be advisable to use X'05' instead of a comma as you would have the file 'tab' delimited after the download.
Please correct me if am wrong. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Please note the "final destination" is Excel - which works better with a delimited file rather than a plain text file. With a delimited file the import into a Win-product (i.e. Excel) is basically automatic. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Excel handles tab delimited files - I've just created one. Don't ask me what happened next? I still cannot find what I need to PARSE and convert from VB to FB in one go! |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Far too quick - no mention of PARSE at all. But this is hijacking the thread so I will leave it and see if I can find an appropriate example somewhere. |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
well here is a way to create a semicolon delimited file using DFSORT
Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DATA,DLM=$$
JOBXXXX CPUTIME 196.0 TOTAL ELAPSED TIME 200.8
//NXXXXX JOB,CLASS
//STEP1
//
//
RELATIONSHIPS
PROCESSING OPTIONS
TABLE NAME NOOF ROWS
TAB1 9018
TAB2 2000
$$
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,18,CH,EQ,C'PROCESSING OPTIONS'),
PUSH=(81:ID=1,SEQ=8))
OUTFIL INCLUDE=(81,1,CH,GT,C' ',AND,82,8,ZD,GT,2),IFOUTLEN=25,
REMOVECC,HEADER1=('TABLE NAME;NO:OF ROWS;'),IFTHEN=(WHEN=INIT,
PARSE=(%01=(ENDBEFR=C' ',FIXLEN=10),%02=(FIXLEN=10)),
BUILD=(%01,JFY=(SHIFT=RIGHT),C';',%02,UFF,M11,LENGTH=10,C';'))
//* |
The output from this is
Code: |
TABLE NAME;NO:OF ROWS;
TAB1;0000009018;
TAB2;0000002000; |
|
|
Back to top |
|
|
|