IBM Mainframe Forum

by **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.

by **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.

by **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.

by **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. . .

by **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.

by **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:

Select all

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.

IBM Mainframe Forum

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

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

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

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