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
 

 

Format Excel File in Mainframe.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
Kumaresan.nk

New User


Joined: 22 Mar 2010
Posts: 31
Location: Chennai

PostPosted: Thu Dec 30, 2010 12:29 pm    Post subject: Format Excel File in Mainframe.
Reply with quote

Hi,

I have converted a flat file to an excel file using a COBOL program by inserting X'05' in between the fields. This works fine. My concern now is that I need to format the fields in excel. Because some of my fields have leading and trailing zeroes in it and when I open the excel file in PC, all the leading and trailing zeroes are evaded. The excel file in mainframe has all the zeroes in tact. Is there any way I can format this in mainframe itself ?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Dec 30, 2010 1:12 pm    Post subject:
Reply with quote

when you OPEN the .cvs file in EXCEL on the PC,
you have a window where you indicated the x'05' is the field delimiter.
You have additional windows in the dialog where you can define the datatype of each field.

once you figure out what datatype you want each field to be,
you can write a macro,
which you can then deliver to your customer.

BUT FIRST YOU HAVE TO LEARN ABOUT EXCEL
Back to top
View user's profile Send private message
Kumaresan.nk

New User


Joined: 22 Mar 2010
Posts: 31
Location: Chennai

PostPosted: Thu Dec 30, 2010 1:31 pm    Post subject:
Reply with quote

Hi,

I'm directly sending the file to the customer as a zip file, once it's been converted to excel in the job itself. So, what I'm looking is, if it's possible to format the fields in mainframe itself without any manual intervention.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Dec 30, 2010 2:29 pm    Post subject:
Reply with quote

creating an .xls type file on the mainframe
has been discussed before. search the forum.


as I said,

create the excel macro that the customer can use to import the cvs file.

Learn EXCEL if you want to generate all the bit-wise stuff you need for a Microsoft Excel .xls file. but then you would probably have to FTP the file in binary. But, I don't know, I always use the Excel Macro to import the .cvs file.
Back to top
View user's profile Send private message
Kumaresan.nk

New User


Joined: 22 Mar 2010
Posts: 31
Location: Chennai

PostPosted: Thu Dec 30, 2010 2:47 pm    Post subject:
Reply with quote

Thanks, pal. I'll try that.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7931
Location: Bellevue, IA

PostPosted: Thu Dec 30, 2010 5:33 pm    Post subject:
Reply with quote

The Microsoft Excel file formats manual is something over 1,000 pages long and changes with every release of Excel. In order to generate an actual .xls or .xlsx file on the mainframe, you either need to buy a package to do this (cost will be tens of thousands of dollars or more) or write code based on your reading the 1,000+ page manual to generate the correct data -- which may take 1000 to 2000 hours of effort, and will have to be maintained.

It is much easier, and simpler, to generate a comma-delimited .csv file and let Excel do the work when you open the file.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Dec 30, 2010 8:35 pm    Post subject:
Reply with quote

Hello,

Quote:
I have converted a flat file to an excel file using a COBOL program by inserting X'05' in between the fields.
Your very first words are incorrect - which has caused you confusion. . . The code had NOT created an Excel file - it has created a tab-delimited file. Very Big Difference.

As others have mentoned, you need to provide for the desired formatting on the (User's) PC or your organization will need to invest in the software and skills to really create an Excel file on the mainframe.
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 -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts Produce Mainframe Dash Board Reports ... sdcarlson53 All Other Mainframe Topics 2 Thu Jan 05, 2017 11:01 pm
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
This topic is locked: you cannot edit posts or make replies. Urgent Mainframe Requirments (2-10 yr... Shankar779 Mainframe Jobs 0 Sun Nov 27, 2016 1:48 pm


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