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

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

Postby bitbybit » Fri Dec 14, 2012 7:56 pm

I might get a quick call with the client today.
Billyboyo...Making cell bigger did not work. It still shows it in formula. Whenever the number is greater than 11 digits, it will show in formula format. I found a way to show it in excel always in digits, not the formula. select that column and format the cell as number, then it will show the whole number. but they have to do it every time they open this file. they didnt like that either. The problem is whenever Excel opens a CSV file, it defaults all the columns to "GENERAL" format, in that format, numbers larger than 11 digits are shown as formula. my field is 14 digits. I am throwing today another idea to the client that I can comine that number field with either one of the fileds befre or after it, so it is not a pure number anymor and will show all numbers. I have to see how it goes.
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 BillyBoyo » Fri Dec 14, 2012 9:04 pm

It should be possible to arrange that an Excel Macro can be executed each time "something" is opened: "something" depending on how they are using the .CSV. That MACRO can then change column widths, formatting, whatever.

I don't understand where the "formula" comes into it. I have just typed 14 digits into a cell, and it displays with 1E+023 in the cell, but as the correct value in the input bar. When I make the cell bigger, I get to see 12345678901234. This should work the same from a .CSV.

I think for more specific help, you may need to do a bit of googling or find an Excel support site. I also don't see that there is anything you can do in the Cobol program directly to affect the way Excel operates.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

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

Postby bitbybit » Fri Dec 14, 2012 9:32 pm

I am using office-2007 and I just open Excel and typed the same thing and expanded the cell width, it still shows 1.23457E+13, only the input bar has 12345678901234. Even if I cut&paste from that cell, it will display as 1.23457E+13 where ever you are pasting. Thank you for the response. I am still waiting to hear from the client, if they liked my idea about combining the 2 columns to make it character data.
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 9:42 pm

Hello,

I just open Excel and typed the same thing and expanded the cell width, it still shows 1.23457E+13, only the input bar has 12345678901234
Then you have made some mistake. . . Or your Excel is configured differently.

Look at BillyBoyo's reply immediately before yours. I too get the "real" number, not what you report.

I am not sure why time (=$$$) is still being invested in this. . .

What the user should be aware of is that:
A mainframe process ran.
A file was downloaded.
This data was imported into Excel.

As long as all of this worked, data/files used along the way should be of no concern to the user. . . :(
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
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 » Fri Dec 14, 2012 9:55 pm

Since this topic has ABSOLUTELY nothing to do with COBOL and everything to do with Excel, I've moved the entire topic.
Robert Sample
Global moderator
 
Posts: 3720
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

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

Postby Ed Goodman » Mon Dec 17, 2012 9:09 pm

I have beaten this by using XML format for the Excel workbook. If you want to see what it looks like, go into Excel and save the spreadsheet as XML. If you create that format, then you are golden.

I will try to post the Sort cards I used, but they may not make sense:
OUTFIL FTOV,OUTREC=(C'   <Row>',/,                               
               C'    <Cell><Data ss:Type="String">',             
               002,002,C'</Data></Cell>',/,           county     
               C'    <Cell><Data ss:Type="String">',             
               007,006,C'</Data></Cell>',/,           workerid   
               C'    <Cell><Data ss:Type="String">',             
               016,015,C'</Data></Cell>',/,           name last 
               C'    <Cell><Data ss:Type="String">',             
               034,015,C'</Data></Cell>',/,           name first
               C'    <Cell><Data ss:Type="String">',             
               052,001,C'</Data></Cell>',/,           name mi   
               C'    <Cell><Data ss:Type="String">',             
               056,012,C'</Data></Cell>',/,           indv
.
.
.
REMOVECC,                                                           
HEADER1=(C'<?xml version="1.0"?>'/,                                 
C'<Workbook  xmlns="urn:schemas-microsoft-com:office:spreadsheet"',/,
C'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >',/,     
C' <Worksheet ss:Name="Put a nice name for the sheet here">',/,       
C'  <Table>',/,                                                     
C'   <Row>',/,                                                       
C'    <Cell><Data ss:Type="String">County</Data></Cell>',/,         
C'    <Cell><Data ss:Type="String">Worker</Data></Cell>',/,         
C'    <Cell><Data ss:Type="String">Last Name</Data></Cell>',/,       
C'    <Cell><Data ss:Type="String">First Name</Data></Cell>',/,     
C'    <Cell><Data ss:Type="String">MI</Data></Cell>',/,             
C'    <Cell><Data ss:Type="String">Individual</Data></Cell>',/,     
.
.
.
C'   </Row>'),                                                     
TRAILER1=(C'  </Table>',/,                                         
C' </Worksheet>',/,                                                 
C'</Workbook>')                                                     


In a nutshell, it takes a flat file and builds an XML from it. In my example the "Individual" field is a twelve byte number that was doing exactly what you are describing. When we did this format, that problem went away.

Yes, I know it's clever, and will do exactly what you need, but don't gush...it's embarrassing.
Ed Goodman
 
Posts: 341
Joined: Thu Feb 24, 2011 12:05 am
Has thanked: 3 times
Been thanked: 17 times

Previous

Return to PC Optimization & Downloads