Page 1 of 2

help me in creating EXCEL document as OUTPUT FROM COBOL PROG

Posted: Tue Jan 22, 2013 12:46 pm
by amareesh1
Hi All,

Please help me. I'm new to programming and now I'm dealing with COBOL. I want to create excel document with some named rows and columns. Please suggest me and help me to achieve this.
As I am fresher I want to know deeply on what to do ? and How to do?

Thanks

Posted: Tue Jan 22, 2013 4:04 pm
by William Collins
An EXCEL document is very complex. Not actually a suitable task for a beginner from Cobol with no additional software package to achieve it.

Your best bet is to create a simple "delimited" file which can be opened in EXCEL and will then appear as "columns".

Choose a delimiter, like | which is unlikely to appear as actual data.

Posted: Tue Jan 22, 2013 4:11 pm
by amareesh1
Hi Collins,

Thanks for your reply and suggestion.
Can you please give me some tips to write a program to generate a delimited file.

Posted: Tue Jan 22, 2013 4:30 pm
by William Collins
It is like writing a "normal" sequential file except between each actual field you have an extra field which you set to your delimiter value.

Code: Select all

01  a-record.
    05  a-field-no-1 PIC X(20).
    05  a-delimeter-1 PIC X.
    05  a-field-no-2 PIC X(12).
    05  a-delimeter-1 PIC X.
    05  a-field-no-3 PIC 9(9).
This is a very simple example, but should show you how to get data into EXCEL as a "delimited file".

Posted: Tue Jan 22, 2013 6:29 pm
by amareesh1
thanks Collins

I have tried the record structure like this. what is the logic to get data in to EXCEL?
Thanks for your assistance.

Posted: Tue Jan 22, 2013 6:34 pm
by William Collins
Google for "opening a CSV in EXCEL" or "open a delimited file in EXCEL"

Posted: Tue Jan 22, 2013 7:01 pm
by amareesh1
But I want feed data in to .CSV file through cobol program. how to do that.

Posted: Tue Jan 22, 2013 10:09 pm
by DikDude
Define a sequential output file in your program.
Open it as output.
Write the delimited records to this file.
Close the file

Transfer the file to the remote where Excel is to run with a .csv extension (ftp is rather easy and the ftp client is on every PC i've seen for a long time). You will need to learn how to "pull" the file from your mainframe to the remote - ask your network people)

Open EXCEL and import the file and proceed from there.

If you want Headers for the columns in the spreadsheet, write an extra record first to the output file that contains the column-names and the pipe-delimiter.

Posted: Mon Jan 28, 2013 3:33 pm
by Anuj Dhawan
I have tried the record structure like this. what is the logic to get data in to EXCEL?
Excel is a windows (in general) software. You need to transfer the data first to a window-box from zOS -- where you create the file. As said before, while transfering the file with the choice of options you pick from -- you can name it as "file.csv"; double click on it and it will open in excel.

Posted: Wed Apr 16, 2014 6:19 pm
by Attack1988
Hi I have an idea .. First convert into CSV file .. Then using command prompt , u can get it as excel .. once u give ceratin command .. If u want more on this will give u ;) i have solved it in my project :)

Posted: Wed Apr 16, 2014 9:07 pm
by NicC
Attack1988,

This topic is now over a year old so the TS is probably well past that particular program.
Why convert whatever into a CSV file - just create it as a csv file in the first place - which is wht was advised to the TS.
What command prompt?

The proper sequence is:
1 - Create CSV dataset in COBOL
2 - Transfer the dataset to the server/PC/whatever
3 - Double click (single-click in my set-up) on the file on the server/PC/whatever and it will be opened by Excel

Posted: Thu Apr 17, 2014 9:07 am
by Attack1988
NicC,

Hi
I use command prompt to transfer files .. between mainframe and windows .. i think we have to login to mainfrme using ftp
changer usr to our id .. then give mget 'filename' u havent heard about this method .. ?
It can be used for multiple file transfer

Posted: Thu Apr 17, 2014 9:12 am
by Attack1988
Creating a File in CSV using cobol ..will that not make program litttle bit complex ? I used to creat normal file .. and using JCL Squeeze option .. I generate CSV .. its kind a easy for me .. and keeps my cobol easy to read for everyone..
and also problem will happen .. say If I
have variable like X(20) .. I will occupy extra space .. right .. ?
then CSV will be having records like
NAME ,ADDRESS , NUMBER ,
but if i want like
NAME,ADDRESS,NUMBER
Squeeze Delimit in JCL will work good ;)

Posted: Thu Apr 17, 2014 1:01 pm
by NicC
It does not make the cobol program any more complex as you can see from the sample code above - just a few extra fields to store the delimiter and they can be given a value at declaration. Or you can string the fields together delimited by size or space or whatever is appropriate with the delimiter between each field. That is how I do it - no extra spaces.

JCL does not have a Squeeze option - if you do not believe me look at the index of the JCL Language reference manual. If you can find it there plese give the version number of the manual, level of JCL and z/OS it refers to as I will have to download it to keep my library up to date.

Posted: Mon Apr 21, 2014 10:18 am
by Attack1988
NicC,

IF U WANT TO INSERT CHARACTERS .. delim IS THERE .. DUNNO THE SYNTAX .. OK

SO TRY USING MID
U CAN ALSO ALIGN SHIFT, LEFT AND CENTRE

OUTREC FIELDS=(1,80,SQZ=(SHIFT=LEFT,MID=C','))