Re: spool excel file with sql*plus

With all due respect to the great deal of effort Sybrand and Daniel put into this ng, there are legit reasons for getting data to end users in a form they are comfortable with and can manipulate ( or, "cook the numbers" to quote). Sometimes I just need to get data out to people who are outside our firewall and have no access to the database.

With that aside, there is a very peculiar problem with exporting text in .CSV format. It seems that when Excel imports a zero prefixed string such as "00456", it automatically converts it to a number of the form 456 (number). I have found that you can get around this by creating a comma separated file but giving it a .txt extension. This forces Excel to use the Import Wizard and you can declare that column as a string. A little extra work, but you get the intended result.

I haven't tried out this script, so I can't endorse it, but if you go to www.asktom.oracle.com and Search/Archives for "Excel", there is a PL/SQL script which will produce output in SYLK format (which I believe goes back all the way to the Multiplan days). The archive is entitled "Owa_sylk utility".

Cheers,
js

"Eric J. Givler" <ericjgivler_at_earthlink.net> wrote in message news:C6rF8.2879$8t.1900_at_newsread2.prod.itd.earthlink.net...
> I agree with Sybrand here. I had problems with Forms and OLE trying to
> create an Excel file and I just switched over to a TEXT_IO routine that
> wrote the CSV file. It took less time to write/troubleshoot, and it is
> associated to Excel by default. So, all you do is double click on the
file
> and you are good to go. If they want an excel file, then do a save as.
>
>
Received on Mon May 20 2002 - 05:23:06 CEST