canwe write a program to put the contents of tableinto Excel

mfstudent1 · New User Joined: 03 Mar 2006 Posts: 18

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???

amitava · Active User Joined: 30 Oct 2005 Posts: 186 Location: India

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:

superk · Posted: Sat Mar 04, 2006 12:35 am

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?

pspreddy · New User Joined: 14 Mar 2006 Posts: 24 Location: Chicago

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

martin9 · Posted: Fri Mar 17, 2006 5:54 pm

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

prasanth_thavva · New User Joined: 28 Jul 2005 Posts: 86 Location: Chennai

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

pspreddy · New User Joined: 14 Mar 2006 Posts: 24 Location: Chicago

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

KS · New User Joined: 28 Feb 2006 Posts: 91 Location: Chennai

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

jasorn · Active User Joined: 12 Jul 2006 Posts: 191 Location: USA

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.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi jasorn,

Very valuable suggestions.
Thanks for that.

jasorn · Active User Joined: 12 Jul 2006 Posts: 191 Location: USA

jasorn · Active User Joined: 12 Jul 2006 Posts: 191 Location: USA

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

jasorn · Active User Joined: 12 Jul 2006 Posts: 191 Location: USA