help me in creating EXCEL document as OUTPUT FROM COBOL PROG

This is a Mainframe COBOL forum - you can post your queries on Mainframe COBOL, VS COBOL II, COBOL/370 , Enterprise COBOL

Moderators: dbzTHEdinosauer, Moderator Group

amareesh1
Member
Posts: 4
Joined: Wed Apr 25, 2012 2:44 pm
Location: chennai

help me in creating EXCEL document as OUTPUT FROM COBOL PROG

Post by amareesh1 » Tue Jan 22, 2013 12:46 pm

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

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Tue Jan 22, 2013 4:04 pm

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.

amareesh1
Member
Posts: 4
Joined: Wed Apr 25, 2012 2:44 pm
Location: chennai

Post by amareesh1 » Tue Jan 22, 2013 4:11 pm

Hi Collins,

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

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Tue Jan 22, 2013 4:30 pm

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".

amareesh1
Member
Posts: 4
Joined: Wed Apr 25, 2012 2:44 pm
Location: chennai

Post by amareesh1 » Tue Jan 22, 2013 6:29 pm

thanks Collins

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

William Collins
Active Member
Posts: 732
Joined: Thu May 24, 2012 4:07 am

Post by William Collins » Tue Jan 22, 2013 6:34 pm

Google for "opening a CSV in EXCEL" or "open a delimited file in EXCEL"

amareesh1
Member
Posts: 4
Joined: Wed Apr 25, 2012 2:44 pm
Location: chennai

Post by amareesh1 » Tue Jan 22, 2013 7:01 pm

But I want feed data in to .CSV file through cobol program. how to do that.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Tue Jan 22, 2013 10:09 pm

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.
Have a good one

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Mon Jan 28, 2013 3:33 pm

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.
Regards,
Anuj

Attack1988
Member
Posts: 18
Joined: Tue Apr 15, 2014 4:18 pm

Post by Attack1988 » Wed Apr 16, 2014 6:19 pm

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 :)
Attack1988

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Wed Apr 16, 2014 9:07 pm

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
Regards
Nic

Attack1988
Member
Posts: 18
Joined: Tue Apr 15, 2014 4:18 pm

Post by Attack1988 » Thu Apr 17, 2014 9:07 am

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
Attack1988

Attack1988
Member
Posts: 18
Joined: Tue Apr 15, 2014 4:18 pm

Post by Attack1988 » Thu Apr 17, 2014 9:12 am

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 ;)
Attack1988

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Thu Apr 17, 2014 1:01 pm

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.
Regards
Nic

Attack1988
Member
Posts: 18
Joined: Tue Apr 15, 2014 4:18 pm

Post by Attack1988 » Mon Apr 21, 2014 10:18 am

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','))
Attack1988

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others