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: 6968
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: 6968
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: 8121
Location: East Dubuque, Illinois, USA

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 Split file based on comparsion to 2 f... Div Grad DFSORT/ICETOOL 3 Thu Jul 27, 2017 9:16 pm
No new posts Extra character appears in file when ... Balu5491 All Other Mainframe Topics 1 Wed Jul 26, 2017 2:39 pm
No new posts SSH - known_hosts file configuration vasanthz All Other Mainframe Topics 2 Wed Jul 26, 2017 2:10 am
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Mainframe Opening in Chennai ( 2 to 4... muralikrishnan_new Mainframe Jobs 0 Tue Jul 18, 2017 2:20 pm


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