Page 1 of 2

FTP M/F file opened in XL and 14 bypte number field

PostPosted: Thu Dec 13, 2012 9:51 pm
by bitbybit
I have a M/F file that is FTPed to a server using some in-house midrange tool. When the FTPed file opened by customer in XL, one of the fields defined in COBOL report as X(14), which has all numbers will show up in scientific format. For example, value 12345678901234 will appear as 1.23457E+13. Customer wants to see it as 12345678901234. Is there anything I can do in my cobol program to force it to appear in XL sheet as 12345678901234, instead of 1.23457E+13. Thank you.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Thu Dec 13, 2012 9:54 pm
by Akatsukami
Probably not; IIRC that is an Excel setting, nothing to do witth he data. It's unfortunate that your customer insists on using such inferior tools, but there is nothing to be done but pat heesh on the head and murmur sympathetically.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Thu Dec 13, 2012 10:32 pm
by dick scherrer
Hello,

Tell them to expand the Excel cell and see if this provides the desired result.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Thu Dec 13, 2012 11:33 pm
by Robert Sample
Is there anything I can do in my cobol program to force it to appear in XL sheet as 12345678901234, instead of 1.23457E+13.
Sure -- put quote marks around it so it becomes a text field instead of a number. If it needs to be a number instead of text, then the answer is that there is NOTHING you can do in your COBOL program to change the way the value displays in Excel.

And, by the way, this is NOT a script kiddie forum. If you meant Excel, spell it out -- do not abbreviate a five-letter word to two letters. Using SMS text abbreviations just causes you to be considered immature and unprofessional -- hardly good attributes for someone wanting an IT career.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Fri Dec 14, 2012 1:40 am
by bitbybit
I tried the putting the quotes or inserting Hyphen, those are working, but, teh customer came back saying, they didnt like it, they just want to see the numbers in Excel column, not the scientific format that it defaults to as the number gets larger. I tried to increase the column width in Excel sheet, it doesnt help, still shows in the Scientific format. I just wanted to seek help here, before I tell my customer that is how its going to be, can not help it. I have asked them to open in text pad, since there it will show it correctly. Just asking if there is any way we can control that in COBOL Program when we are creating that file/report. Thank you.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Fri Dec 14, 2012 2:19 am
by Akatsukami
If you want to bother having your COBOL program write the data encapsulated in HTML, you can control the column widths at least in Excel 2010. Otherwise, once again: this is an Excel failing, not a COBOL failing.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Fri Dec 14, 2012 2:59 am
by dick scherrer
Hello,

Or a customer failing (yup, they surely dlo). They should present the requirement and not be concerned with the internal processing.

Why do they object to enclosing these fields in quotes?

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Fri Dec 14, 2012 3:09 am
by bitbybit
when I send them the file with quotes around that number, the response I got from my PM is "the customer is begrudginly accepting the file for now, but they are not happy with it, we still need to find out how to fix the issue"!!!!. So I am trying...

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Fri Dec 14, 2012 3:43 am
by dick scherrer
Hello,

Suggest to the PM that WHY they are not happy needs to be understood. Groups i've supported have had some unreasonable customers but something like this was never an issue . . .

IMHO, you Have Already fixed the issue :?

If there is some "real" issue (more than personal preference), more investigation might be warranted, but i just don't see a need to put more time in this.

Re: FTP M/F file opened in XL and 14 bypte number field

PostPosted: Fri Dec 14, 2012 6:18 pm
by BillyBoyo
You state that the number on your Cobol file looks how your customer would like it to be seen, even without the quotes.

In Excel, as has been said, if the input is "too big" to show in a cell, then it will be shown differently, the scientific notation being a possibility.

If the client, as has already been suggested, makes the cell "bigger", then there will be no need for Excel to use the scientific notation.

If the data on your file was in scientific notation, it could/would still be displayed how the client wants it to be displayed, as long as the cell is "big enough".

Now you have to get someone to politely show the client how to change their own spreadsheet so that they can see your data, how they want to see it. As has been said.