Re: .CVS to .XLS



"James J. Gavan" <jgavandeletethis@xxxxxxx> wrote in message
news:TW1Nl.10721$CN5.245@xxxxxxxxxxxxxxx
Paul H wrote:
Per my post named "XLS, not .CSV from NETX?" dated Tuesday, March 24,
2009 1:16 PM I have solved my problem by having the COBOL program
create a .CSV file, then calling a program that reads the .CSV file
and creates a .XLS file, with the headings bold, the columns sized
correctly, etc. It uses the invoke command extensively. I need one
that will cause a field that is expressed as .1234 to display as
12.34% - does one exist? TIA, Paul


You're sure having fun with this one. If you don't have success in setting
up the Excel Work*** - how about this 'pick and shovel' approach,
*providing* users are not recalculating your initial percentage displays.


Even if they are not doing so today, who knows what they will do tomorrow?
The fact that they have requested the data asa spread*** implies they may
havefurther uses for it.

From above you are sending a COBOL file as a CSV to Excel. You are storing
the percentage in a COBOL file as pic v9(04); (that appears weird for a
start !), and then you want to decimalize .1234 to become 12.34 in Excel.

Regardless of storage format calculate, (insert decimal point) and result
in usage display :-

01 PercentField pic zz.99. *> the value in this field goes *> into your
CSV record

.1234 becomes 12.34 using PercentField; format Excel so that the
Percentage field is a four/five character text field = pic x(04) ???

Even if you resolve it within Excel, it would make sense to send the
percentages via the CSV as 99.99 instead of v9999.

Jimmy

I don't think so.

The obvious easy solution is sometimes not the best one. ("All ya gotta do
is..." - Doc Dwarf)

No Jimmy, it is standard practice for percentages to be expressed as .99 or
..9999 in applications which use spreadsheets. That's why Excel provides
properties to "translate" the fraction into a formatted percentage on
output.

Although you, quite properly, added the disclaimer about further processing,
there is no guarantee that further processing will not occur, and even if it
doesn't, you have now broken a convention as to how percentages are stored
in spreadsheets.

If he did what you say, the target spread*** would have to contain a macro
to "deformat" the display string so it could be used in further calculation.
Otherwise, all the percentages would be wrong if the *** is manipulated or
passed on for any further processing. Breaking an accepted convention which
then requires further processing before you can use it, solves an immediate
short term problem, but it creates a longer term one, which is confusing for
people who expect percentages to be recorded in accordance with convention.

Best avoided.

The Excel solution explained previously is not difficult and it means the
cell retains the value it is expected to have; it just displays it in a
"percent" format.

Maybe you could post some Micro Focus OO COBOL (Net Express?) that shows how
to invoke these properties when using Excel via Automation?

That would be really useful...

I haven't been able to locate any Net Express documents that have samples of
COM (Automation) processing. Do you know if there are examples in the docs
that come with the package?

Pete.
--
"I used to write COBOL...now I can do anything."


.