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



Tips & Tricks for PC Optimization and tweaks, Freeware and shareware downloads related to mainframes

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

Postby bitbybit » Thu Dec 13, 2012 9:51 pm

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.
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

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

 

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

Postby Akatsukami » Thu Dec 13, 2012 9:54 pm

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.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby dick scherrer » Thu Dec 13, 2012 10:32 pm

Hello,

Tell them to expand the Excel cell and see if this provides the desired result.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6269
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby Robert Sample » Thu Dec 13, 2012 11:33 pm

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.
Robert Sample
Global moderator
 
Posts: 3625
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 267 times

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

Postby bitbybit » Fri Dec 14, 2012 1:40 am

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.
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

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

Postby Akatsukami » Fri Dec 14, 2012 2:19 am

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.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

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

Postby dick scherrer » Fri Dec 14, 2012 2:59 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6269
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby bitbybit » Fri Dec 14, 2012 3:09 am

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...
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

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

Postby dick scherrer » Fri Dec 14, 2012 3:43 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6269
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby BillyBoyo » Fri Dec 14, 2012 6:18 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to PC Optimization & Downloads