View previous topic :: View next topic
|
Author |
Message |
Kumaresan.nk
New User
Joined: 22 Mar 2010 Posts: 31 Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Kumaresan.nk
New User
Joined: 22 Mar 2010 Posts: 31 Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Kumaresan.nk
New User
Joined: 22 Mar 2010 Posts: 31 Location: Chennai
|
|
|
|
Thanks, pal. I'll try that. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8697 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|