MVSFORUMS.com

santi.sworup · Beginner Joined: 24 Jul 2010 Posts: 3 Topics: 2

Hi

My code is in Mainframe COBOL. I am able to send Email with an Excel attachment successfully thru JCL. The problem is if the data is plain number.. the leading zeros are removed..

I want the numbers to be displayed as 000089 instead of just 89.

To implement this fix, I double quote each field with leading zeros or spaces and place an equals sign (=) directly ahead of the first quote, like this below:
="000089"

It works . But user will use the excel file to be uploaded into their system. The =" gives them error.

I want to fix the issue in mainframe cobol. Is there anyway to fix this issue?
While sending the data as email attachment thru JCL , can we have any way to let excel treat my data as text type?

Please help me in solving this issue.

Nic Clouston · Posted: Mon Jul 26, 2010 1:50 am Post subject:

Why use = ??? Surely just quoting the text is enough?
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

Anuj Dhawan · Posted: Mon Jul 26, 2010 4:31 am Post subject:

I believe that's a "property of spread-sheet(excel)" - is the column-of-spread-sheet in which you populate the above said values is defined as "text" instead of "number"? If the column is defined as "number" - "000089" will be converted to "89".
_________________
Regards,
Anuj

santi.sworup · Beginner Joined: 24 Jul 2010 Posts: 3 Topics: 2

yes..
The property of Excel is like if it finds a numeric value in the starting of a column, it will treat the field as numeric.
So even if we are marking the column as text in our program, the Excel property overrides it.
_________________
Santi

kolusu · Posted: Tue Jul 27, 2010 10:22 am Post subject:

santi.sworup,

Send the same file one as excel and the other as plain text file without the = and quote. The text file without the = and quotes will be used to load the table and the other to open in excel.

Kolusu

Franko · Posted: Tue Jul 27, 2010 12:18 pm Post subject:

Try formatting the entire column: 1) Format cells, 2) Custom, 3) 000000. This should provide the leading zeros you require.

Anuj Dhawan · Posted: Wed Jul 28, 2010 12:08 am Post subject:

misi01 · Posted: Thu Aug 12, 2010 3:53 am Post subject:

... or simply try appending one single quote in front of the numeric values whose leading zeroes you want to retain

Cogito-Ergo-Sum

misi01 · Posted: Sat Aug 28, 2010 2:51 am Post subject:

Funny - if you google this problem you find both suggestions (one leading quote and ="0000number")

Just tried testing it myself with a CSV file on my desktop with the following contents

kolusu · Posted: Sat Aug 28, 2010 1:18 pm Post subject:

misi01,

Check this post and the solution posted by me

http://www.mvsforums.com/helpboards/viewtopic.php?p=41425#41425

Kolusu

misi01 · Posted: Sun Aug 29, 2010 2:03 am Post subject:

Hi Kolusu. I read your linked article, but as I mentioned in my append just above, it all depends on how you open the data file.

Michael

Crox · Beginner Joined: 29 May 2004 Posts: 52 Topics: 9

it is possible to create a totally other kind of type file to download to excel. Did you ever investigate the SYLK format? The normal extension for SYLK is .SLK

You can create a true spreadsheet with that, with all kinds of extra features compared to csv like calculating rules, colors, shade, things like that.

Also it is possible to create the sheet in a completely other order than it is shown. In the Sylk format it is possible to write the last cell first and the other way around. I use SYLK a lot.