Spool decimal value to csv file [message #351431] |
Tue, 30 September 2008 16:26 |
shoaib123
Messages: 118 Registered: December 2007 Location: Chicago
|
Senior Member |
|
|
HI,
I need to spool amount field to the csv file in such away that If the amount field has zeros after decimal, it should load that amount value in the csv file with zeros after decimal..
I am successfully able to spool those decimal values which contain digits other than zeros after decimals. (like 1234...)
i am trying something like this...
spool file.csv
select
'"'col1||'","'||
to_char(amount_field,'9999.99')||'"'
from table1
any hint would appreciated !!!
|
|
|
|
Re: Spool decimal value to csv file [message #351537 is a reply to message #351449] |
Wed, 01 October 2008 07:52 |
shoaib123
Messages: 118 Registered: December 2007 Location: Chicago
|
Senior Member |
|
|
Oracle 9.2.0.8.0 window professional
I am creating output file on window.. So, after spool i am getting the output amount field value in the csv file like this....
Amount_field
--------
1234
1234.99
The field value which i am currently getting is 1234 but somehow it should be 1234.00
|
|
|
Re: Spool decimal value to csv file [message #351539 is a reply to message #351537] |
Wed, 01 October 2008 07:58 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
shoaib123 wrote on Wed, 01 October 2008 08:52 | Oracle 9.2.0.8.0 window professional
I am creating output file on window.. So, after spool i am getting the output amount field value in the csv file like this....
Amount_field
--------
1234
1234.99
The field value which i am currently getting is 1234 but somehow it should be 1234.00
|
If you ran the code ebrian gave you, you will have what you need. What's the problem? Obviously you ran some other incorrect code.
|
|
|
|
Re: Spool decimal value to csv file [message #351541 is a reply to message #351540] |
Wed, 01 October 2008 08:25 |
shoaib123
Messages: 118 Registered: December 2007 Location: Chicago
|
Senior Member |
|
|
SQL> desc test;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
COL1 VARCHAR2(12) Y
COL2 DATE Y
SQL> desc test;
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
COL1 VARCHAR2(12) Y
AMOUNT_FIELD NUMBER Y
SQL> spool C:\temp\file.csv
Started spooling to C:\file.csv
SQL> set head off;
SQL> select
'"'||col1||'","'||
trim(to_char(amount_field,'9999.99'))||'"'
from test
"Hoe","23.00"
"Joe","123.99"
SQL> spool off
Stopped spooling to C:\temp\file.csv
Upto here everything looks perfectly ok.. But when i go to C:\temp folder and double click on the file.csv and open it up. Here is what i am getting....
The amount field which i am expecting to be 123.00 in the csv file, it is 123. The two 0's after the decimal sign is missing..
|
|
|
|
|
Re: Spool decimal value to csv file [message #351544 is a reply to message #351542] |
Wed, 01 October 2008 08:40 |
shoaib123
Messages: 118 Registered: December 2007 Location: Chicago
|
Senior Member |
|
|
Joy..
Appreaciate your time on my question..
I knew that there is no different between, 23 and 23.00,0.00 and 0..
But the business peroples is not getting that.. they want to see there two 0's after the decimal inthe output csv file...
Like for exampke...
For 0 it should be 0.00, for 123 it should be 123.00...
|
|
|
Re: Spool decimal value to csv file [message #351546 is a reply to message #351543] |
Wed, 01 October 2008 08:44 |
shoaib123
Messages: 118 Registered: December 2007 Location: Chicago
|
Senior Member |
|
|
Mike...
Apreacite your valuable time on my question..
I can defintely change the format of that cell in the csv file now, as long as i am staying in UAT... But this process need to go in the prod, and as we know, in prod everything needs to be automate... I cannot go there and change the format manually...
Once again thanks for you time..
|
|
|
Re: Spool decimal value to csv file [message #351547 is a reply to message #351544] |
Wed, 01 October 2008 08:47 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | they want to see there two 0's after the decimal inthe output csv file
|
There are, if you open the file with notepad, you will see there are the two 0.
Regards
Michel
|
|
|