Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

canwe write a program to put the contents of tableinto Excel

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
mfstudent1
Currently Banned

New User


Joined: 03 Mar 2006
Posts: 18

PostPosted: Fri Mar 03, 2006 6:17 am    Post subject: canwe write a program to put the contents of tableinto Excel
Reply with quote

Hello ,

Iam a new member to the group...

I have to move all the distinct values of a table into a dataset and then move the dataset into an excel sheet.

Can I do that??

If yes,Can you please help me with the pgms to do so???
Back to top
View user's profile Send private message

amitava
Warnings : 1

Active User


Joined: 30 Oct 2005
Posts: 186
Location: India

PostPosted: Fri Mar 03, 2006 10:52 am    Post subject:
Reply with quote

Hey mfstudent1, u can use the following JCL - which will unload the DB2 table to a dataset and then the file u move by ISPF 6 option - File Transfer. Eg :-
Suppose you want to unload the rows for
department D01 from the project table. You can fit the table
specification on one line, and you do not want to execute any non- SELECT statements, so you do not need the SQL parameter. Your
invocation looks like this:
Code:
//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20
     //SYSTSPRT DD  SYSOUT=*
     //SYSTSIN  DD  *
      DSN SYSTEM(DSN)
      RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
            LIB('DSN710.RUNLIB.LOAD')
     //SYSPRINT DD SYSOUT=*
     //SYSUDUMP DD SYSOUT=*
     //SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
     //            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
     //            VOL=SER=SCR03
     //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
     //            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
     //            VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
     //SYSIN    DD *
     DSN8710.PROJ WHERE DEPTNO='D01'

Now just move the DSN8UNLD.SYSREC00 DS by FILE TRANSFER option.

But in this case, when u will import this file in a XLS, it will not look very good.

THat's why I suggest u to write a simple COBOL code which will read the specified table, opens a cursor, fetch it and then writes these data into the DS with '~' seperated. So when u transfer the output file, the corrosponding file in ur system will be '~' seperated. Now u import this file to XLS using the seperator.
Back to top
View user's profile Send private message
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4649
Location: Raleigh, NC, USA

PostPosted: Sat Mar 04, 2006 12:35 am    Post subject: Re: canwe write a program to put the contents of tableinto E
Reply with quote

Isn't it easier and less time-consuming to use your third party data management tools (Mercator, Cloverleaf, Gentran, SAS, Crystal) to do all this instead?
Back to top
View user's profile Send private message
pspreddy

New User


Joined: 14 Mar 2006
Posts: 24
Location: Chicago

PostPosted: Fri Mar 17, 2006 5:10 am    Post subject: canwe write a program to put the contents of tableinto Excel
Reply with quote

Hi mfstudent1,

Two simple options for this

1) You can write a SPUFI to get the results into a dataset directly and then you can ddownload this data into an Excel sheet. Use option 6 as said. And open the downloaded file using excel and go for 'Fixed Width' option where you can specify the width of each and every column that you need. The data looks very neat. If you have any unwanted rows in the datset like
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
you can either delete them in the dataset itself before downloading or you can delete them from the excel sheet that you generate.

2) You can use QMF to run the query and say 'export ?' to specify the dataset you want to capture the results into. then download this dataset as said above

I generally opt for SPUFI rather than QMF. SPUFi is very convenient to use and we can always go back to the dataset whenever we want and with all sort of edit options. But with QMF you may not be able to retain the data unless you specificaly exort it.

We can come out of SPUFI and go to dataset to check the results. I heard QMF holds the table while we are viewing the results. in simple terms you can compare QMF to online and SPUFI to batch task. Some one please correct me if I'm wrong.

Have a great weekend folks

-Shiva
Back to top
View user's profile Send private message
martin9

Active User


Joined: 01 Mar 2006
Posts: 290
Location: Basel, Switzerland

PostPosted: Fri Mar 17, 2006 5:54 pm    Post subject:
Reply with quote

hy mfstudent1,

yes you can make a file for excel,
but you have to know, that you need
to create a simple sequential dataset,
ergo a flat file.
just, and this is important, you must do it
as a csv file...

value1 ; value2 ; value3 ; valuen
you see that each record should separate its values
by any delimiter. each value gives then a column
in excel data row.

martin9
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Sun Mar 19, 2006 2:52 pm    Post subject:
Reply with quote

HI pspreddy,

thank you for your kind information, i need more differences for SPUFI and QMF, please let me know..

Thanks and Regard,
venkata Prasanth
Infy
Back to top
View user's profile Send private message
pspreddy

New User


Joined: 14 Mar 2006
Posts: 24
Location: Chicago

PostPosted: Tue Mar 21, 2006 5:53 am    Post subject: Re: canwe write a program to put the contents of tableinto E
Reply with quote

Hi Prasanth,

Some of the differences are like below

1) Using SPUFI, we can run multiple SQL statements at the same time. But this is not possible in QMF. A QMF query can only contain one SQL statement. Even if it has a second SQl statementon the panel, it ignores that running only the first statement

2) QMF queries can include dynamic variables, in which QMF prompts the user for a value and then executes the query based upon the values supplied. But this is not possible in SPUFI

3) QMF queries can be shared across users. We can share queries using SPUFI also. But the difference is when we use SPUFI all the queries will be stored as members of the the PDS you mention in the SPUFI panel with the name you give

4) I heard QMF also can be run in batch. But no idea on this.

5) The output of a SPUFI will be in a dataset preceded by your user id. In some organizations another user can not view the contents of a dataset starting with your user id (We have this constraint)

6) Using SPUFI there is a limit on the number of records (I do not remeber the exact count limit) that can be fetched with an SQL statement. If the record count excees this limit SPUFI can not fetch those records. But using QMF you can fetch as many reords as you want and you can direct them to a dataset if you wish to

These are the differences I know of. May be site gurus can throw some light on this topic.

Thanks
Sivaprasad
Back to top
View user's profile Send private message
KS

New User


Joined: 28 Feb 2006
Posts: 91
Location: Chennai

PostPosted: Wed Mar 22, 2006 2:31 pm    Post subject:
Reply with quote

Hi,

Write the query in the spufi panel.
Then save the report to the dataset using the command - EXPORT REPORT TO NAME;
Now output of the query will be stored in the dataset ur ID.NAME.REPORT
Now using the FTP option 6 u can send the report to Excel.
Please correct if am wrong.

Thanks,
KS
Back to top
View user's profile Send private message
jasorn

Active User


Joined: 12 Jul 2006
Posts: 153
Location: USA

PostPosted: Wed Jul 12, 2006 7:38 pm    Post subject: Re: canwe write a program to put the contents of tableinto E
Reply with quote

I usually see the csv method used for this but I have used another method that works pretty well. Actually, I use this method from cobol or other apps that produce plain text files when I want a fancy report. This method gets cool points if you create the spreadsheet and actually email it from the mainframe. I first tried this with to create reports for a football pool a friend of mine used to run(I do now) back when excel added the html converted thingie to it's functionality. This process sounds complicated but it's really not. In fact it can be a real time saver if you have a mainframe process for which you need to repeatedly create a fancy report. Here is the basic process:

1 Create the file the way you want it to look in excell. Could be basically anything you like from more of a fancy report to something more complicated. As long as you can save it to html from within excel, this will work.

2 If this the file will basically be a static size, fille in the data fields with place holder dummy values. I used something like V_FNAME.

3 If this fits the format of a header, body(detail rows), and footer, then just make one row of data using the dummy values.

4. Save the spredsheet as html. Later the file can be renamed to .xls and although is has html or xml or whatever, the xls extension prevents it from being opened in a browser. On the other hand, if you'd prefer your audience to use a browser leave it as html.

If #3 was true,

5. edit the html and put in comments that mark the begging and ending of your data row. So the body is separated from the header and footer. Actually it might be better to separate them into three files.

6. Modify your program so that it creates a duplicate of the 'body' file above.

7. Concatenate the 3 files and put the result wherever you want.

8. You might also want to process the header and footer separately if they contain things that would normally change such as date or something.

else if #3 above is false and this report is more of a static format like that in my attachment,

9. Read the file with the dummy values and replace them with real values. I usually do this be creating arrays for each of the dummy value 'types' and then read each row and replace values as a appropriate. I have other methods of doing this but this is the one I use most.

That's basically it. For cases in which you need just a simple spreadsheet, you would most likely stick to the easy csv mothod.

Using this same approach I create the attachment in dbase vi in a jiffy. I've also used this method on the mainframe so create more professional looking reports that are in spreadsheet form that get sent straight to the people who need to work with them. This is nice because there is absolutely nothing they need to do before using the spreadsheet.

I'm in linux so the attachments won't actually be of excel. Instead I'll post one opened in firefox and one opened in openoffice. Openoffice doesn't look as good as the excel since I created this in excel. The same approach could be used with oo though.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jul 13, 2006 3:27 pm    Post subject:
Reply with quote

Hi jasorn,

Very valuable suggestions.
Thanks for that.
Back to top
View user's profile Send private message
jasorn

Active User


Joined: 12 Jul 2006
Posts: 153
Location: USA

PostPosted: Thu Jul 13, 2006 5:35 pm    Post subject:
Reply with quote

parikshit123 wrote:
Hi jasorn,

Very valuable suggestions.
Thanks for that.


Sure thing.

Using excel as a 'report painter' this way has served me well. This is especially true in shops which don't have a tool for this purpose. Most places have at least one office product that you can use for this purpose but I haven't seen many that have a product specifically for creating reports that as flexible and easy as this simple, nothing else to buy, method.

You could use this method with any product that uses a plain text format like xml which ms office uses now. I don't think it would have worked too well in the old format. The fact that word and excel also creat html is a bonus. And it's an extra, extra bonus that you can rename it's .html output to .xls and it opens just fine.

Normally I wouldn't recommend either for creating html files but it's a workable solution in this case.

For those who want to implement this but needs some clarification on my long, convaluted post, let me know and we can talk it through.

I'd be interested too in hearing from those who alread have or do in the future use this method.
Back to top
View user's profile Send private message
jasorn

Active User


Joined: 12 Jul 2006
Posts: 153
Location: USA

PostPosted: Thu Jul 13, 2006 11:06 pm    Post subject: Re: canwe write a program to put the contents of tableinto E
Reply with quote

From this thread I learned that if you tab delimit instead of , and write out a *.csv excel will open it without the import wizard, too. This helps the people who want a simple spreadsheet that the user can just open.

http://ibmmainframes.com/viewtopic.php?t=6057&highlight=csv
Back to top
View user's profile Send private message
jasorn

Active User


Joined: 12 Jul 2006
Posts: 153
Location: USA

PostPosted: Thu Jul 13, 2006 11:15 pm    Post subject: Re: canwe write a program to put the contents of tableinto E
Reply with quote

jasorn wrote:
From this thread I learned that if you tab delimit instead of , and write out a *.csv excel will open it without the import wizard, too. This helps the people who want a simple spreadsheet that the user can just open.

http://ibmmainframes.com/viewtopic.php?t=6057&highlight=csv


Also, the sylk format as pointed out in that post is pretty good for this general purpose and might be easier for folks who want to format the output a bit and for whatever reason rules out the other options.

I think the bottom line is there is a pretty easy method for creating snazzy reports and spreadsheets and sending them straight to a pc user for use with no prep work for the end user. And perhaps no reason for many of use to use those fancy tools and add ons that can be a little pricey.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm
No new posts pre-compiler for program in an HFS Pedro DB2 0 Fri Jun 23, 2017 11:13 pm
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us